Ordering guarantees in SQL Server...(SQLServer中保证排序不被优化，insert into ....select...order by时插入顺序不对)

原文： http://blogs.msdn.com/b/sqltips/archive/2005/07/20/441053.aspx

Ordering guarantees of queries in various context is a common source of confusion. For example, a common workaround to make the results from querying a view ordered is to introduce TOP 100 PERCENT and ORDER BY in the view definition. But this however does not guarantee order in the actual results sent to the client since the query optimizer will re-order operations to find more efficient query plans. Note that even though this topic applies to SQL Server 2005 most of the rules are valid for SQL Server 2000 too.

 

Here are the scenarios that guarantee ordering:

1. If you have an ORDER BY in the top-most SELECT block in a query, the presentation order of the results honor that ORDER BY request
2. If you have a TOP in the same SELECT block as an ORDER BY, any TOP computation is performed with respect to that ORDER BY. For example, if there is a TOP 5 and ORDER BY clause then SQL Server picks the TOP 5 rows within a given sort.  Note that this does not guarantee that subsequent operations will somehow retain the sort order of a previous operation. The query optimizer re-orders operations to find more efficient query plans
3. Cursors over queries containing ORDER BY in the top-most scope will navigate in that order
4. INSERT queries that use SELECT with ORDER BY to populate rows guarantees how identity values are computed but not the order in which the rows are inserted
5. SQL Server 2005 supports a number of new "sequence functions" like RANK(), ROW_NUMBER() that can be performed in a given order using a OVER clause with ORDER BY
6. For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.

总结：

尝试在 select 查询中加入top,row_number等依赖order by的关键字即可。

注意：不要假设insert into ... select ....order by中的记录顺序，就是你查询的顺序，如果真的要保证向表中插入的记录顺序就是查询顺序的话，需要做一些特殊处理，这个

在建议第4点已经给出。

实验：

1、问题演示：

DECLARE @tmpPerTable TABLE (
[Name] VARCHAR(100),
Age INT,
[Address] VARCHAR(500)
)

INSERT INTO @tmpPerTable
SELECT  *
FROM 
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age

SELECT * FROM @tmpPerTable

执行结果：

(6 行受影响)
Name       Age         Address
---------- ----------- ----------
Jim        15          Road 1
Tim        15          Road 2
Sim        12          Road 2
Sam        11          Road 4
Jack       9           Road 1
Tom        16          Road 3
(6 行受影响)

可以看到我们试图的排序，在插入操作过后已经丢失了。

2、参照建议中使用 top 100 percent,看是否可以解决：

DECLARE @tmpPerTable TABLE (
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)

INSERT INTO @tmpPerTable
SELECT TOP 100 PERCENT *
FROM 
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age

SELECT * FROM @tmpPerTable

执行结果：

(6 行受影响)
Name       Age         Address
---------- ----------- ----------
Jim        15          Road 1
Tim        15          Road 2
Sim        12          Road 2
Sam        11          Road 4
Jack       9           Road 1
Tom        16          Road 3
(6 行受影响)

可以看到插入的结果也没有应用到指定的排序,所以不能使用 top 100 percent来强制插入时应用排序

3、使用top 关键字

DECLARE @tmpPerTable TABLE (
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)


INSERT INTO @tmpPerTable
SELECT TOP 100 *
FROM 
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a
ORDER BY a.[Address],a.Age


SELECT * FROM @tmpPerTable

执行结果：

(6 行受影响)
Name       Age         Address
---------- ----------- ----------
Jack       9           Road 1
Jim        15          Road 1
Sim        12          Road 2
Tim        15          Road 2
Tom        16          Road 3
Sam        11          Road 4
(6 行受影响)

这个可以了，插入的顺序和我们查询排序的顺序是一致的，但是这里存在一个问题是，我们怎么知道结果集返回记录数，所以top 仅可以用于已知

结果集记录条数的情况；不要假定，插入数据表中记录顺序就是select语句中order by的顺序，尽管这个在大多数情况下是ok，但是在某些特殊情况下

可能会有问题，如：MAXDOP > 1的情况（即：max degree of parallelism option,that is the number of processors employed to run a single statement, for each parallel plan execution. ）

4、参照建议中的在sql 2005以上版本，使用rank,row_number顺序函数，这个我们可以不用知道记录条数，但是要多添加一个辅助字段。

DECLARE @tmpPerTable TABLE (
seq INT IDENTITY(1,1), --这里不得不多定义一列
[Name] VARCHAR(10),
Age INT,
[Address] VARCHAR(10)
)

Set Identity_insert ON;

INSERT INTO @tmpPerTable
SELECT ROW_NUMBER() OVER (ORDER BY a.[Address],a.Age), *
FROM 
(
SELECT 'Jim' AS 'Name',15 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tim' AS 'Name',15 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sim' AS 'Name',12 AS 'Age','Road 2' AS 'Address'
UNION ALL
SELECT 'Sam' AS 'Name',11 AS 'Age','Road 4' AS 'Address'
UNION ALL
SELECT 'Jack' AS 'Name',9 AS 'Age','Road 1' AS 'Address'
UNION ALL
SELECT 'Tom' AS 'Name',16 AS 'Age','Road 3' AS 'Address'
) a

Set Identity_insert OFF;

SELECT * FROM @tmpPerTable

执行结果：

(6 行受影响)
seq         Name       Age         Address
----------- ---------- ----------- ----------
1           Jack       9           Road 1
2           Jim        15          Road 1
3           Sim        12          Road 2
4           Tim        15          Road 2
5           Tom        16          Road 3
6           Sam        11          Road 4

(6 行受影响)

可以看到这个结果，在插入的时候也是应用到了我们查询时候的排序的。这里强制利用identity字段，所以可以保证插入的顺，是order by的顺序；

但是即便这样也不要认为最终从@tmpPerTable中取出的数据顺序就是插入时order by的顺序，切记！

参考阅读：

1、http://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause

The order of a SQL Select statement without Order By clause

No, that behavior cannot be relied on. The order is determined by the way the query planner has decided to build up the result set. simple queries like select * from foo_table are likely to be returned in the order they are stored on disk, which may be in primary key order or the order they were created, or some other random order. more complex queries, such as select * from foo where bar < 10 may instead be returned in order of a different column, based on an index read, or by the table order, for a table scan. even more elaborate queries, with multipe where conditions, group byclauses, unions, will be in whatever order the planner decides is most efficient to generate.

The order could even change between two identical queries just because of data that has changed between those queries. a "where" clause may be satisfied with an index scan in one query, but later inserts could make that condition less selective, and the planner could decide to perform a subsequent query using a table scan.

2、 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=186664

3、 https://support.microsoft.com/en-us/kb/273586

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

4、 https://technet.microsoft.com/en-us/library/ms181007(v=sql.105).aspx

max degree of parallelism Option