在SQL Server中等效于Oracle的RowID | Server中等效于Oracle的RowID

本文介绍了在SQL Server中等效于Oracle的RowID的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

SQL Server中Oracle的RowID等同于什么?

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

推荐答案

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

For each row in the database, the ROWID pseudocolumn returns theaddress of the row. Oracle Database rowid values contain informationnecessary 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 filenumber 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！