Equivalent of Oracle's RowID in SQL Server

Question

What's the equivalent of Oracle's RowID in SQL Server?

Answer 1

http://vyaskn.tripod.com/programming_faq.htm#q17

Answer 2

If you want to uniquely identify a row within the table rather than your result set, then you need to look at using something like an IDENTITY column. See "IDENTITY property" in the SQL Server help. SQL Server does not auto-generate an ID for each row in the table as Oracle does, so you have to go to the trouble of creating your own ID column and explicitly fetch it in your query.

EDIT: for dynamic numbering of result set rows see below, but that would probably an equivalent for Oracle's ROWNUM and I assume from all the comments on the page that you want the stuff above. For SQL Server 2005 and later you can use the new Ranking Functions function to achieve dynamic numbering of rows.

For example I do this on a query of mine:

select row_number() over (order by rn_execution_date asc) as 'Row Number', rn_execution_date as 'Execution Date', count(*) as 'Count'
from td.run
where rn_execution_date >= '2009-05-19'
group by rn_execution_date
order by rn_execution_date asc

Will give you:

Row Number  Execution Date           Count
----------  -----------------        -----
1          2009-05-19 00:00:00.000  280
2          2009-05-20 00:00:00.000  269
3          2009-05-21 00:00:00.000  279

There's also an article on support.microsoft.com on dynamically numbering rows.

Answer 3

Check out the new ROW_NUMBER function. It works like this:

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

Answer 4

There is no direct equivalent to Oracle's rownum or row id in SQL Server. Strictly speaking, in a relational database, rows within a table are not ordered and a row id won't really make sense. But if you need that functionality, consider the following three alternatives:

1. Add an IDENTITY column to your table. See Books Online for more information

2. Use the following query to generate a row number for each row. The following query generates a row number for each row in the authors table of pubs database. For this query to work, the table must have a unique key.

SELECT (SELECT COUNT(i.au_id) FROM pubs..authors i WHERE i.au_id >= o.au_id ) AS RowID, au_fname + ' ' + au_lname AS 'Author name' FROM pubs..authors o ORDER BY RowID

1. Use a temporary table approach, to store the entire resultset into a temporary table, along with a row id generated by the IDENTITY() function. Creating a temporary table will be costly, especially when you are working with large tables. Go for this approach, if you don't have a unique key in your table. Search for IDENTITY (Function) in SQL Server Books Online.

Answer 5

if you just want basic row numbering for a small dataset, how about someting like this?

SELECT row_number() OVER (order by getdate()) as ROWID, * FROM Employees

Answer 6

How it is done:

with results as
(select row_number() over (order by price desc) as rowid, * from titles)
select * from resultset;

You've just created a rowid in Pubs's titles table. row_number() is a built-in function.