Equivalent of Oracle's RowID in SQL Server

Solution 1:

From the Oracle docs

ROWID Pseudocolumn

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

• The data object number of the object
• The data block in the datafile in which the row resides
• The position of the row in the data block (first row is 0)
• The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

The closest equivalent to this in SQL Server is the rid which has three components File:Page:Slot.

In SQL Server 2008 it is possible to use the undocumented and unsupported %%physloc%% virtual column to see this. This returns a binary(8) value with the Page ID in the first four bytes, then 2 bytes for File ID, followed by 2 bytes for the slot location on the page.

The scalar function sys.fn_PhysLocFormatter or the sys.fn_PhysLocCracker TVF can be used to convert this into a more readable form

CREATE TABLE T(X INT);

INSERT INTO T VALUES(1),(2)

SELECT %%physloc%% AS [%%physloc%%],
       sys.fn_PhysLocFormatter(%%physloc%%) AS [File:Page:Slot]
FROM T

Example Output

+--------------------+----------------+
|    %%physloc%%     | File:Page:Slot |
+--------------------+----------------+
| 0x2926020001000000 | (1:140841:0)   |
| 0x2926020001000100 | (1:140841:1)   |
+--------------------+----------------+

Note that this is not leveraged by the query processor. Whilst it is possible to use this in a WHERE clause

SELECT *
FROM T
WHERE %%physloc%% = 0x2926020001000100 

SQL Server will not directly seek to the specified row. Instead it will do a full table scan, evaluate %%physloc%% for each row and return the one that matches (if any do).

To reverse the process carried out by the 2 previously mentioned functions and get the binary(8) value corresponding to known File,Page,Slot values the below can be used.

DECLARE @FileId int = 1,
        @PageId int = 338,
        @Slot   int = 3

SELECT CAST(REVERSE(CAST(@PageId AS BINARY(4))) AS BINARY(4)) +
       CAST(REVERSE(CAST(@FileId AS BINARY(2))) AS BINARY(2)) +
       CAST(REVERSE(CAST(@Slot   AS BINARY(2))) AS BINARY(2))

Solution 2:

I have to dedupe a very big table with many columns and speed is important. Thus I use this method which works for any table:

delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From MyTable) T
Where T.RowNumber > 1

Solution 3:

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.