Clustered Index in SQL Server

When I was novice with SQL Server and mainly working with .NET applications, Index seemed to me as alien of the planet of MARS. I was always confused about Index concept and always making mistake in choosing clustered and non-clustered index for particular field. When SQL Server and database technologies attract me, I had started to understanding Index concept in details few year back and this article is coming from my own experience and learning.

Though Clustered Index is not a concept of Relational Database, it is very useful for performance improvement in highly volume transaction processing system.

Basically Clustered Index is a kind of data structure that provide fast data access. There are two types of index available in SQL Server 1.)  Clustered Index 2.) Non-Clustered Index. Today we are going to talk about 1^st Option which is Clustered Index.

You can have maximum one clustered index per page because clustered Index stores sorted physical data in leaf level and this is obvious that you can sort data in one direction only so that you can have maximum one Clustered Index in database table. However, you can define clustered index on one or more than one field but try to keep as less columns as possible in clustered index.

When you create Primary Key, unique index is automatically getting created on column(s) which is Clustered Index by default; however, you can make it non-clustered while generating Primary Key.

While choosing the field for Clustered Index, you should consider few things in your mind. Column shouldn’t be low-cardinality. Low-cardinality means, it is the column which contain very less distinct value. For example, if you consider Grade filed (I guess there should be four grade A, B, C, D), you maximum having 4 kind of value in column.

Column should be highly selective in your WHERE clause, GROUP BY clause, JOIN and ORDER BY clause so that you can get maximum benefit out of it.

If you would like to read some more articles about Index in this blog, go through below given link

can't create Index on view

Calculate the size of clustered and non-clustered index

DBCC SHOWCONTIG and DBCC INDEXDEFRAG

sys.dm_db_index_usage_stats and sys.dm_db_index_operational_sats

For getting the details about structure of Clustered Index, please Click Here

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com