January 27, 2017 at 7:55 am
For once I'm going to ask what is probably a relatively simple question for most of you. Just for the record, I think the answer is yes but I'm looking for some validation.
I've got a transaction (soon to be a Fact) table with a thousand rows and 100 distinct descriptions. I have to derive a Dimension to associate those 100 distinct descriptions. (Yes, I will be introducing the concept of Master Data and Data Governance later but right now it is what it is.) Each of these facts has a date associated with it, call it transaction date. If I say:
SELECT DISTINCT Description, TransactionDate
FROM MyTable
ORDER BY TransactionDate
I'll get most, if not all, of the 1000 rows.
My question. If I put the above values in a temp table and then said:
SELECT DISTINCT Description
FROM #MyTempTable
Would it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?
"Beliefs" get in the way of learning.
January 27, 2017 at 7:56 am
No.
The only thing that guarantees the order data is returned is an "ORDER BY" clause in your SELECT statement.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 28, 2017 at 4:24 pm
Robert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?
No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 31, 2017 at 4:07 am
GilaMonster - Saturday, January 28, 2017 4:24 PMRobert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.
What if he put a clustered index on the TransactionDate column? Would that not guarantee a sort order?
January 31, 2017 at 4:12 am
kevaburg - Tuesday, January 31, 2017 4:07 AMGilaMonster - Saturday, January 28, 2017 4:24 PMRobert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.What if he put a clustered index on the TransactionDate column? Would that not guarantee a sort order?
No.
Consider if a query goes parallel - there's no way to guarantee which thread is going to finish / present its results first.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 31, 2017 at 4:16 am
kevaburg - Tuesday, January 31, 2017 4:07 AMGilaMonster - Saturday, January 28, 2017 4:24 PMRobert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.What if he put a clustered index on the TransactionDate column? Would that not guarantee a sort order?
Still no. Take a look at an old post by Hugo Kornelis. Old, but still one of my faivorit posts:-)
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/01/16/Beatles_2D00_vs_2D00_Stones_2D00_explanation.aspx
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 31, 2017 at 4:28 am
Adi Cohn-120898 - Tuesday, January 31, 2017 4:16 AMkevaburg - Tuesday, January 31, 2017 4:07 AMGilaMonster - Saturday, January 28, 2017 4:24 PMRobert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.What if he put a clustered index on the TransactionDate column? Would that not guarantee a sort order?
Still no. Take a look at an old post by Hugo Kornelis. Old, but still one of my faivorit posts:-)
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/01/16/Beatles_2D00_vs_2D00_Stones_2D00_explanation.aspxAdi
Well, that is that cleared up..... 😀
February 1, 2017 at 8:38 am
kevaburg - Tuesday, January 31, 2017 4:07 AMGilaMonster - Saturday, January 28, 2017 4:24 PMRobert Frasca - Friday, January 27, 2017 7:55 AMWould it be guaranteed that my returned values will still be in TransactionDate order because they are in the temp table?No.
Tables are unordered sets of rows, therefore the rows in the temp table cannot be said to be in any order. Hence your SELECT, without an Order By may return the rows in any order whatsoever.
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.What if he put a clustered index on the TransactionDate column? Would that not guarantee a sort order?
No ORDER BY on the SELECT that returns data, no guarantee of order. End of Story.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2017 at 2:58 pm
What you would need to do is this:
SELECT Description
FROM (
SELECT Description, MIN(TransactionDate) AS TransactionDate
FROM MyTable
GROUP BY Description
) AS derived
ORDER BY TransactionDate
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply