In SQL Server 2005/2008, if an ordered record is inserted into a staging table, the records queried from the staging table are ordered (not dependent on order by and ordered), but starting with SQL Server 2012, even if the inserted recordset is orderly, The result of the query becomes unordered. You need to rely on order by or get an ordered result. For example, the following example:
SELECT into from ORDER by name;
SELECT from #tables;
As shown above, SQL Server 2014 is out of order, and I'm surprised how it began to become unordered from SQL Server 2012, which is like a black box for us, only to know that the result set has changed, but we don't know what internal changes are causing the phenomenon. Records not sorted in the table when using the ORDER by clause to insert data into Temp table The issue is described in the official forum as follows:
If you have special needs and require the data in the staging table to be ordered, you can solve this problem by creating a clustered index. As shown below:
SELECT into from WHERE 1 = 0;
CREATE CLUSTERED INDEX on #tables (name);
into SELECT from ORDER by name;
SELECT from #tables;
Sorting problems for SQL SERVER temporary tables