What is the SQL Server Equivalent of Oracle's ROWID?

Equivalent of Oracle's RowID in SQL Server

The ROWID pseudocolumn in Oracle uniquely identifies each row in a table and contains information about its location in the database. In SQL Server, the closest equivalent to ROWID is the rid, which has three components: File, Page, and Slot.

To obtain the rid, you can use the undocumented and unsupported %%physloc%% virtual column, which returns a binary(8) value with the Page ID in the first four bytes, File ID in the next two bytes, and Slot location in the last two bytes.

You can use the sys.fn_PhysLocFormatter or sys.fn_PhysLocCracker functions to convert this binary value into a more readable format:

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

Output:

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

Note that SQL Server does not leverage the rid in query processing, so using %%physloc%% in a WHERE clause will result in a full table scan.

To convert File, Page, and Slot values back to the binary(8) representation, use:

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))

The above is the detailed content of What is the SQL Server Equivalent of Oracle's ROWID?. For more information, please follow other related articles on the PHP Chinese website!