在SQL Server中等效于Oracle的RowID [英] Equivalent of Oracle's RowID in SQL Server

问题描述

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).文件 数字是相对于表空间的.

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

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

要逆转前面提到的两个功能所执行的过程，并获得与已知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))

这篇关于在SQL Server中等效于Oracle的RowID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！