不带ORDER BY的SQL Server 2005 ROW_NUMBER() [英] SQL Server 2005 ROW_NUMBER() without ORDER BY

问题描述

我正在尝试使用

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不是一个标识列，这就是为什么我必须找到一种自己自动递增的方法的原因.

TargetTable.ID is not an identity column, which is why I have to find a way to auto-increment it myself.

我知道我可以使用游标，或者创建一个具有标识列和FieldValue字段的表变量，进行填充，然后在我的insert into...select中使用它，但这不是很有效.我尝试使用ROW_NUMBER函数进行递增，但实际上我无法在SourceTable中使用合法的ORDER BY字段，并且希望保持SourceTable的原始顺序(如果可能).

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

有人可以提出任何建议吗?

Can anyone suggest anything?

推荐答案

您可以避免如下指定显式顺序:

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};

但是，请注意，这只是避免指定顺序的一种方法，并且不能保证，将保留所有原始数据的顺序.还有其他一些因素可能导致结果排序，例如外部查询中的ORDER BY.为了完全理解这一点，必须认识到未排序(以某种特定方式)"的概念与保持原始顺序"(以某​​种特定方式进行排序！)不同.我认为，从纯关系数据库的角度来看，后一种概念不存在，按定义 (尽管可能存在违反此概念的数据库实现，但SQL Server并非其中之一)他们).

However, please note that is merely a way to avoid specifying an ordering and does NOT guarantee that any original data ordering will be preserved. There are other factors that can cause the result to be ordered, such as an ORDER BY in 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).

锁定提示的原因是为了防止在查询的执行部分之间使用您计划使用的值插入其他进程的情况.

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.

注意:许多人使用(SELECT NULL)来解决窗口函数的ORDER BY子句中不允许使用常量"的限制.由于某些原因，我更喜欢1而不是NULL.

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 1 over NULL.

另外:我认为，identity列要优越得多，应该改用它.并发性专门锁定整个表不是很好.轻描淡写.

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.

这篇关于不带ORDER BY的SQL Server 2005 ROW_NUMBER()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！