What equals to Oracle’s ROWNUM | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

What equals to Oracle’s ROWNUM

Hi,
What equals to Oracle#%92s ROWNUM in MSSQL Server 2000
Regards
Srini
There is no such thing in SQL Server. You get a global variable automatically set after a query, @@ROWCOUNT, which you can capture and use, but there is no equivalent to Oracle’s ROWNUM. @@ROWCOUNT is a count of the number of rows affected by a query. You can also limit the number of rows a query will affect by setting the option ROWCOUNT, e.g. by running SET ROWCOUNT 50 your query will only affect 50 rows. It defaults to 0 (which means no limit). Tom Pullen
DBA, Oxfam GB
The upcoming version of SQL Server, code name Yukon, a.k.a. 2005, has a ROW_NUMBER() function you can include in any SELECT query.
Adriaan is right on this. The next version will finally include some of the standard SQL OLAP functions like ROW_NUMBER(). However, you’ve asked a FAQ in SQL Server land. There is no direct equivalent and while the correct answer is: "Do this at the client", you might start here:
http://support.microsoft.com/default.aspx?scid=kb;en-us;186133 —
Frank
http://www.insidesql.de
]]>