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...
|
| Author: sangeetha 14 May 2009 | Member Level: Gold Points : 2 |
Clustered Index:
>The data is physically stored
>Only one clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and whose values do not change often.
How Clustered index works:
In a clustered index, the data is stored on the leaf level of a B-Tree. The structure of a B-Tree can be viewed as a filing cabinet. The data pages of the table are like the folders stored in alphabetical order in the filing cabinet and the rows of data are like the documents stored in the folders.
Sql server performs the following streps when it uses a clustered index to search for a value:
1.It obtains the address of the root page from sysindexes table.
2.The search values are compared with the key values of the root page.
3.The highest key value is found on the page where the key value is less than or equal to the search value.
4.The page pointer is followed to the next lowest level in the index.
5.Steps 3 and 4 are repeatabed until the data pages is reached.
6.The rows of data are searched on the data page until the search value is found. If search value is not found on the data page, no rows are returned by the query.
|