SELECT * INTO 保留 SQL Server 2008 中的 ORDER BY 但不保留 2012

SELECT * INTO retains ORDER BY in SQL Server 2008 but not 2012

提问by Rivka

Execute the following SQL in 2008 and 2012. When executed in 2008, the returned result is in its correct sort order. In 2012, the sortorder is not retained.

2008年和2012年分别执行以下SQL，2008年执行时，返回的结果是正确的排序顺序。2012 年不保留排序顺序。

Is this a known change? Is there a work-around for 2012 to retain the sort order?

这是一个已知的变化吗？2012 年是否有保留排序顺序的解决方法？

CREATE TABLE #MyTable(Name VARCHAR(50), SortOrder INT)
INSERT INTO #MyTable SELECT 'b', 2 UNION ALL SELECT 'c', 3 UNION ALL SELECT 'a', 1 UNION ALL SELECT 'e', 5 UNION ALL SELECT 'd', 4

SELECT * INTO #Result FROM #MyTable ORDER BY SortOrder

SELECT * FROM #Result

DROP TABLE #MyTable
DROP TABLE #Result

回答by Gordon Linoff

How can you tell what the order is inside a table by using select * from #result? There is no guarantee as to the order in a selectquery.

如何使用 判断表中的顺序select * from #result？无法保证select查询中的顺序。

However, the results are different on SQL Fiddle. If you want to guarantee that the results are the same, then add a primary key. Then the insertion order is guaranteed:

但是，SQL Fiddle 上的结果不同。如果要保证结果一样，那就加一个主键。然后保证插入顺序：

CREATE TABLE MyTable(Name VARCHAR(50), SortOrder INT)
INSERT INTO MyTable SELECT 'b', 2 UNION ALL SELECT 'c', 3 UNION ALL SELECT 'a', 1 UNION ALL SELECT 'e', 5 UNION ALL SELECT 'd', 4


select top 0 * into result from MyTable;

alter table Result add id int identity(1, 1) primary key;

insert into Result(name, sortorder)
    SELECT * FROM MyTable
    ORDER BY SortOrder;

I still abhor doing select * from Resultafter this. But yes, it does return them in the correct order in both SQL Server 2008 and 2012. Not only that, but because SQL Server guarantees that primary keys are inserted in the proper order, the records are even guaranteed to be in the correct order in this case.

select * from Result在这之后我仍然讨厌这样做。但是是的，它确实在 SQL Server 2008 和 2012 中以正确的顺序返回它们。不仅如此，而且因为 SQL Server 保证以正确的顺序插入主键，甚至可以保证记录以正确的顺序在这个案例。

BUT . . . just because the records are in a particular order on the pages doesn't mean they will be retrieved in that order with no order byclause.

但 。. . 仅仅因为记录在页面上按特定顺序排列并不意味着它们将按该顺序检索而没有order by子句。

回答by sgeddes

When using ORDER BYwith an INSERT, it has never been guaranteed to do anything other than control the order of the identity columnif present.

使用时ORDER BY用INSERT，它从来没有被保证做的比控制的顺序以外的任何标识列（如果存在）。

Prior to SQL Server 2012, the optimizer always produced a plan as if an identity column existed and thus appears to order correctly. SQL Server 2012 correctly does not assume an identity column exists, and only orders if the table actually has an identity column.

在 SQL Server 2012 之前，优化器总是生成一个计划，就好像标识列存在一样，因此看起来排序正确。SQL Server 2012 正确地不假定存在标识列，并且仅在表实际具有标识列时才进行排序。

So you can resolve this issue by adding an Identity column to your temp result table.

因此，您可以通过向临时结果表添加标识列来解决此问题。

However, you really should just add an ORDER BYclause to your SELECTstatement? SELECTstatements without an ORDER BYhave never been guaranteed to return the results in any specific order. Always add the ORDER BYclause to ensure you receive the results the way you expect.

但是，您真的应该ORDER BY在您的SELECT语句中添加一个子句吗？ SELECT不带 的语句ORDER BY从不保证以任何特定顺序返回结果。始终添加ORDER BY子句以确保您以预期的方式收到结果。

回答by Losbear

First, thanks sgeddes for the explanation, it helped a lot. The thing about defining a table variable or creating a temp table is you have to define it, and if you are going to go through the work of defining it, you might as well do the insert the correct way:

首先，感谢 sgeddes 的解释，它很有帮助。关于定义表变量或创建临时表的事情是你必须定义它，如果你要完成定义它的工作，你不妨以正确的方式进行插入：

INSERT INTO #Result (col1, col2...)
SELECT Col1, Col2... FROM #MyTable....

In my case, the ORDER BY in the INSERT was dynamic so when I called "SELECT * FROM #Result", the ORDER BY was unknown. My solution was to add a ROW_NUMBER column that I could hardcode into the SELECT when I was getting the data. Yea, I still have to include an ORDER BY, but at least it's static. Here's what I did:

就我而言，INSERT 中的 ORDER BY 是动态的，所以当我调用“SELECT * FROM #Result”时，ORDER BY 是未知的。我的解决方案是添加一个 ROW_NUMBER 列，我可以在获取数据时将其硬编码到 SELECT 中。是的，我仍然需要包含一个 ORDER BY，但至少它是静态的。这是我所做的：

--Insert
SELECT ROW_NUMBER() OVER (ORDER BY T.SortOrder ASC) AS RowNum, T.*  
INTO #Result 
FROM (SELECT * FROM #MyTable ...) AS T;

--Get data out
SELECT * FROM #Result ORDER BY RowNum;

Hope this helps.

希望这可以帮助。

回答by Snowww

You must to create ROW_NUMBER() order by column you want to order. Order by directly in the select, is ignored when insert is executed.

您必须按要订购的列创建 ROW_NUMBER() 订单。直接在select中order by，执行insert时会被忽略。

CREATE TABLE #MyTable(Name VARCHAR(50), SortOrder INT)

INSERT INTO #MyTable 
SELECT 'b', 2 
UNION ALL SELECT 'c', 3 
UNION ALL SELECT 'a', 1 
UNION ALL SELECT 'e', 5 
UNION ALL SELECT 'd', 4

SELECT  Name,
        ROW_NUMBER() OVER (ORDER BY MyTable.SortOrder) AS SortOrder 
INTO #Result 
FROM #MyTable AS MyTable
ORDER BY SortOrder

SELECT * FROM #Result

DROP TABLE #MyTable
DROP TABLE #Result

回答by patrick imbault

Workaround : You could add a SET ROWCOUNTbefore this type of query, then put if back to zero after to reset it, it works. This will force SQL to keep the order in your query.

解决方法：您可以SET ROWCOUNT在此类查询之前添加一个，然后在重置它之后将其归零，它可以工作。这将强制 SQL 保持查询中的顺序。

SET ROWCOUNT 1000000000

CREATE TABLE #MyTable(Name VARCHAR(50), SortOrder INT)
INSERT INTO #MyTable SELECT 'b', 2 UNION ALL SELECT 'c', 3 UNION ALL SELECT 'a', 1 UNION ALL SELECT 'e', 5 UNION ALL SELECT 'd', 4

SELECT * INTO #Result FROM #MyTable ORDER BY SortOrder

SELECT * FROM #Result

SET ROWCOUNT 0

DROP TABLE #MyTable
DROP TABLE #Result

回答by George Johnston

If you have different sorted results when querying each database, your collation is probablydifferent between the two.

如果在查询每个数据库时有不同的排序结果，则两者之间的排序规则可能不同。

Try explicitlysetting the collation in your query and see if your results are returned in the same order in both databases, e.g.

尝试在您的查询中显式设置排序规则，并查看您的结果是否在两个数据库中以相同的顺序返回，例如

SELECT * FROM #Result ORDER BY C1 COLLATE Latin1_General_CS_AS