How to create RowNum column in SQL Server?

In Oracle we have "rownum". What can I do in SQL Server?

From stackoverflow

• In SQL Server 2005 (and 2008) you can use the ROW_NUMBER function, coupled with the OVER clause to determine the order in which the rows should be counted.

  Update

  Hmm. I don't actually know what the Oracle version does. If it's giving you a unique number per row (across the entire table), then I'm not sure there's a way to do that in SQL Server. SQL Server's ROW_NUMBER() only works for the rows returned in the current query.

• If you have an id column, you can do this:

  select a.*, 
  (select count(*) from mytable b where b.id <= a.id) as rownum
  from mytable a
  order by id;
  

  Of course, this only works where you're able to order rownums in the same (or opposite) order as the order of the ids.

  If you're selecting a proper subset of rows, of course you need to apply the same predicate to the whole select and to the subquery:

  select a.*, 
  (select count(*) from table b where b.id <= a.id and b.foo = 'X') as rownum
  from table a where a.foo = 'X'
  order by id;
  

  Obviously, this is not particularly efficient.

  gbn : Very inefficient for SQL Server 2005 and above

  tpdi : Yes, as I noted, above. But if you have to do it, and you don't hve 2005 or above, you do what you must.

• Based on my understanding, you'd need to use ranking functions and/or the TOP clause. The SQL Server features are specific, the Oracle one combines the 2 concepts.

  The ranking function is simple: here is why you'd use TOP. Note: you can't WHERE on ROWNUMBER directly...

  'Orable:
  select 
    column_1, column_2 
  from 
    table_1, table_2
  where
    field_3 = 'some value'
  and rownum < 5
  
  --MSSQL:
  select top 4
    column_1, column_2 
  from 
    table_1, table_2
  where
    field_3 = 'some value'