Getting rownumber in View in SQL2000

[vineetcs]

Yes In SQL2000 , Row_Number() function will not work as in SQL2005.
But, we can use the IDENTITY(INT,1,1) function but it requires a temporary table to which it stores the data and in View does'nt Support Temporary tables.

what I did is, I created a function which returns a table and in that table I have but 1 Identity field with AutoIncrement...
Sample Code
****************
ALTER FUNCTION dbo.UF_GetName()

RETURNS @Temp_tbl TABLE (
[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
AS
BEGIN

INSERT INTO @Temp_tbl (Name)
SELECT
Name
FROM
Users
WHERE
Name like 'a%'

ORDER BY Name


RETURN

END

GO

************************************************** ***************************

Now, you can use this function in the stored procedure or views and you want to ... and the RowNumber in the table will give you the RowNumbers for ur query Result...

SampleCode
****************

Create View View_GetName
as
SELECT TOP 100 PERCENT RowNumber,Name FROM dbo.UF_GetName()
WHERE RowNumber BETWEEN 1 AND 100

********************

then Execute the View u will get the result...


I Hope this post might help someone, who is trying to create view in sql2000 and wants the rownumber in the result.

Cheers !!!
Vineet CS