Rownum in SQL Server

Prior to SQL Server 2005, there was no inherent function to generate a rownumber within a row. There is a workaround however for SQL 2000. If you are on SQL 2005+, then you will utilize the following function:

Method 1

-- SQL 2005+
SELECT
    RowNumber = ROW_NUMBER() OVER (ORDER BY c.CustomerID ASC)
    ,c.*
FROM SalesLT.Customer c

Method 2

There are two ways to do this in SQL 2000. The easiest way is to join the table on itself and count the number of rows that precede the primary key.

-- SQL 2000+
SELECT Rownum =
(
    SELECT COUNT(1)
    FROM SalesLT.Customer
    WHERE CustomerID <= c.CustomerID
), *
FROM SalesLT.Customer c
ORDER BY CustomerID

Method 3

The other way for SQL 2000 is to use the IDENTITY function in order to generate a Rownum. The downside is that you must insert the results into a temp table, then select from the temp table in order to retrieve the rownum.

-- SQL 2000+
SELECT
    RowNumber   = IDENTITY(INT,1,1)
    ,c.LastName
    ,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC

SELECT *
FROM #Customer_RowID

Also notice from this example that when we insert the results into the temp table, we are specifying the column names. This is because the identity function will not work if you include the primary key of the table, which is another downside.

6 comments
Hiroko 29 Aug 2015 at 5:41 am

Firefox 3.5.3 Windows XPDo you have a spam issue on this website; I also am a blgeogr, and I was curious about your situation; we have developed some nice methods and we are looking to swap strategies with others, please shoot me an email if interested.

kashan 07 Apr 2012 at 3:29 pm

third one worked for me

rerrere 17 Jan 2012 at 1:03 am
Growan Technologies Pvt. Ltd. 27 Aug 2011 at 11:18 pm

Method 1 was quit easy and understandable helped me alot thanks for the post………

Rez 21 Mar 2011 at 11:01 pm

this isn’t clear.

Raju 29 Aug 2015 at 5:14 am

Internet Explorer 7.0 Windows XPI really like your blog.. very nice corlos & theme. Did you make this website yourself or did you hire someone to do it for you? Plz answer back as I’m looking to construct my own blog and would like to find out where u got this from. kudos

Featured Articles

 Site Author

  • Thanks for visiting!