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 1st 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.
For getting the details about structure of Clustered Index, please Click Here