相当于 SQL Server 中 Oracle 的 RowID

2021-12-10 00:00:00 sql tsql sql-server row-number rowid

SQL Server 中 Oracle 的 RowID 相当于什么?

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

推荐答案

来自 Oracle 文档

ROWID 伪列

对于数据库中的每一行，ROWID 伪列返回行的地址.Oracle 数据库 rowid 值包含信息需要定位一行:

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:

  • 对象的数据对象编号
  • 该行所在的数据文件中的数据块
  • 该行在数据块中的位置(第一行为0)
  • 行所在的数据文件(第一个文件是 1).文件number 是相对于表空间的.

在 SQL Server 中与此最接近的是 rid，它包含三个组件 File:Page:Slot.

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

在 SQL Server 2008 中，可以使用未记录且不受支持的 %%physloc%% 虚拟列来查看此内容.这将返回一个 binary(8) 值，前四个字节是页面 ID，然后是文件 ID 的 2 个字节，然后是页面上的插槽位置的 2 个字节.

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.

标量函数 sys.fn_PhysLocFormatter 或 sys.fn_PhysLocCracker TVF 可用于将其转换为更易读的形式

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

示例输出

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

请注意，查询处理器并未利用此功能.虽然可能在WHERE子句

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 将不会直接寻找指定的行.相反，它将执行全表扫描，为每一行评估 %%physloc%% 并返回匹配的那一行(如果有的话).

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

要反转前面提到的 2 个函数执行的过程并获得与已知 File,Page,Slot 值相对应的 binary(8) 值，可以使用以下方法.

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

相关文章