SQL Server: Oracle's ROWNUM equivalent in SQL server.

SQL server does not have rownum pseudo column. So if we would like to fetch row number along with select statement we have to use a ranking function ROW_NUMBER.

In oracle we can achieve same thing very easily using rownum. for example:

CREATE TABLE test1
AS 
SELECT table_name, column_name 
FROM user_tab_columns;

SELECT ROWNUM, table_name, column_name
FROM test1;

But SQL Server does not have rownum like pseudo column. So instead we have to do something like this:

SELECT OBJECT_NAME(object_id) as table_name, name as column_name
INTO test1
FROM sys.columns;

SELECT ROW_NUMBER() OVER (ORDER BY table_name, column_name) as rownum,
table_name, column_name
FROM test1; 

Now row_number() is an anaytical function and it also has partition by clause just like its oracle counter-part. So if you would like to partition/divide the result set and show rownum based on each partition you can do that. In the above example lets say you would like to show row number by each table name. Then following query will do just like that:

SELECT ROW_NUMBER() OVER (PARTITION BY table_name ORDER BY table_name, column_name) as rownum,
table_name, column_name
FROM test1;


References:

• http://msdn.microsoft.com/en-us/library/ms186734.aspx
• http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm