September 17, 2008 at 2:42 am
If there's a clustered index on a SQL Server 2000 table, and data is SELECTed from it without an "order by" clause, the resulting output uses the index and looks sorted, but can it be guaranteed to be sorted using the index? Is not using "order by" an optimisation which works, or does it mean that in some circumstances the data won't be sorted properly?
September 17, 2008 at 3:40 am
Unless you use an ORDER BY, the sequence is not guaranteed.
SQL Server will choose what index to use based on the actual query.
If you were to add a WHERE clause to the query, and SQL Server finds a different index that nicely supports your WHERE clause, you will likely end up with data presented in the sequence of that new index.
Also, what happens if you decide to change the clustered index in the future.
You shouldn't affect performance by adding the ORDER BY, if SQL Server is already using that index anyway.
September 17, 2008 at 3:50 am
Ian Scarlett (9/17/2008)
Unless you use an ORDER BY, the sequence is not guaranteed.SQL Server will choose what index to use based on the actual query.
If you were to add a WHERE clause to the query, and SQL Server finds a different index that nicely supports your WHERE clause, you will likely end up with data presented in the sequence of that new index.
Also, what happens if you decide to change the clustered index in the future.
You shouldn't affect performance by adding the ORDER BY, if SQL Server is already using that index anyway.
This is what I believe to be the case. I'm really looking for some statement or reference from Microsoft to this effect.
Cheers.
September 17, 2008 at 4:04 am
I don't know if you will find a statement from Microsoft, but the SQL standard says that if you don't use an ORDER BY, the server may return the results in any order.
Have a look at this http://en.wikipedia.org/wiki/Order_by_(SQL)
September 17, 2008 at 7:07 am
It's not difficut to prove for yourself that a clustered index doesn't guarantee order. Example:
CREATE TABLE tbl (x INT NOT NULL PRIMARY KEY CLUSTERED, z INT NOT NULL UNIQUE);
INSERT INTO tbl (x,z) VALUES (1,4);
INSERT INTO tbl (x,z) VALUES (2,3);
INSERT INTO tbl (x,z) VALUES (3,2);
INSERT INTO tbl (x,z) VALUES (4,1);
SELECT x,z FROM tbl;
x z
----------- -----------
4 1
3 2
2 3
1 4
(4 row(s) affected)
Tested on SQL Server 2008. YMMV
September 25, 2008 at 8:07 am
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply