Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Simulating ROWNUM in SQL Server 2000

This is one of the most frequent question asked in SQL Server newsgroups. Basically how can we simulate the same behaviour as Oracle's ROWNUM functionality. Here is one approach you can use to generate a pseudo row number in SQL Server.

use pubs
GO
Select IDENTITY(int, 1,1) id, * INTO #Temp from authors
Select * from #Temp

Now you can observe that we have added a column id on the fly to generate the temp table. This is a typical rownum generator in SQL Server 2000.

There are also MS KB articles with fetures to generate row numbers. Visit http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186133 for other methods of generating the same code.