Can a Table Really Have a Clustered Index?

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

    Hakim Ali
    www.sqlzen.com

  • 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]

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

  • Good explanation!

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

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

  • 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 😉

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

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

  • 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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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';

  • 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)

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

  • 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

  • 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