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不是自增列，这就是为什么我必须找到一种方法来自动增加它。

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

有人能提供任何建议吗？

- Fragilerus

---

源表上的聚集索引是什么？当你说“SourceTable的原始顺序”时，我认为你指的就是它。如果它是一个堆，则没有特定的顺序。 - Martin Smith

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。您使用的内容取决于您自己。

- ErikE

---

@Emtucifor：第一个可以正常工作。至于第二个，事实上它确实会返回一个结果，无论如何都是一行。如果表为空，则返回NULL，因此不需要子查询，只需在MAX()上使用ISNULL()，但不要在其中使用。我曾经设法先忽略它，实际上当我在SO上阅读某人的帖子时（现在不记得是谁了），偶然发现了这一点。在发布更正之前进行了检查。为什么更好？嗯，它看起来更简单，更容易阅读，我认为。 - Andriy M

这并不一定是正确的，如果外部查询有 ORDER BY 子句，或者在其他边缘情况下，可以在这里看到：https://dev59.com/fmMk5IYBdhLWcg3wyw7H。 - Lukas Eder

@LukasEder 我的回答是正确的，但可能不够完整。如果有人想要“保留原始顺序”，而不知道在SQL Server中这是一个无意义的概念，他们可能会尝试使用这段代码来实现--但会失败。我已经更新了我的回答，以解决这个问题。 - ErikE

太好了，感谢修复。 - Lukas Eder

@Mohammad Anini，我已经撤销了你的编辑；我更喜欢这种语法，在SQL Server中完全有效。除非你百分之百、绝对、肯定需要编辑代码，否则请不要编辑。 - ErikE

Answer 2

您可以通过使用 order by (select null) 来忽略排序，例如：

declare @IDOffset int;
select  @IDOffset = max(isnull(ID, 0)) from TargetTable

insert  into TargetTable(ID, FIELD)
select  row_number() over (order by (select null)) + @IDOffset, FeildValue
  from  SourceTable
 where  [somecondition]

- Mohammad Anini

---

所有人：请勿使用此答案，因为没有锁提示来确保 max() 结果在 insert 完成之前仍然有效。两个并发客户端可以获得相同的最大值，然后尝试插入相同的值，这将导致第二个客户端抛出关键约束违规错误。即使在此答案中纠正了并发问题，它也将与另一个提前5年半编写的答案在功能上完全相同，因此增加的价值很小。 - ErikE