Posted in Index on November 18, 2009 |
Leave a Comment »
What is an index?
A clustered index determines the storage order of data in a table. A table can contain only one clustered
index.
Clustered Index:
Before creating clustered indexes, understand how your data will be accessed.
Consider using a clustered index for:
- Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes. However, if there are very few distinct values, such as only 1 and 0, no index should be created.
- Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
- Columns that are accessed sequentially.
- Queries that return large result sets.
- Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns.
- Column(s) specified in the ORDER BY or GROUP BY clause. This eliminates the need for SQL Server to sort the data because the rows are already sorted.
- OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
Clustered indexes are not a good choice for:
- Columns that undergo frequent changes. When columns change that are part of a clustered index, the entire row moves because SQL Server must keep the rows data values in physical order. This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
- Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.
Read Full Post »
