相当于 SQL Server 中 Oracle 的 RowID

Equivalent of Oracle's RowID in SQL Server

提问by Martin Smith

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

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

回答by Martin Smith

From the Oracle docs

来自 Oracle 文档

ROWID Pseudocolumn

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:

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

ROWID 伪列

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

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

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

在 SQL Server 中与此最接近的是rid具有三个组件的File:Page:Slot。

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.

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

The scalar function sys.fn_PhysLocFormatteror the sys.fn_PhysLocCrackerTVF can be used to convert this into a more readable form

标量函数sys.fn_PhysLocFormatter或sys.fn_PhysLocCrackerTVF 可用于将其转换为更具可读性的形式

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

Example Output

示例输出

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

Note that this is not leveraged by the query processor. Whilst it is possibleto use this in a WHEREclause

请注意，查询处理器不会利用这一点。虽然可以在WHERE子句中使用 this

SELECT *
FROM T
WHERE %%physloc%% = 0x2926020001000100 

SQL Server will notdirectly 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).

SQL Server不会直接查找指定的行。相反，它将执行全表扫描，评估%%physloc%%每一行并返回匹配的行（如果有）。

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.

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

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

回答by S Wright

I have to dedupe a very big table with many columns and speed is important. Thus I use this method which works for any table:

我必须对包含许多列的非常大的表进行重复数据删除，速度很重要。因此，我使用这种适用于任何表的方法：

delete T from 
(select Row_Number() Over(Partition By BINARY_CHECKSUM(*) order by %%physloc%% ) As RowNumber, * From MyTable) T
Where T.RowNumber > 1

回答by Xiaofu

If you want to uniquely identify a row within the table rather than your result set, then you need to look at using something like an IDENTITY column. See "IDENTITY property" in the SQL Server help. SQL Server does not auto-generate an ID for each row in the table as Oracle does, so you have to go to the trouble of creating your own ID column and explicitly fetch it in your query.

如果要唯一标识表中的行而不是结果集，则需要考虑使用 IDENTITY 列之类的内容。请参阅 SQL Server 帮助中的“IDENTITY 属性”。SQL Server 不会像 Oracle 那样为表中的每一行自动生成 ID，因此您必须费心创建自己的 ID 列并在查询中显式获取它。

EDIT:for dynamic numbering of result set rows see below, but that would probably an equivalent for Oracle's ROWNUM and I assume from all the comments on the page that you want the stuff above. For SQL Server 2005 and later you can use the new Ranking Functionsfunction to achieve dynamic numbering of rows.

编辑：对于结果集行的动态编号，请参见下文，但这可能与 Oracle 的 ROWNUM 等效，并且我从页面上的所有评论中假设您需要上述内容。对于 SQL Server 2005 及更高版本，您可以使用新的Ranking Functions函数来实现行的动态编号。

For example I do this on a query of mine:

例如，我在我的查询中执行此操作：

select row_number() over (order by rn_execution_date asc) as 'Row Number', rn_execution_date as 'Execution Date', count(*) as 'Count'
from td.run
where rn_execution_date >= '2009-05-19'
group by rn_execution_date
order by rn_execution_date asc

Will give you:

会给你：

Row Number  Execution Date           Count
----------  -----------------        -----
1          2009-05-19 00:00:00.000  280
2          2009-05-20 00:00:00.000  269
3          2009-05-21 00:00:00.000  279

There's also an article on support.microsoft.comon dynamically numbering rows.

support.microsoft.com上还有一篇关于动态编号行的文章。

回答by Daren Thomas

Check out the new ROW_NUMBERfunction. It works like this:

查看新的ROW_NUMBER函数。它是这样工作的：

SELECT ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID, * FROM EMPLOYEE

回答by Vincent

Several of the answers above will work aroundthe lack of a direct reference to a specific row, but will not workif changes occur to the other rows in a table. That is my criteria for which answers fall technically short.

上面的几个答案将解决缺少对特定行的直接引用的问题，但如果表中的其他行发生更改，则将不起作用。这是我的标准，即答案在技术上不足。

A common use of Oracle's ROWID is to provide a (somewhat) stable method of selecting rows and later returning to the row to process it (e.g., to UPDATE it). The method of finding a row (complex joins, full-text searching, or browsing row-by-row and applying procedural tests against the data) may not be easily or safely re-used to qualify the UPDATE statement.

Oracle 的 ROWID 的一个常见用途是提供一种（有点）稳定的方法来选择行，然后返回到行进行处理（例如，更新它）。查找行的方法（复杂连接、全文搜索或逐行浏览并对数据应用程序测试）可能无法轻松或安全地重新用于限定 UPDATE 语句。

The SQL Server RID seems to provide the same functionality, but does not provide the same performance. That is the only issue I see, and unfortunately the purpose of retaining a ROWID is to avoid repeating an expensive operation to find the row in, say, a very large table. Nonetheless, performance for many cases is acceptable. If Microsoft adjusts the optimizer in a future release, the performance issue could be addressed.

SQL Server RID 似乎提供相同的功能，但不提供相同的性能。这是我看到的唯一问题，不幸的是，保留 ROWID 的目的是避免重复昂贵的操作以在非常大的表中查找行。尽管如此，许多情况下的性能是可以接受的。如果 Microsoft 在未来版本中调整优化器，则可以解决性能问题。

It is also possible to simply use FOR UPDATE and keep the CURSOR open in a procedural program. However, this could prove expensive in large or complex batch processing.

也可以简单地使用 FOR UPDATE 并在程序程序中保持 CURSOR 打开。但是，这在大型或复杂的批处理中可能会很昂贵。

Caveat:Even Oracle's ROWID would not be stable if the DBA, between the SELECT and the UPDATE, for example, were to rebuild the database, because it is the physical row identifier. So the ROWID device should only be used within a well-scoped task.

警告：例如，如果 DBA 在 SELECT 和 UPDATE 之间重建数据库，即使 Oracle 的 ROWID 也不稳定，因为它是物理行标识符。所以 ROWID 设备应该只在一个范围明确的任务中使用。

回答by cjs

From http://vyaskn.tripod.com/programming_faq.htm#q17:

来自http://vyaskn.tripod.com/programming_faq.htm#q17：

Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or how to generate output with row number in SQL Server?

There is no direct equivalent to Oracle's rownum or row id in SQL Server. Strictly speaking, in a relational database, rows within a table are not ordered and a row id won't really make sense. But if you need that functionality, consider the following three alternatives:

• Add an IDENTITYcolumn to your table.

• Use the following query to generate a row number for each row. The following query generates a row number for each row in the authors table of pubs database. For this query to work, the table must have a unique key.

  SELECT (SELECT COUNT(i.au_id) 
          FROM pubs..authors i 
          WHERE i.au_id >= o.au_id ) AS RowID, 
         au_fname + ' ' + au_lname AS 'Author name'
  FROM          pubs..authors o
  ORDER BY      RowID
  

• Use a temporary table approach, to store the entire resultset into a temporary table, along with a row id generated by the IDENTITY()function. Creating a temporary table will be costly, especially when you are working with large tables. Go for this approach, if you don't have a unique key in your table.

Oracle 有一个 rownum 来使用行号或行 ID 访问表的行。在 SQL Server 中是否有任何等价物？或者如何在 SQL Server 中生成带有行号的输出？

在 SQL Server 中没有直接等同于 Oracle 的 rownum 或 row id。严格来说，在关系数据库中，表中的行没有排序，行 id 没有意义。但如果您需要该功能，请考虑以下三种替代方案：

• IDENTITY向表中添加一列。

• 使用以下查询为每一行生成一个行号。以下查询为pubs 数据库的authors 表中的每一行生成一个行号。要使此查询起作用，该表必须具有唯一键。

  SELECT (SELECT COUNT(i.au_id) 
          FROM pubs..authors i 
          WHERE i.au_id >= o.au_id ) AS RowID, 
         au_fname + ' ' + au_lname AS 'Author name'
  FROM          pubs..authors o
  ORDER BY      RowID
  

• 使用临时表方法，将整个结果集以及IDENTITY()函数生成的行 ID 存储到临时表中。创建临时表的成本会很高，尤其是在处理大型表时。如果您的表中没有唯一键，请采用这种方法。

回答by cjs

if you just want basic row numbering for a small dataset, how about someting like this?

如果你只想要一个小数据集的基本行编号，这样的怎么样？

SELECT row_number() OVER (order by getdate()) as ROWID, * FROM Employees

回答by user2793105

If you want to permanently number the rows in the table, Please don't use the RID solution for SQL Server. It will perform worse than Access on an old 386. For SQL Server simply create an IDENTITY column, and use that column as a clustered primary key. This will place a permanent, fast Integer B-Tree on the table, and more importantly every non-clustered index will use it to locate rows. If you try to develop in SQL Server as if it's Oracle you'll create a poorly performing database. You need to optimize for the engine, not pretend it's a different engine.

如果要对表中的行进行永久编号，请不要使用 SQL Server 的 RID 解决方案。在旧的 386 上，它的性能会比 Access 差。对于 SQL Server，只需创建一个 IDENTITY 列，并将该列用作集群主键。这将在表上放置一个永久的、快速的整数 B 树，更重要的是每个非聚集索引都将使用它来定位行。如果您尝试在 SQL Server 中进行开发，就好像它是 Oracle 一样，您将创建一个性能不佳的数据库。您需要针对引擎进行优化，而不是假装它是不同的引擎。

also, please don't use the NewID() to populate the Primary Key with GUIDs, you'll kill insert performance. If you must use GUIDs use NewSequentialID() as the column default. But INT will still be faster.

另外，请不要使用 NewID() 用 GUID 填充主键，否则会降低插入性能。如果必须使用 GUID，请使用 NewSequentialID() 作为列默认值。但是INT仍然会更快。

If on the other hand, you simply want to number the rows that result from a query, use the RowNumber Over() function as one of the query columns.

另一方面，如果您只想对查询结果的行进行编号，请使用 RowNumber Over() 函数作为查询列之一。

回答by Yuri Chernov

Please try

请尝试

select NEWID()

select NEWID()

Source: https://docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql

来源：https: //docs.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql

回答by Erik

ROWID is a hidden column on Oracle tables, so, for SQL Server, build your own. Add a column called ROWID with a default value of NEWID().

ROWID 是 Oracle 表上的隐藏列，因此，对于 SQL Server，请构建您自己的列。添加名为 ROWID 的列，默认值为NEWID()。

How to do that: Add column, with default value, to existing table in SQL Server

如何做到这一点：将具有默认值的列添加到 SQL Server 中的现有表