SELECT * INTO 保留 SQL Server 2008 中的 ORDER BY 但不保留 2012 [英] SELECT * INTO retains ORDER BY in SQL Server 2008 but not 2012

问题描述

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

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.

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

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

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

推荐答案

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

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 select query.

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

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:

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;

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

I still abhor doing select * from Result after 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.

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

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 by clause.

这篇关于SELECT * INTO 保留 SQL Server 2008 中的 ORDER BY 但不保留 2012的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！