Overview:
In this article I would like to give you an over view on what is Clustered Index and what are key points that you have to consider in designing the clustered index on a table.
Introduction:
Primarily in SQL server there are two types of indexes, clustered index and Non-Clustered index. Let me give you very basic difference between these types of indexes. These two indexes are different in terms how the data is stored. In Clustered index the leaf-nodes contains the data where as in Non-clustered index leaf-nodes contains the bookmarks to the actual data. In other words data is sorted and stored based on the Clustered Index. So that’s the reason you can have only one Clustered index per table.
Index Creation:
Now let’s get into action on how to create Clustered index.
The syntax to create index is:
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX index_name
ON table (column [ASC|DESC] [ ,...n ] )
[WITH {IGNORE_DUP_KEY|DROP_EXISTING|SORT_IN_TEMPDB}]
[ON filegroup ]
CLUSTERED or NONCLUSTERED: Optional clause. If it is not explicitly listed, then NONCLUSTERED index is created. So to create Clustered Index you need to specify “CLUSTERED” Clause.
Scenarios to have Clustered indexes:
1) If the query has a WHERE clause with operators like BETWEEN, >, >=, <, and <=.
2) If the query is returns large result sets.
3) If the query uses JOIN clauses.
4) The query accesses values in a column sequentially. For example, a query on EmployeeIDs between 10000 and 900000
5) If the query uses an ORDER BY clause or a GROUP BY clause. The index might remove the need to sort the data because the data is already ordered.
Criteria to create Clustered index:
1) Contains unique values or, failing that, contain mostly distinct values.
2) Are defined as IDENTITY. Each value in the column is known to be unique.
3) Are often used to sort data returned by a query. Having data in a clustered index that is already sorted in a desired order can save the cost of sorts during the query.
Criteria to not create Clustered index:
1) There is a wide key for each data row.
2) Values in the key columns undergo frequent changes. Because data in a clustered index is sorted on key column values, a change in those values means that data rows have to be moved into their correctly sorted positions after any changes.
|
| Author: Nirupa Rani Acharya 27 Jul 2008 | Member Level: Bronze Points : 0 |
thanks 4 d this...
|