Hi Friends,
The reason I write this “simplest of all blog post” is because of a conversation I had with an interviewee (Mr. X).
Me: What is a SQL Server clustered index?
Mr. X: blah blah…It physically orders a table..blah blah.. It physically orders a table.
Me: Ok. Can you please elaborate?
Mr. X: It physically orders the table… hmmmmm…. Blah blah…
Me: Suppose you have a table with a column having values 1-10. A Clustered index is created on that column as Create index ix_clust on T1 (sno desc). What will be the result of this query…
“Select * from T1”
Mr. X: Very simple – it will return rows from 1-10.
Me: Nice talking to you sir.
Well, below is what actually happens and clustered index physically orders a table .
Having said that, there’s lot more behind the statement “Clustered index physically orders the data in a table” which I will write in a while as I need to go now otherwise my friends will kill me as its TGIF
Happy Learning!!!
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
also the clustered index contains the data at the leaf levels, unlike nonclustered indexes which would contain pointers.
Hi Ahmad,
I would like to offer a few clarifications to your blog post.
First, a Clustering Key does NOT truly physically order your data records. It logically orders your data records. But if you look at the physical data pages on disk, your data records may or may not physically be in the order of your clustering key.
Second, it is a common misconception that if you execute a SELECT * without an ORDER BY, that SQL Server will output your resultset in the same order as defined by your Clustering Key. It will MOST of the time, but there are edge cases when it won’t. SQL Server will return data using what it believes is the fastest option available at that time. The only way to ensure the ordering of a given resultset is to use an ORDER BY.
Regards
Thanks for your clarification Andy. I do know that it logically orders the data records and I will cover it in my next blog also I will certainly work to find out the edge cases where it won’t return data as it should have been…
Hi Ahmad,
Just wanted to add to the thread so that everybody is aware of, if your data is large enough that the query optimizer has\can use Parallelism you can re-produce what Andy is trying to convey in second point. In simple language “take large volume of data and write a select query, make sure Max DOP is set to more than 1 or set to 0 and you have more than 1 processor you will start observing this behaviour. Worth Trying Smile
Thanks,
Sarabpreet Singh Anand
Thanks Sarab.. will certainly try it..
worth noting here. When you first create a clustered index on a table, SQL Server will allocate new pages and store data in sorted order i.e. physically sorted as per the index key, any subsequent inserts are added to the left space on the page as per the fill factor and the row offset data which is kept at the bottom of page sorts that data actually, for eg i have values 1,2,5,6,7 etc and created clustered index on this key. page could accomodate 1,2,5 for example and subsequently we insert 4, on the data page sql server will insert it after 5 but the row offset at the bottom of the page will make sure that 4 is selected before 5.
I hope i could explain what i really wanted to explain….Embarassed
Thanks a lot Naginder for your valueable comments.. Andy also mentioned same thing in his comments.. I didn’t get time to write the part two of the blog post where in I would have explained the same thing. Will certainly do it once I get time.
Thanks,
Ahmad,
really very nice research, i too wanted to understand the actual behaviour of SQL server when we create a clustered index.
your post gives me way to go ahead… nice one.
Tarni