SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can a Table Really Have a Clustered Index?


Can a Table Really Have a Clustered Index?

Author
Message
hakim.ali
hakim.ali
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1066 Visits: 1064
Comments posted to this topic are about the item Can a Table Really Have a Clustered Index?

Hakim Ali
www.sqlzen.com
Robert Davis
Robert Davis
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4544 Visits: 1631
Great definition and clarification of terms for beginners! You explained it very well.



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master, SQL Server MVP
Database Engineer at BlueMountain Capital Management
hakkie42
hakkie42
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 104
Ok, so in other words you're saying a clustered index means that the physical data is stored in index order, so e.g. binary searches are possible.

A nonclustered index is closer to what you'd expect of the meaning of "index": a separate list with a pointer to the physical record.

That does clear up a lot...
Neha05
Neha05
SSChasing Mays
SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)SSChasing Mays (600 reputation)

Group: General Forum Members
Points: 600 Visits: 60
Good explanation!
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 1339
I always liked the library analogy.

The clustered index is how the books are shelved (usually by Dewey Decimal number), the non-clustered indexes are the index cards in the card catalogs.

Of course this probably tells you I'm old... (laughing)

Extending this, covering indexes are having the information you're looking for in the card catalog so you don't have to go looking on the shelf. :-D
patrickmcginnis59
patrickmcginnis59
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1127 Visits: 2333

Let's assume there is no clustered index on this table. This makes it a heap, meaning the rows are not physically written to disk in any particular order. When you search for a record in such a table via a select statement, the database must look at each record in the table. This is called a table scan. Not a very good way of doing things, because it is bad for performance.

If you have a nonclustered index on the table and the select statement had criteria as such that it could use the nonclustered index, does SQL still have to do a table scan? Seems to me it would do a lookup on the index then bookmark lookup for the matching rows in the heap. I do realise that bookmark lookups are more expensive than using a clustered index.
Mr. hoek
Mr. hoek
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 52
It would be nice that MSSQL tables would 'be' clustered or non clustered, but in fact they 'are' heaps with an optional 'clustered index' added to then.
Removing the clustered index, does not remove the data Wink
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 1339
Mr. hoek (12/24/2012)
It would be nice that MSSQL tables would 'be' clustered or non clustered, but in fact they 'are' heaps with an optional 'clustered index' added to then.
Removing the clustered index, does not remove the data Wink


Actually, non-heaps *are* clustered indexes. Creating a clustered index physically rearranges the table into order by the index, and keeps it in that physical order as records are added and deleted. Removing a clustered index merely stops SQL server from keeping the table in order, but doesn't bother to rearrange the table randomly again. :-)
dfortier
dfortier
Old Hand
Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)Old Hand (332 reputation)

Group: General Forum Members
Points: 332 Visits: 283
Finally, an explanation that does not require mental gymnastics and reading between the lines. Thanks for taking the time.
Phil Parkin
Phil Parkin
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34972 Visits: 20859
Nice article, but rather than fields and records, I think you really mean columns and rows - there is a difference.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search