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:
-- SQL 2005+ SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY c.LastName ASC) ,c.LastName ,c.FirstName FROM SalesLT.Customer c
And the SQL 2000 version:
SELECT RowNumber = IDENTITY(INT,1,1) ,c.LastName ,c.FirstName INTO #Customer_RowID FROM SalesLT.Customer c ORDER BY c.LastName ASC
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 *) :
SELECT RowNumber = IDENTITY(INT,1,1) ,c.LastName ,c.FirstName ,* INTO #Customer_RowID FROM SalesLT.Customer c ORDER BY c.LastName ASC
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.
Popular search terms:
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,