SQL Server 2005 ROW_NUMBER() 不带 ORDER BY

Question

我正在尝试使用从一个表插入另一个表

DECLARE @IDOffset int;
SELECT @IDOffset = MAX(ISNULL(ID,0)) FROM TargetTable

INSERT INTO TargetTable(ID, FIELD)
SELECT [Increment] + @IDOffset ,FeildValue
FROM SourceTable
WHERE [somecondition]

TargetTable.ID 不是一个标识列，这就是为什么我必须找到一种方法来自己自动递增它。

我知道我可以使用游标，或者创建一个带有标识列和 FieldValue 字段的表变量，填充该变量，然后在我的 insert into...select 中使用它，但那就是效率不高。我尝试使用 ROW_NUMBER 函数来递增，但我确实在 SourceTable 中没有可以使用的合法 ORDER BY 字段，并且希望保留 SourceTable 的原始顺序(如果可能)。

有人可以提出建议吗？

Answer 1

您可以避免指定显式排序，如下所示:

INSERT dbo.TargetTable (ID, FIELD)
SELECT
   Row_Number() OVER (ORDER BY (SELECT 1))
      + Coalesce(
         (SELECT Max(ID) FROM dbo.TargetTable WITH (TABLOCKX, HOLDLOCK)),
         0
      ),
   FieldValue
FROM dbo.SourceTable
WHERE {somecondition};

但是，请注意，这只是避免指定排序的一种方法，并且不保证将保留任何原始数据排序。还有其他因素可能导致结果排序，例如外部查询中的 ORDER BY。为了充分理解这一点，我们必须认识到“未排序(以特定方式)”的概念与“保留原始顺序”(以特定方式排序!)不同。我相信，从纯粹的关系数据库角度来看，后一个概念不存在，根据定义(尽管可能存在违反这一点的数据库实现，但 SQL Server 不是其中之一)他们)。

在查询中计算 Max 并添加锁定提示的原因是为了防止由于并发进程在查询的各个部分之间插入使用您计划使用的相同值而导致的错误。查询执行。唯一的其他半合理的解决方法是在循环中执行 Max() 和 INSERT 多次，直到成功(距离理想的解决方案还很远)。使用标识列优越得多。独占锁定整个表对并发性不利，这是轻描淡写的说法。

注意:许多人使用 (SELECT NULL) 来绕过“窗口函数的 ORDER BY 子句中不允许使用常量”的限制。出于某种原因，我更喜欢 1 而不是 NULL。您使用什么取决于您。