July 22, 2013 at 4:52 am
Toreador (7/22/2013)
PRR.DB (7/22/2013)
If table doesn't have clustered index , there is no guarantee for order , if table having clustered index then result will display clustered index column order.Is my answer correct or not? Can any one explain on it?
It may well display in this order. But there is no guarantee that it always will, or that the behaviour will be the same in the next release. There is nothing in the standard or in the documentation that states what the sequence will be, therefore you cannot rely on it.
Which is why one should never assume that results with appear in any order that is desired, unless you specify that order in your query.
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
July 22, 2013 at 5:16 am
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
When we declare a clustered index on a column then it will gives us the data in a sorted manner or not?
As per the BOl and I read articles its said that after declaring a clustered index it will do reordering of data in a physical order, does this not mean that data will come in sorted manner?
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 5:23 am
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...
For storage, you are right - the physical storage structure of the data is determined by the clustered index (though not necessarily exactly in the way many people think it is).
But this question is about the order in which data is returned from a query that does not use ORDER BY. And that order is not related to either the clustered index, the primary key, the insertion order, or the physical storage structure.
July 22, 2013 at 5:42 am
kapil_kk (7/22/2013)
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
When we declare a clustered index on a column then it will gives us the data in a sorted manner or not?
As per the BOl and I read articles its said that after declaring a clustered index it will do reordering of data in a physical order, does this not mean that data will come in sorted manner?
My previous reply was interrupted by a long phone conversation, so I did not see the new messages.
The answer to your questions is: no. Without ORDER BY, there is no guarantee that the rows will be returned in clustered index order. Or in any other particular order. The only thing in SQL Server that guarantees that rows from a query are returned to the client in a particular order is an explicit ORDER BY clause, on the outermost level of the query. All other situations where you happen to see a particular order are side effects of how stuff is implemented, undocumented, unguaranteed, and prone to change.
July 22, 2013 at 5:55 am
Hugo Kornelis (7/22/2013)
kapil_kk (7/22/2013)
John Mitchell-245523 (7/22/2013)
kapil_kk (7/22/2013)
Yes you are right but user PRR asked a scenario only for clustered index and if a clustered index is used then it will reorder of the data in physical order...You're confusing the way the data is stored with the way it's returned. As Hugo explained, they're not guaranteed to be the same.
John
Edit - corrected typo.
When we declare a clustered index on a column then it will gives us the data in a sorted manner or not?
As per the BOl and I read articles its said that after declaring a clustered index it will do reordering of data in a physical order, does this not mean that data will come in sorted manner?
My previous reply was interrupted by a long phone conversation, so I did not see the new messages.
The answer to your questions is: no. Without ORDER BY, there is no guarantee that the rows will be returned in clustered index order. Or in any other particular order. The only thing in SQL Server that guarantees that rows from a query are returned to the client in a particular order is an explicit ORDER BY clause, on the outermost level of the query. All other situations where you happen to see a particular order are side effects of how stuff is implemented, undocumented, unguaranteed, and prone to change.
create table Orderby(ID int)
go
insert into Orderby
select 1
union all
select 2
union all
select 5
union all
select 4
union all
select 3
union all
select 7
union all
select 6
go
select * from Orderby
go
CREATE CLUSTERED INDEX [ClusteredIndex_Order1_ID] ON [dbo].[Orderby]
(
[ID] ASC
)
go
select * FROM Orderby
GO
DROP TABLE Orderby
In this example after putting clustered index we will data in ascending sorted order.... while before adding clustered index data was not coming in an order...
I am confused now :doze:
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 6:09 am
kapil_kk (7/22/2013)
(...)In this example after putting clustered index we will data in ascending sorted order.... while before adding clustered index data was not coming in an order...
I am confused now :doze:
Data will sometimes be returned in clustered index order. In a case such as your repro, it will (based on current product versions).
But that repro does not prove that it will always happen that way. Only that it sometimes happens that way.
If you checked out my blog post on beatles versus stones, you will have seen one situation where data was NOT returned in clustered index order. Other examples are harder to reproduce, but if you create a table with several million rows and query on a computer with mulitple cores, you have a chance of seeing how parallel processing can change the order in which rows are returned.
July 22, 2013 at 6:27 am
Hugo Kornelis (7/22/2013)
kapil_kk (7/22/2013)
(...)In this example after putting clustered index we will data in ascending sorted order.... while before adding clustered index data was not coming in an order...
I am confused now :doze:
Data will sometimes be returned in clustered index order. In a case such as your repro, it will (based on current product versions).
But that repro does not prove that it will always happen that way. Only that it sometimes happens that way.
If you checked out my blog post on beatles versus stones, you will have seen one situation where data was NOT returned in clustered index order. Other examples are harder to reproduce, but if you create a table with several million rows and query on a computer with mulitple cores, you have a chance of seeing how parallel processing can change the order in which rows are returned.
Thanks Hugo.........
I read your article beatles versus stones its a very good article.....
Now my misconception is clear about clustered index....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2013 at 7:00 am
Today's QtD is worthwhile.
thanks hugo and kapil 🙂
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
July 22, 2013 at 7:02 am
This was removed by the editor as SPAM
July 22, 2013 at 7:09 am
Koen Verbeeck (7/22/2013)
Didn't even have to think for this one (which makes it an excellent Monday question)! 😀
+ 1. Thanks for the question and thanks for the great discussion.
July 22, 2013 at 7:23 am
Here is a good article that explains this common misconception and provides an easy to follow example of ordering with no order by clause going wrong.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 22, 2013 at 7:31 am
Nice one.....
July 22, 2013 at 7:37 am
Another consideration on this topic: specify _exactly_ what you want. If you need results in a particular order, you should request it that way.
A {large}% of the QotD are about what goes wrong when developers assume things that aren't true. Usually the 'solution' to this problem (after "read BOL") is to be very clear and explicitly state what you want.
Another reason to be explicit is for the sake of the maintenance programmer who has to add a feature or tune for performance. Before you malign their potential lack of skill, that person might be you in 6 months! 🙂
If a developer relies on the existence of an index to order results of a query and the DBA changes the index to improve server-wide performance but it "breaks" the developer's app - who is at fault? I say the developer - but it doesn't matter what pitfalls he or she left, it's the DBA who gets the call for the now-malfunctioning app. The situation is made worse when the dev has already left the company... so now a simple fix must start with a sleuthing operation to find what/where to fix.
July 22, 2013 at 7:38 am
Easy but good to know
July 22, 2013 at 9:07 am
Good one...
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply