15

I need a short explaining of clustered index.

  • What is a clustered index?
  • What are best practices for using the clustered index?
0

2 Answers 2

15

In simple terms...

A telephone directory: the data is the index/the index is the data. To look you up, I'd start with Rezaei, Amir for example. No external lookup is needed.

In database terms:

The table data and clustered index are one and the same (in SQL Server, also InnoDB, Oracle IOT)

Best practice is narrow, numeric, strictly increasing (think IDENTITY or Autonumber column). Data types like GUIDs are not a good idea

Links to read (to avoid bloating this answer)

Edit, to fully explain my best practice suggestions, follow the link in Eric Humphrey's comment: Queen of Indexing: Kimberly Tripp

2
  • Though large, GUIDs are okay as clustering keys if they are generated sequentially (e.g. using NEWSEQUENTIALID()). Aug 16, 2011 at 14:55
  • @Nick: they are not sequential after a server restart. And too wide = performance hit. And sorting GUIDs is expensive
    – gbn
    Aug 16, 2011 at 14:57
2

Its an index on the data itself such that the records are physically sorted by the field you have the clustered index on. This is why you can have only one Clustered Index per table.

Non Clustered Index is not as fast for look ups but you can have many of these types of indexes on a table.

5
  • 3
    Non clustered indexes can be just as efficient for lookups, if the non-clustered index is covering for the lookup in question. Jan 26, 2011 at 2:42
  • @Russell There is no way it can be faster as the index has forced the actual sort order of the data. There is no simpler/ faster way for SQL or any RDBMS to find the information it needs.
    – kacalapy
    Jan 26, 2011 at 14:11
  • 4
    I didn't say faster. A covering index has all the data requested in the select in the index - IN ORDER, on a separate location on the disk. So a select against a matching covering index never hits the table. Jan 26, 2011 at 14:44
  • 3
    A covering NCI likely will be smaller which means less pages to read and faster. Jun 12, 2012 at 6:55
  • Reason to have only one Clustered index on a table is not physical sorting, Index pages are always logically sorted, and contiguous allocation of pages is not guaranteed. You can have only one Clustered Index per table because the Clustered Index is the table itself, and Non-Clustered indexes are the copies of the table with required columns. Jun 24, 2015 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.