I need a short explaining of clustered index.
- What is a clustered index?
- What are best practices for using the clustered index?
-
It is a good idea to use a dates in a clustered index since new data is always appended to the end of the table. – Ralph Winters Mar 6 '11 at 18:50
-
1Possibly, but not always. – mrdenny Mar 7 '11 at 8:58
-
@mrdenny - could you elaborate on when this would not be so? – Ralph Winters Mar 8 '11 at 19:33
-
1If rows are being entered out of order. If the table is more typically going to be queried by the primary key. If the primary key is an INT (or smaller) and there will be lots of non-clustered indexes on the table than a datetime clustered key would be larger and less efficient. If the table will never be queried by the date column than using is as the clustering key wouldn't be worthwhile as only the non-clustered indexes would be used. – mrdenny Mar 8 '11 at 20:27
-
@mrdenny: also if the table has no date column (a subset case of your last one :) – ypercubeᵀᴹ Jun 12 '12 at 7:07
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)
- "Effective Clustered Indexes" (Simple Talk)
- Wikipedia
- MSDN (SQL Server 2000)
- MSDN (SQL Server 2005+)
Edit, to fully explain my best practice suggestions, follow the link in Eric Humphrey's comment: Queen of Indexing: Kimberly Tripp
-
3You'll also want Kimberly Tripp's blog: sqlskills.com/BLOGS/KIMBERLY/category/Clustered-Index.aspx – Eric Humphrey - lotsahelp Jan 25 '11 at 15:58
-
1And there in lie the problem with applying "best practice" without context. For instance, if the primary search criteria for a large frequently searched table is a date range, your best location for the clustered index is the date being searched NOT the primary key. To say the table and the clustered index are one and the same is a bit too oversimplified. The data is actually stored in the order dictated by the clustered index. Among other things, this means that clustered indexes are particularly useful for range searches returning the full data set. – Russell Steen Jan 26 '11 at 2:39
-
2@Russell Steen: 1. I included links 2. OP had to ask, consider what level I pitched this answer at 3. Eric Humphrey supplied a create link. We can go through case by case what us more experienced guys know and understand, but are we here to help educate or overwhelm...? – gbn Jan 26 '11 at 4:49
-
I hear what you're saying. The problem is not with your answer per-se, but in the fact that less experienced people than yourself run around quoting and applying something because it's "best practice" without understanding the use and context the best practice was written for -- I'd argue that yes, we're here to educate and that quoting best practice without providing more context directly in the post doesn't do as good of a job of that. – Russell Steen Jan 26 '11 at 14:47
-
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.
-
3Non 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
-
4I 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
-
2A 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
