没有 ORDER BY 的 SQL Server 2005 ROW_NUMBER()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4810627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2005 ROW_NUMBER() without ORDER BY
提问by Fragilerus
I am trying to insert from one table into another using
我试图从一个表插入到另一个使用
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 is not an identity column, which is why I have to find a way to auto-increment it myself.
TargetTable.ID 不是标识列，这就是为什么我必须自己找到一种方法来自动增加它。
I know I can use a cursor, or create a table variable with an identity column and a FieldValue field, populate that, then use it in my insert into...select, but that is not very efficient. I tried using the ROW_NUMBER function to increment, but I really don't have a legitimate ORDER BY field in the SourceTable that I can use, and would like to keep the original order of the SourceTable (if possible).
我知道我可以使用游标，或者创建一个带有标识列和 FieldValue 字段的表变量，填充它，然后在我的insert into...select. 我尝试使用 ROW_NUMBER 函数进行递增，但我确实在 SourceTable 中没有可以使用的合法 ORDER BY 字段，并且希望保留 SourceTable 的原始顺序（如果可能）。
Can anyone suggest anything?
任何人都可以提出任何建议吗？
回答by ErikE
You can avoid specifying an explicit ordering as follows:
您可以避免指定显式排序，如下所示：
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};
However, please note that is merely a way to avoid specifying an ordering and does NOT guaranteethat any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BYin the outer query. To fully understand this, one must realize that the concept "not ordered (in a particular way)" is not the same as "retaining original order" (which IS ordered in a particular way!). I believe that from a pure relational database perspective, the latter concept does not exist, by definition(though there may be database implementations that violate this, SQL Server is not one of them).
但是，请注意，这只是一种避免指定排序的方法，并不保证将保留任何原始数据排序。还有其他因素可能导致结果被排序，例如ORDER BY在外部查询中。要完全理解这一点，必须意识到“未排序（以特定方式）”的概念与“保留原始顺序”（以特定方式排序！）不同。我认为，从一个纯粹的关系型数据库的角度来看，后者的概念不存在，根据定义（虽然有可能是数据库实现违反本时，SQL Server是不是其中之一）。
The reason for the lock hints is to prevent the case where some other process inserts using the value you plan to use, in between the parts of the query executing.
锁定提示的原因是为了防止某些其他进程使用您计划使用的值在执行查询的部分之间插入的情况。
Note: Many people use (SELECT NULL)to get around the "no constants allowed in the ORDER BY clause of a windowing function" restriction. For some reason, I prefer 1over NULL.
注意：许多人(SELECT NULL)习惯于绕过“窗口函数的 ORDER BY 子句中不允许使用常量”限制。出于某种原因，我更喜欢1了NULL。
Also: I think an identity column is far superior and should be used instead. It's not good for concurrency to exclusively lock entire tables. Understatement.
另外：我认为身份列要好得多，应该改用。独占锁定整个表对并发性不利。轻描淡写。
回答by Mohammad Anini
You can ignore the ordering by using order by (select null)like this:
您可以使用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]
