Eliminating ROW_NUMBER() for SQL 2000

Question

I have to migrate a sql to work on Microsoft SQL Server 2000. Unfortunately the current sql uses the function ROW_NUMBER() which is not yet supported in this version. Therefore I have to find something similar.

Below my SQL (I used * instead of listing all columns)

SELECT [Id], ROW_NUMBER() OVER (ORDER BY InstallmentNumber, ID ASC) AS ROWID
FROM [ARAS].[ARAS].[Movement]

What is the rest of the query doing? Is it using ROWID to iterate? If so, there could be other ways to handle this. — SQLMason, Apr 18 '11 at 16:27
Yes. basically it uses the RowId to filter the data. The idea is to sum all previous total values. A Select in Select with the following column `(SELECT SUM(Total) WHERE RowId <= currentRowId) AS Balance` — Kuepper, Apr 18 '11 at 17:28

score 3 · Accepted Answer · answered Apr 18 '11 at 16:28

3

Using a temp table with an identity column to simulate the ROW_NUMBER may be your best bet performance wise:

CREATE TABLE #tmpRowNum (
    ROWID INT IDENTITY(1,1),
    ID INT
)

INSERT INTO #tmpRowNum
    (ID)
    SELECT ID
        FROM [ARAS].[ARAS].[Movement]
        ORDER BY InstallmentNumber, ID

answered Apr 18 '11 at 16:28

Joe Stefanelli

132,803
19
237
235

But this does not work in a single select like above, does it? I guess there is no way of preventing using a more complex stored procedure. – Kuepper Apr 18 '11 at 17:33
Except that since the above example is using a temp table that is unique to the connection/process/spid, nobody else can affect it. You will always get a contiguous stream of values this way. – Nicholas Carey Apr 18 '11 at 17:35

score 0 · Answer 2 · answered Apr 18 '11 at 16:28

Not too sure, but here's a starting-point:

SELECT [Id],
  ( SELECT SUM(1)
    FROM [ARAS].[ARAS].[Movement]
    WHERE InstallmentNumber <= OuterMovement.InstallmentNumber
      AND ID <= OuterMovement.ID
  ) AS ‘Row Number’
FROM [ARAS].[ARAS].[Movement] AS OuterMovement

SQLMason · Answer 3 · 2011-04-18T18:20:33.203

You can make a variable table giving the "ROWID" an INT Identity value and inserting the values into that.

You can also do while w/o using the ROWID. The performance wouldn't be great though...

DECLARE @id INT, @SUM INT
SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement]    


WHILE EXISTS (SELECT [Id] FROM [ARAS].[ARAS].[Movement] WHERE [Id] >= @id)
BEGIN
    ... do something with the ID ..
    SELECT @SUM = SUM(...) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id

    SELECT @id = MIN([Id]) FROM [ARAS].[ARAS].[Movement] WHERE [Id] > @id
END

Eliminating ROW_NUMBER() for SQL 2000

3 Answers3

Linked