June 25, 2009 at 8:42 am
Is there a difference between how SQL 2000 and SQL 2005 handles sorting records outside of the order by clause?
For example, supposed I am using the following query to retreive records and thing1, thing2, and thing3 are the same for 3 records, how are these 3 records sorted? Is it different in SQL 2000 and SQL 2005?
select * from mytable order by thing1, thing2, thing3
Thanks for any help.
June 25, 2009 at 9:21 am
According to relational theory a table is an unordered set.
This means that the order of rows outside the ORDER BY clause cannot be guaranteed.
If you want rows returned in the same order each time the query is run, make the ORDER BY clause unique by adding the primary key.
eg:
ORDER BY thing1, thing2, thing3, mytableId
June 25, 2009 at 6:08 pm
I'm a little confused by your post because in the first sentence you mention "sorting...outside of the order by clause" but then your select statement clearly has the order by clause included.
Also, I'm wondering whether you're trying to get information on the internal workings between SQL 2K and 2K5 out of curiosity or you are looking to solve a problem.
If you are, in fact, using the order by clause in your query and the results are coming back differently from two different databases, then I would look into how the collation is setup on the server, database and columns on the table in question. Also, I've seen corrupt indexes cause problems in what gets returned -- try rebuilding the indexes and see if that helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply