Simulate ROW_NUMBER in SQL 2000
-
Posted on May 23, 2010 by Derek Dieter
-
1
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.
- Comments (RSS)
- Trackback
- Permalink
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.
Is there any way to achieve over partition by effect in SQL 2000?
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;
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.
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,
