July 7, 2011 at 3:21 am
Hi
I have a table_A with clustered index CL_dx on col A and Non clustered index on ColB,ColC (Both key columns). Now if I run a query like this:
Select B,C from table_A, then is it always guarenteed that that my resultset will be sorted by B,C?
i am actually asking , does non clustered index sorts the data on key column , always?
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 7, 2011 at 3:23 am
S_Kumar_S (7/7/2011)
Select B,C from table_A, then is it always guarenteed that that my resultset will be sorted by B,C?
No
i am actually asking , does non clustered index sorts the data on key column , always?
The nonclustered index is always logically sorted by its key columns. That does not guarantee that a query will return data in that order or any other. No Order By, 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
July 7, 2011 at 3:23 am
no,
the only way to guarantee the order of the results of a query is to use the ORDER BY clause.
July 7, 2011 at 3:33 am
ok, but i am sure, clustered index always gets sorted data on key columns, without adding ORDER BY, Always.
Gail, I have gone thru your series on Index basics. That is really good. But can you still explain "logically sorted" on key columns?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 7, 2011 at 3:36 am
S_Kumar_S (7/7/2011)
ok, but i am sure, clustered index always gets sorted data on key columns, without adding ORDER BY, Always.
no it doesn't not always.
July 7, 2011 at 3:37 am
As Gail siad, No Order By, no guarantee of order. End of Story.
July 7, 2011 at 3:38 am
S_Kumar_S (7/7/2011)
ok, but i am sure, clustered index always gets sorted data on key columns, without adding ORDER BY, Always.
No, that is not correct.
Ordering Guarantees in SQL Server
No Seatbelt - Expecting Order Without ORDER BY
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 7, 2011 at 3:48 am
S_Kumar_S (7/7/2011)
ok, but i am sure, clustered index always gets sorted data on key columns, without adding ORDER BY, Always.
The clustered index itself is always sorted. Queries that use it however, same thing I said earlier. No Order By, no guarantee of order. End of Story.
Gail, I have gone thru your series on Index basics. That is really good. But can you still explain "logically sorted" on key columns?
Each page in the index leaf level has a pointer to the next and previous page in the order of the index key. They may not be physically ordered or physically contiguous, but SQL always can tell what the next page in the index leaf is.
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
July 7, 2011 at 4:01 am
The answer to clustered index is surprising to me. I was under impression that query will always return sorted data, if we have clustered index.
I even tried it practically, and query always returned sorted data.
Can someone pls provide a scenario/script, which confirms my assumption was wrong.(ofcourse i admit, it was wrong). Just trying to understand why I am wrong.
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
July 7, 2011 at 4:06 am
S_Kumar_S (7/7/2011)
The answer to clustered index is surprising to me. I was under impression that query will always return sorted data, if we have clustered index.I even tried it practically, and query always returned sorted data.
In simple cases, a query using the clustered index probably will return data as ordered by the clustered key. However, without an order by, it's just the order that the last operator in the query left the data in.
The point is, it is NOT guaranteed. Sooner or later it breaks, and it breaks when the last operator in the query does not return the data in the clustered index order (either because it's not order-preserving or because the index scan didn't read the index in order at all)
Can someone pls provide a scenario/script, which confirms my assumption was wrong.(ofcourse i admit, it was wrong). Just trying to understand why I am wrong.
I can conjure several such scenarios, but it's not going to teach anything. If you really want one, go read the article by Conor Cunningham that Paul posted
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
July 7, 2011 at 4:06 am
S_Kumar_S (7/7/2011)
The answer to clustered index is surprising to me. I was under impression that query will always return sorted data, if we have clustered index.I even tried it practically, and query always returned sorted data.
Can someone pls provide a scenario/script, which confirms my assumption was wrong.(ofcourse i admit, it was wrong). Just trying to understand why I am wrong.
read this article that Paul posted
July 7, 2011 at 4:16 am
I just went rhu the article. Thaks all... it was a new learning for me....
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply