Oracles ROWNUM equivalent in SQL Server

Hi,

Can any one tell me is there anything in SQL Server thats equivalent
to Oracle's ROWNUM.

Note that the Identity Property or TOP n will not solve my problem.

I want to asign a sequence no. to each row when its being fetched.

For example if in the emp table there are 2000 rows and I write
the following query in Oracle ,

SELECT rownum , empno, empname FROM emp Where rownum < =3

I get the result like this

Rownum--Empno-----Empname
-------------
1----2345-----ABCD
2----3334-----EFGH
3----4484-----IJKL

I know I can limit the output rows in SQL Server by using TOP n. But
I also want to generate a sequence no. The identity property of SQL Server
will not be usefull here because my actaul WHERE clause will be more
complex like WHERE resigndate = '01-jan-2004'

Thanks

Asim Naveed

3A frequently asked question...

The answer usually involves creating a temp table (or table function) with an identity column. Insert the results of your query into this temp table and you'll have more or less what you need.

Hopefully this is something that is addressed in 2005?

hmcott|||There is actually a good reason for this "limitation" to exist. If the result set is being used on the server, it has to go into a table anyway, so the IDENTITY solution works nicely. If the result set is being used on a client, then the client ought to provide the rowid values (since the client knows more about how it wants the ids arranged than the server can).

This is actually a good design property, even though applications being moved from database managers like dBase and Oracle are often dependant on physical details that they shouldn't rely on.

-PatP