How to find duplicate rows in a table? (For SQL Server 2005)

There are two possibilities,

1. Take account of all the rows: Your table doesn’t have any primary key and you want to check for duplicates.
2. Only for selected rows: Your table is having a primary key, so that will always be unique. Here you want to check for other columns.

They query is very easy for the first possibility.
Suppose a table ‘mytable’ has four columns a, b, c, d. (No Primary key)

I.e. select a,b,c,d from mytable group by a,b,c,d having count(*) > 1

But, the query is long for the second option.

Suppose a table ‘mytable’ has four columns a, b, c, d (a and b is composite primary key)

Then the query would be,

select T1.a,T1.b,T1.c,T1.d
from
mytab T1, mytab T2
where
(T1.c=T2.c or (T1.c is null and T2.c is null))
and (T1.d=T2.d or (T1.c is null and T2.c is null))
and T1.A != T2.A
and T1.b != T2.b
order by T1.c,T1.d

OR (this is same as above)

select t1.a,t1.b, t1.c,t1.d
from
mytab T1 inner join mytab T2
on
(T1.c=T2.c or (T1.c is null and T2.c is null))
and (T1.d=T2.d or (T1.c is null and T2.c is null))
where
T1.A != T2.A and T1.b != T2.b
order by t1.c,t1.d

For Itch

Before we start coding, we got an email about best practices for writing code in .NET. There was a line in the email about using ‘For’ instead of using ‘For Each’ loop (in VB .net). People have proven that ‘For’ loop is faster than (or same at max) ‘For Each’ by going unto Intermediate Language.

I don’t want to go in discussion about which one is better. Just see this and tell me which one is better to use. Isn’t it better to write simple and maintainable code than 'possibly' faster code?

'For Each
For Each str As String In BaseString.Split(" "c)
      Result += SomeFunction(str)
Next
'For only
For i As Integer = 0 To BaseString.Split(" "c).Length - 1
      Result += SomeFunction(BaseString.Split(" "c)(i))
Next

How to work at same time with T-SQL Stored Procedure and Coding standards

I was having real bad time by modifying the T-SQL procedures according to our coding standards. It's because the editor which comes with SQL Sever 2005, doesn't provide auto-indent or auto-format functionality.

Another thing! Command to read stored i.e. SP_HelpText doen't read TABs (or there is a problem in my way of copy-pasting). So all my hardwork with go in vain if I don't take care about that. And the editor allows you to use TABs freely and, without mistake, it puts the tab character instead of bunch of spaces. 'Untabbify' functionality is there in the editor but I don't know why the hell it's not working everytime.

So currently, as I have to bear with this hell, I am following a new approach.

That is, first develop procedure in SQL server editor (fast for development) and then go to scintilla and format the shit. I got many problem with scintilla but all were solved when I downloaded MSI from http://gisdeveloper.tripod.com/scite.html This MSI comes with some good extensions and customized preferences. This preference are set exactly the same which I wanted (i.e. No tabs, tab size 4, font preferences etc).

So life is not so good because there is not directly 'auto-format' function, but the above approach is working until Microsoft really does something :)

T-SQL ROW_NUMBER() function

Number you eggs before they hatch! :)) Bad one!!

I search a lot about giving row number to each resultant row of select statement but it seemed such automatic row numbering function is not available in T-SQL (SQL which runs with MS SQL server). But my colleague, Subamanin, gave a wonderful solution which I want share with you. Code goes like this notice the ROW_NUMBER() function.

CODE:

declare @EMP table
(
emp_name VARCHAR(10),
emp_post varchar(10)
)

insert into @EMP values('A11','00A')
insert into @EMP values('A12','00B')
insert into @EMP values('A13','00C')
insert into @EMP values('A14','00A')
insert into @EMP values('A14','00A')

select *
from @EMP --Produces Table(0)

select *,
ROW_NUMBER() over(order by emp_name) as ID_NUM
from @EMP --Produces Table(1)

And this is RESULT:

TABLE(0)

emp_name	emp_post
A11	00A
A12	00B
A13	00C
A14	00A
A14	00A

TABLE(1)

emp_name	emp_post	ID_NUM
A11	00A	1
A12	00B	2
A13	00C	3
A14	00A	4
A14	00A	5

But unfortunately over(order by emp_name) function has to be there. Here in 'over' function you can order by column of your choice which may or may not be your primary or unique key.

Delay Google

Today I googled for around an hour to find a solution from which I can get the structure of a database table in my (.net) datatable object. Frustrated, dejected ; I closed the browser window and started doing something as the work was urgent and a work around was highly required. After using my own brain for few minutes I got it and it was so simple (a query… Select * from table_name where 1=0) which made me think whether google is necessary on the next moment when I get into a problem.

About MS Visual Studio 2005 Editor for ASP .net

I get excited when I start my Visual Studio to write any web application because of its kind nature of suggesting the code (oh ya!! It’s ‘intellisense’). But the shit about this editor is when I change table height/width parameters (in design view) it changes in pixels not in %. This is really irritating me because only a wrong mouse click (in design view) in my complex web pages changes width and height parameters of al s. So again I’ve to go to each and every of my table and change the parameter. Although this editor is much batter then the one which was coming in VS 2003 (for ASP .net) as that one was starting in grid mode by default. Editor for ASP .net in VS 2005 doesn’t change my HTML formatting and I am really indebted to Microsoft as it was not there in editor of VS 2003.

When does the firefox put the machine on fire?

Q. When does the firefox put the machine on fire?

Ans: Just analyze the attachment.

Ye, Yes, Pee . NET 2

From this week I am starting work on ASP .net 2, a year after first hands on exp (read my blog of 1^st march 2005). Webcasts from Microsoft are giving great promises but it’s my time to see whether it was an advertising program or they really those are for us. Some of cool new features (or features with newness) according to me are masterpages, menu, sitemap, cashing, RAD and most importantly XHTML W3C standards compatibility for cross browser support (MS now understands that not following standards may keep MS following only). VS 2005 and MS SQL 2005 are also yet to experience. And as I have to work on AJAX application, I need to have a look at ‘Atlas’ too.

The list is not long but time is really ticking fast!

Google is reached to the Mars

How about URL http://www.google.com/mars !! Don't laugh and just click on it. Google knows the future. Days are not far when you will like to spot a location on mars to have party for next weekends. It's just matter of 50 years ;)

µTorrent - another slick baby

µTorrent is very very light weight bit-torrent client. It's amazing for poor peoples like me, who don't have much money to buy enough RAM. Installer is 130 KB and the application takes only 5-8 MB in my RAM. It's all those features which an advanced bit-torrent client should have. So, I kicked Azureus out (takes 30-60 MB RAM) and µTorrent is in.

Get it from here.

Slickrun, slick one is faster

Slick run is a tiny software which provides slick “run” bar from where you can execute your commands. It's a floating command line utility i.e. Application launcher. Many useful commands come with the software but surely you will also add your own and that makes is different then [Windows Key + R]. It has auto complete facility and this bar can be put anywhere on the screen. It's ghost effect will never let you know about it's existence until you want to use it. If you are using Start -> Run very often then definitely you will love slick run.

It's here.

This is it... Really slick na ?

Tell me - Is AJAX cool or hot?

Feeling to write someting on some cool technology to slay off my drowsiness. So I choose AJAX. When I started using Gmail, I was surprised by its quick response. Specially, it was noticeable that on most of the events, it don't load full page (like Yahoo!! is still doing). This is due to new idea of AJAX technology. AJAX is new 'idea' but not new technology, as it uses our old babies 'Java script' and 'XML'. Whenever the web page needs some data, you get it using Java Scrip in XML format. As AJAX is simply use of Java Script and XML in smart way, you can develop such interface in any web development languages (like ASP, JSP, PHP... and many). You can have application like interface and speed with AJAX. I have read somewhere that 'limitations of AJAX totally depends upon the imagination of the developer' – and that may be right. If you want to see sample application, goto (again... I am a bad programmer) google suggest and google maps (common... don't except link from me). A good tutorial is here.

I leave you here and myself too.

My Penguin wants IPod nano to play with

Linux lover please stay calm... see there are people around you. Don't shout and just read.

Yes... your guess is correct. Your IPod can boot the Linux. You see, Linux can boot anywhere and it's uCLinux this time. You can have an dual boot IPod with second boot option as the uCLinux. People have done this successfully and now playing with this. Even you can take that line literally, as you can play games.. yes games... in your IPod nano. As once you have Linux somewhere it's over. GUI is provided by podzilla. There are many customized builds exists for that. For more information, goto (sorry, I am a bad programmer) iPodLinux site: http://ipodlinux.org/

These are the tasks which you can perform with your IPod.

1. You can play MP3, OGG, AAC (for which you have the one)

2. MultiConvert - A program to convert any unit of any type to all other units of that type in "real time"

3. Periodic Table - Periodic Table for the iPod

4. minix-sh

5. viP - Text Editor

6. aalib - Portable ASCII art graphics library

7. Python - Write Python programs on your iPod

8. Ncurses - Write ncurses programs on your iPod

9. Wikipedia - Carry the whole WikiPedia around on your iPod

10. Hell lots of games.

11. Play video... No need to pay extra bucks.. just wait for some more time to mature this tech.
    

You will never get screwed by doing this as you can always restore your iPod firmware again. Try this today if you have the one. And donate me Rs 14000 if you want me to do so :D

Take my words, one day, some Linux will boot in mind.