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 […]