Tuesday, March 27, 2012

Do Inserts into Temptable enforce order of select?

I have a temp table which I am trying to do a select INTO. The temptable has an identity field which increments by one. In the select query, I am doing an order by. It does not appear that the temp table retains the physical ordering of the records from the order by. When I do a select * FROM temptable without an order by, the first record that is displayed as an id of 113. Then about the 42nd record in the temp table is the ID=1 record. In addition, the table records are not ordered by the original order of the select that inserted into the temp table.

From this I am led to believe that SQL does not enforce the orders of records on INSERT into statesments. Is this a correct assumption, or might there be something else going on?Please take a look at the blog post below:

http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Ordering is not guaranteed for SELECT queries unless you include an explicit ORDER BY in the outer-most query. Similarly, the order of insertion of rows is not guranteed either. The identity values are however generated based on the order specified in the ORDER BY in INSERT...SELECT statement.

No comments:

Post a Comment