SQL Server中类似于Oracle的RowID的等效选项

Question

SQL Server中类似于Oracle的RowID的等效选项

sqlsql-servert-sqlrowidrow-number

101

在SQL Server中，与Oracle的RowID相当的是什么？

-

Stephanie：假设数据中存在唯一键，这意味着数据已经规范化，但有时这是错误的假设。因此，在SQL Server中，什么是Oracle的RowID的等效物？ - Christopher Mahan

13个回答

18

我需要对一个非常大的表进行去重，该表有许多列，速度非常重要。因此我使用了这种适用于任何表格的方法：

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

- S Wright

9

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

编辑：对于结果集行的动态编号，请参见下文，但这可能相当于Oracle的ROWNUM，我认为从页面上所有评论中可以看出你想要上面的内容。对于SQL Server 2005及更高版本，您可以使用新的排序函数函数实现行的动态编号。

例如，我在我的一个查询中这样做：

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

将会给你：

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

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

- Xiaofu

我认为标识列在表中可以唯一标识一行，但不能在整个数据库中唯一标识。 - tuinstoel

1

这是正确的，但它符合我在Oracle文档中看到的ROWID定义：“外部数据类型ROWID标识数据库表中的特定行”...但我看到你是因为我在顶部打错字才这么说的。 :) 谢谢你指出来。 - Xiaofu

一行的“编号”不是 ROWID。ROWID 包含行的物理位置，它与唯一数字不同。特别是它在数据库中所有表格中都是唯一的（当使用特殊存储技术时除外）。 - user330315

8

请查看新的ROW_NUMBER函数。它的使用方法如下：

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

- Daren Thomas

21

我认为这是用来替代Rownum而不是Rowid的。 - tuinstoel

6

以上的一些回答可以绕过直接引用特定行的缺点，但如果其他行发生更改，则不能正常工作。这是我的标准，哪些答案在技术上不足。

Oracle ROWID 的常见用途是提供一个（有点）稳定的方法来选择行并稍后返回到该行以处理它（例如，更新它）。找到一行的方法（复杂的连接、全文搜索或逐行浏览并对数据应用过程性测试）可能不容易或安全地重复使用以符合 UPDATE 语句的要求。

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

还可以简单地使用 FOR UPDATE 并在过程性程序中保持 CURSOR 打开。然而，在大型或复杂的批处理中，这可能会变得很昂贵。

警告：即使是 Oracle 的 ROWID，在选择和更新之间，例如，如果 DBA 重建数据库，则不会稳定，因为它是物理行标识符。因此，ROWID 设备应仅在范围明确的任务中使用。

- Vincent

5

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

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

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

- user2793105

4

如果您只是想为小型数据集提供基本的行编号，可以尝试像这样的东西吗？

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

- user261189

但对于一些查找快速添加ID的观众来说，它是有效的，他们可能不知道ROWID是什么。 - Graeme

4

来自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 IDENTITY column 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.

- cjs

1

请参阅http://msdn.microsoft.com/en-us/library/aa260631(v=SQL.80).aspx，在SQL Server中，时间戳与DateTime列不同。它用于唯一标识数据库中的行，而不仅仅是表格。这可用于乐观并发性。例如： UPDATE [Job] SET [Name]=@Name, [XCustomData]=@XCustomData WHERE ([ModifiedTimeStamp]=@Original_ModifiedTimeStamp AND [GUID]=@Original_GUID ModifiedTimeStamp确保您正在更新原始数据，并且如果另一个更新已针对该行发生，则会失败。

- Jim

1

ROWID是Oracle表上的隐藏列，因此对于SQL Server，需要自行构建。添加一个名为ROWID的列，并将默认值设置为NEWID()。

操作步骤：在SQL Server中为现有表添加带有默认值的列

- Erik

1

这不更像是一条注释吗？ - The Unfun Cat

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

从Oracle文档

ROWID伪列

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

对象的数据对象号

行所在的数据文件中的数据块

行在数据块中的位置（第一行为0）

行所在的数据文件（第一个文件为1）。文件号相对于表空间而言。

SQL Server中最接近此功能的是rid，它有三个组成部分File:Page:Slot。

在SQL Server 2008中，可以使用未记录和不支持的%%physloc%%虚拟列来查看这一信息。它返回一个binary(8)值，其中包含前四个字节的页面ID、两个字节的文件ID，以及页面上槽的位置的两个字节。

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

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子句中使用此功能是可能的，但并不推荐。

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

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

要反转之前两个函数执行的过程，并获取与已知File、Page、Slot值相对应的binary(8)值，可以使用以下内容。

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