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

Oracles Analyze Table statement ?

Hi,
Below is an Oracle query used for cost optimization purpose :
analyze table test estimate statistics sample 2500 Rows;
Is there any equivalent for the above in SQL Server ?
Please advice,
Thanks,
SamWhy look for equivalent? Just convert to it!|||microsoft sql server automatically updates statistics as a default setting
you wouldnt have to manually create statistics unless you chose exceptions to the norm in your design.

for example
turning off autoupdatestats
creating composite indexes and creating statistics on the non leading columns
creating statistics on commonly searched columns that wouldnt normally warrant an index.

you can run the create statistics statement or the update statistics statements respectively

Check out Books Online in your SQL Server start menu folder
and search for statistics create stats or update stats.|||Below is an Oracle query used for cost optimization :

analyze table Customer list chained rows into CHAINED_ROWS;

Is there any equivalent for the above in SQL Server ?

Please advice,

Thanks,
Sam|||Is there any solutions?
I too need the equivalent for
the above query.

any help would be appreciated

With Regards
Sathya|||MS-SQL does not need the kind of maintenance that Oracle needs. It used to, years ago, but that time is long gone.

I could make up a number of suggestions, some of which would actually do something to improve optimization a bit, others would just do some disk i/o and make you feel good, but none of them are really needed anymore.

-PatP