15

I need a short explaining of clustered index.

  • What is a clustered index?
  • What are best practices for using the clustered index?
Improve this question
0
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

Improve this answer
2
  • Though large, GUIDs are okay as clustering keys if they are generated sequentially (e.g. using NEWSEQUENTIALID()). – Nick Chammas Aug 16 '11 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 '11 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.

Improve this answer
5
  • 3
    Non clustered indexes can be just as efficient for lookups, if the non-clustered index is covering for the lookup in question. – Russell Steen Jan 26 '11 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 '11 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. – Russell Steen Jan 26 '11 at 14:44
  • 3
    A covering NCI likely will be smaller which means less pages to read and faster. – Martin Smith Jun 12 '12 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. – Manoj Pandey Jun 24 '15 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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