Does creating an clustered index physically sort the table

Question

Does creating an clustered index physically sort the table

newbie2sql

SSC Eights!

Points: 987
More actions
August 27, 2009 at 12:28 am

#137685

Hi,
Does creating a clustered index on a table physically sorts the table or it sorts only the index pages.
If it physically sorts the table then heavy IO should be generated and may slowdown the server and how sql server handles this.
Thanks
newbie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The clustered index is the table. A clustered index has, at the leaf level, the actual data pages of the table and SQL will do it's best, at time of index creation, to have those leaf pages stored in the file in order of the clustering key.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Andy Hogg SSCarpal Tunnel Points: 4585 More actions · Answer 2

I get my head around clustered vs. non-clustered indexes by thinking of a clustered index as being like a phone book.

In a phone book data is sorted in the order of a particular field (last name) and because of this when you flick through a phone book and you find the name you want, you actually have the phone number \ address right there, no further looking up is required, this is because the index actually is the ordered data itself.

For a non-clustred index I think of a conventional index in the back of a reference book.

If you look up "Stored Procedure" in the index at the back of one of the tomes on SQL Server, then you would see all the page numbers which contain a mention of this subject, 9, 27, 128-165 etc. But this isn't the data itself, merely a pointer to it.

You have to then go to these page numbers to access the leaf-level data and read about Stored Procedures.

Dave Ballantyne SSC-Dedicated Points: 33566 More actions · Answer 3

Andy Hogg (8/27/2009)
I get my head around clustered vs. non-clustered indexes by thinking of a clustered index as being like a phone book.
In a phone book data is sorted in the order of a particular field (last name) and because of this when you flick through a phone book and you find the name you want, you actually have the phone number \ address right there, no further looking up is required, this is because the index actually is the ordered data itself.
For a non-clustred index I think of a conventional index in the back of a reference book.
If you look up "Stored Procedure" in the index at the back of one of the tomes on SQL Server, then you would see all the page numbers which contain a mention of this subject, 9, 27, 128-165 etc. But this isn't the data itself, merely a pointer to it.
You have to then go to these page numbers to access the leaf-level data and read about Stored Procedures.

Good analogy , i like it 🙂

Clear Sky SQL
My Blog[/url]