Can a Table Really Have a Clustered Index?

  • hakim.ali

    SSCarpal Tunnel

    Points: 4236

    Comments posted to this topic are about the item Can a Table Really Have a Clustered Index?

    Hakim Ali
    www.sqlzen.com

  • Robert Davis

    One Orange Chip

    Points: 28027

    Great definition and clarification of terms for beginners! You explained it very well.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • hakkie42

    SSC Enthusiast

    Points: 151

    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

    Default port

    Points: 1494

    Good explanation!

  • roger.plowman

    SSChampion

    Points: 10174

    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. 😀

  • patrickmcginnis59

    SSCertifiable

    Points: 6423

    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

    Old Hand

    Points: 378

    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 😉

  • roger.plowman

    SSChampion

    Points: 10174

    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 😉

    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

    SSC-Addicted

    Points: 411

    Finally, an explanation that does not require mental gymnastics and reading between the lines. Thanks for taking the time.

  • Phil Parkin

    SSC Guru

    Points: 243849

    Nice article, but rather than fields and records, I think you really mean columns and rows - there is a difference.

    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.

  • annjunk

    Old Hand

    Points: 335

    patrickmcginnis59 (12/24/2012)


    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.

    If a non-clustered index covers a query (meaning everything in your select and where are included somehow in the nc index) SQL will not do a table scan if it decides the index is the optimal way to look at the query.

    For example, if I have a nc index built on the area code field and I have included the first name, last name, and phone number columns in the index. I then run the following query, there is a very good chance that SQL will choose to use my nc index and never touch the table on a heap.

    SELECT Firstname, Lastname, phone FROM phonebook WHERE areacode = '412';

  • Mr. hoek

    Old Hand

    Points: 378

    But why does the table allocation 'double' when adding a clustered index?

    The only explanation I can think of, is that SQL server has the information 'twice' in the database:

    - ones as pure 'table' data (all records/fields)

    - ones as sorted 'index' data (all records/fields)

  • annjunk

    Old Hand

    Points: 335

    I wonder if this over simplification will end up confusing people more in the end. Both clustered and non-clustered indexes have what are called non-leaf-level pages that can help the server in searches.

    Like the person's library analogy earlier, when you walk into the library and know the ID on your book, you can start walking and reading the numbers on the ends of the shelves and only look for your book on the correct shelf. These labels on the ends of the book cases are like the non-leaf level pages of a clustered index.

    This is also why a clustered index is about 105% of the size of your table. There is something more added to the table, not simply the reorganizing of the data.

  • hakim.ali

    SSCarpal Tunnel

    Points: 4236

    Robert Davis (12/24/2012)


    Great definition and clarification of terms for beginners! You explained it very well.

    Thanks much.

    Hakim Ali
    www.sqlzen.com

  • hakim.ali

    SSCarpal Tunnel

    Points: 4236

    hakkie42 (12/24/2012)


    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...

    Yup... not very familiar with binary searches, but the rest sounds about right.

    Hakim Ali
    www.sqlzen.com

Viewing 15 posts - 1 through 15 (of 54 total)

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