Temporary Table and Order By
-
I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set. Thanks in advance.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
-
I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set. Thanks in advance.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
-
data in temp table will remain in the state and order as its been inserted untill you make any operation(sorting,shorting etc) on it
-
are you sure? someone answered differently from another (dbforums) forum.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
The somone else doesn't know what they are talking about. Simple to test, create a table with 1 field, insert 2 records and then select from the records. They will ALWAYS be returned in the order they were inserted unless you order them. Here you go
DECLARE @tbl TABLE (sField VARCHAR(20))
INSERT @tbl (sField) VALUES ('Z')
INSERT @tbl (sField) VALUES ('A')SELECT * FROM @tbl
Never underestimate the power of human stupidity RAH
-
I created a temp table with an order by clause. If later on I query it, would my result set still follow the order by sort or is it going to be indeterminate like querying an actual table? This is hard to test because 999 of 1000 times I might get an ordered result set. Thanks in advance.
---------------------------------------------------------- Lorem ipsum dolor sit amet.
Microsoft do not guarantee the order any records are returned unless you put an order by on the select. The default is to return the data in the prmary key order, but this cannot be relied on.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
The somone else doesn't know what they are talking about. Simple to test, create a table with 1 field, insert 2 records and then select from the records. They will ALWAYS be returned in the order they were inserted unless you order them. Here you go
DECLARE @tbl TABLE (sField VARCHAR(20))
INSERT @tbl (sField) VALUES ('Z')
INSERT @tbl (sField) VALUES ('A')SELECT * FROM @tbl
Never underestimate the power of human stupidity RAH
The first thing to clear up is that you cannot control the insertion order of rows using ORDER BY, so the assumption on which the question is based is inaccurate. This is well documented in Books Online and elsewhere (see refs below). If you insert rows one at a time then it is true that the insertion order determines the allocation order represented by IAM pages. It will not determine the eventual ordering of data pages however, or even necessarily the logical ordering of rows within a page, especially if page splits subsequently occur. In a query, there is no way to specify how rows should be sorted other than by using ORDER BY. This is an important feature because it allows the optimiser to choose the most efficient path to the data based on requirements. By leaving out ORDER BY you signal to the optimiser "I don't care about order" and the optimiser chooses an execution plan accordingly. Check this for yourself by looking at the execution plan of a query without ORDER BY. You will typically see a scan showing "Ordered=False". Depending on various factors the optimiser can choose different strategies to retrieve the data for an unordered scan, which may or may not mimic the order in which rows were first inserted. Some relevant factors that influence the actual plan and sorting are: fragmentation, covering indexes and whether an "Advanced" scan is used (Enterprise Edition only). Of course it is easy to contrive examples where the query order matches the insertion order and just as easy to make up examples where it doesn't. Just as surely, those nice safe examples could return different results if the conditions under which they ran were changed in some subtle way. The correct and documented behaviour is that the sort order of a query without ORDER BY is undefined. If you ever assume otherwise then you are in unsupported territory and you must decide for yourself whether it is worth the possible risk of leaving out ORDER BY. Hope this helps. References Insertion order not guaranteed for SELECT INTO with ORDER BY: http://msdn.microsoft.com/en-us/library/ms188385.aspx Insertion order not guaranteed for INSERT with ORDER BY (SQL Engine Team Blog): http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx Advanced Scan: http://msdn.microsoft.com/en-us/library/ms191475.asp
-
The first thing to clear up is that you cannot control the insertion order of rows using ORDER BY, so the assumption on which the question is based is inaccurate. This is well documented in Books Online and elsewhere (see refs below). If you insert rows one at a time then it is true that the insertion order determines the allocation order represented by IAM pages. It will not determine the eventual ordering of data pages however, or even necessarily the logical ordering of rows within a page, especially if page splits subsequently occur. In a query, there is no way to specify how rows should be sorted other than by using ORDER BY. This is an important feature because it allows the optimiser to choose the most efficient path to the data based on requirements. By leaving out ORDER BY you signal to the optimiser "I don't care about order" and the optimiser chooses an execution plan accordingly. Check this for yourself by looking at the execution plan of a query without ORDER BY. You will typically see a scan showing "Ordered=False". Depending on various factors the optimiser can choose different strategies to retrieve the data for an unordered scan, which may or may not mimic the order in which rows were first inserted. Some relevant factors that influence the actual plan and sorting are: fragmentation, covering indexes and whether an "Advanced" scan is used (Enterprise Edition only). Of course it is easy to contrive examples where the query order matches the insertion order and just as easy to make up examples where it doesn't. Just as surely, those nice safe examples could return different results if the conditions under which they ran were changed in some subtle way. The correct and documented behaviour is that the sort order of a query without ORDER BY is undefined. If you ever assume otherwise then you are in unsupported territory and you must decide for yourself whether it is worth the possible risk of leaving out ORDER BY. Hope this helps. References Insertion order not guaranteed for SELECT INTO with ORDER BY: http://msdn.microsoft.com/en-us/library/ms188385.aspx Insertion order not guaranteed for INSERT with ORDER BY (SQL Engine Team Blog): http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx Advanced Scan: http://msdn.microsoft.com/en-us/library/ms191475.asp
Thanks for that info - I have never actually dug under the hood of the way a db treats the rows as I also am of the opinion that I don't really care, if I need a sort order I will impose it.
Never underestimate the power of human stupidity RAH