Equivalent of Oracle's rownum in SQL Server.

In oracle , we use rownum to display the running number for the result set . There is no direct equivalent in SQL Server . From SQL Server 2005 , it is easy to simulate this functionality with the help of Window Functions ( SQL Server ) / Analytical Functions ( Oracle) .

ORACLE
=======

SQL> set pagesize 90
SQL> select ename , rownum from scott.emp ;

ENAME          ROWNUM
---------- ----------
SMITH               1
ALLEN               2
WARD                3
JONES               4
MARTIN              5
BLAKE               6
CLARK               7
SCOTT               8
KING                9
TURNER             10
ADAMS              11
JAMES              12
FORD               13
MILLER             14

14 rows selected.


SQL Server
=========

C:\Users\zahir>sqlcmd -W
1> use EXSQL2008R2
2> go
Changed database context to 'ExSQL2008R2'.
1>
2> Select top 10 name into t from master.sys.tables;
3> go

1> drop table t;
2> go

1> Select top 10 name into t from master.sys.tables;
2> go

(6 rows affected)
1> Select * from t;
2> go
name
----
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values
MSreplication_options

(6 rows affected)





We use ROW_NUMBER function to get the desired result .

1> Select name ,
2> ROW_NUMBER() over ( partition by 1 order by (Select 1)  asc) as rownum
3> from t;
4> go
name rownum
---- ------
spt_fallback_db 1
spt_fallback_dev 2
spt_fallback_usg 3
spt_monitor 4
spt_values 5
MSreplication_options 6

(6 rows affected)

Comments Welcome.