Simulate ROW_NUMBER in SQL 2000

While the row_number feature in sql 2005+ has proven to be a very powerful feature, there are still ways to implement that same functionality in SQL Server 2000.

Let’s first look at the SQL 2005+ implementation of ROW_NUMBER, then compare it to the SQL 2000:

[cc lang=”sql”]
— SQL 2005+
SELECT
RowNumber = ROW_NUMBER() OVER (ORDER BY c.LastName ASC)
,c.LastName
,c.FirstName
FROM SalesLT.Customer c
[/cc]

And the SQL 2000 version:

[cc lang=”sql”]
SELECT
RowNumber = IDENTITY(INT,1,1)
,c.LastName
,c.FirstName
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC
[/cc]

As with everything, there are pro’s and cons however. Using the SQL 2000 version, the total time take to run is 113ms, while the ROW_NUMBER function returns in 134ms (a 21ms difference). That’s a positive for the SQL 2000 version. Problem is, in this case, there were only 440 rows returned. Increase the result set to over 1000+ and the advantages begin to fade.

The biggest drawback regarding the SQL 2000 version is that if you are inserting the results using a SELECT..INTO model, you cannot insert the identity column from the table you are selecting from. Like this (notice the *) :

[cc lang=”sql”]
SELECT
RowNumber = IDENTITY(INT,1,1)
,c.LastName
,c.FirstName
,*
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC
[/cc]

Otherwise, you receive the following error message:

Cannot add identity column, using the SELECT INTO statement, to table ‘#Customer_RowID’, which already has column ‘CustomerID’ that inherits the identity property.

So essentially, with SQL 2000, there is no way to throw a row_number over the entire table (including it’s identity column) dynamically. However if you exclude the identity column in your query, all will work well.

5 comments
LukeH 06 Mar 2015 at 3:58 am

Thanks Derek,

I needed the identity column (migrationID) to be returned as well into the temp table what i did was cast the identity column to an int

Select
RowNumber = IDENTITY(INT,1,1) ,
cast(migrationID AS Int) as migrationId,
etc,etc
INTO
#Export_RowID
etc etc

and this allowed me to get all the columns into the temp table.
Hope this helps someone else.

Dimitrije 05 Oct 2011 at 7:31 am

Is there any way to achieve over partition by effect in SQL 2000?

Derek Dieter 10 Oct 2011 at 9:10 am

Hi Dimitrieje,

Yes there is. Itzik Ben-Gan has an excellent example of this in his T-SQL Querying 2005 book. I highly recommend this book.

Here’s the example:

SELECT mgrid, empid, qty,
(SELECT COUNT(1)
FROM dbo.Sales AS S2
WHERE s2.mgrid = s1.mgrid
AND (s2.qty < s1.qty
OR (s2.qty = s1.qty AND s2.empid <= s1.empid))) AS rownum
FROM dbo.Sales AS s1
ORDER BY mgrid, qty, empid;

Ayrton 15 May 2012 at 6:26 am

SQL can execute quriees against a database, retrieve,update,insert data from a database and empty refer to an empty result of no rows and NULL refer to a table with one row holding a NULL value.

Mangala 23 Feb 2011 at 9:43 pm

Hi Derek,

I found a way to get an Identity for the table by using a dummy field as so (I am assuming your id is an int):

SELECT
RowNumber = IDENTITY(INT,1,1)
,c.LastName
,c.FirstName
,c.CustomerID*1 as CustomerID
INTO #Customer_RowID
FROM SalesLT.Customer c
ORDER BY c.LastName ASC

Regards,

Featured Articles

 Site Author

  • Thanks for visiting!
css.php