In this article I am trying to explain where to use clustered and where not. I am trying to mention some facts that MS told us to focus on.
Clustered Indexes
Point-1
Clustered indexes are ideal for queries that select by a range of values
or where you need sorted results. This is because the data is already presorted
in the index for you. Examples of this include when you are using BETWEEN,
<, >, GROUP BY, ORDER BY, and aggregates such as MAX, MIN, and COUNT in your
queries.
Point-2
Clustered indexes are good for queries that look up a record with a unique
value (such as an employee number) and when you need to retrieve most or
all of the data in the record. This is because the query is covered by the
index.
Point-3
Clustered indexes are good for queries that use the JOIN or GROUP BY clauses.
Point-4
Clustered indexes are good for queries where you want to return a lot of
columns. This is because the data is in the index and does
not have to be looked up elsewhere.
NON Clustered indexes
Avoid putting a clustered index on columns that increment, such as an identity,
date, or similarly incrementing columns, if your table is subject to a high
level of INSERTS. Since clustered indexes force the data to be physically
ordered, a clustered index on an incrementing column forces new data to be
inserted at the same page in the table, creating a table hot spot, which
can create disk I/O bottlenecks. Ideally, find another column or columns
to become your clustered index.
Hope that the information is informative and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS
I'm sorry, this statement "Avoid putting a clustered index on columns that increment" is completely wrong. Here's a quote from Kimberly Tripp, I suggest you read some of her articles:
ReplyDeleteIf the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity).
Read more: http://sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx#ixzz1lzq0WwQS
Thanks "Anonymous"
ReplyDelete