How to Retrieve RowID-Like Information in SQL Server?

Retrieving RowID-Like Information in SQL Server

One of the key distinctions between Oracle and SQL Server is the absence of an exact equivalent to Oracle's RowID concept. However, there are ways to obtain similar information in SQL Server, primarily through the undocumented and unsupported %%physloc%% virtual column.

Understanding %%physloc%%

The %%physloc%% virtual column returns a binary(8) value representing the following information:

• Page ID (first four bytes)
• File ID (next two bytes)
• Slot location on the page (final two bytes)

Converting Binary Value to Human-Readable Form

SQL Server provides two helpful functions for converting the %%physloc%% binary value into a more readable format:

• sys.fn_PhysLocFormatter: Converts the binary value into the "File:Page:Slot" format.
• sys.fn_PhysLocCracker: Converts the binary value into separate File ID, Page ID, and Slot variables.

Example Usage

To demonstrate the use of %%physloc%%, consider the following query:

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

Reversing the Conversion

To reverse the conversion process and obtain the binary(8) value from known File, Page, and Slot values, the following code 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))

This functionality, while useful, has no direct impact on the query optimization process in SQL Server.

The above is the detailed content of How to Retrieve RowID-Like Information in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!