As per the promise I have made in “Some basics about Index in SQL Server” article, I am coming up with each Indexes of SQL Server and first and most important is “Clustered Index”. 

Understanding of Clustered Index:

As I told in previous article too that Index is the key of performance, good and managed Index could boost up your speed of retrieving of data from table.

Clustered Index contains actual data of the table in leaf level pages in logically sorted order (to understand root and leaf level page, click here). Since it is logically sorted, it doesn’t need to go for all data present there in Index. 

For example if you are looking for the phone number of “Ritesh Shah” in telephone directory, you can move to a page which has phone number of the person whose first name starts with “RI” and once all instance of “RI” over in directory, you don’t need to move on as there is NO CHANCE, you will get “Ritesh” anywhere in directory except those particular pages.

BTW, if you don’t have clustered index on your table, than your table would be called “HEAP”, which wouldn’t not have logically sorted data so if you are searching for “Ritesh Shah”, you can to check complete table as you never know, where you will find “Ritesh Shah”, just one method, go each and every row of table and check for matching criteria.

Like any other columns, you can define clustered index in more than one field too and all the columns covered up under the index, called key column.

While choosing a prime candidate for Clustered Index column in your table, you have to select the columns which meet few of the general criteria defined below. (you can say following criteria as a best practice while choosing index candidate)

--> You key column or combination of key columns should be unique and not null. If your You key column or combination of key columns are not unique than SQL Server has to add one more hidden column of 4-byte INT to make it unique. However, you can’t see that hidden column neither can query it directly; it would be purely for SQL Server’s internal use.

--> It should be short as wide key value would increase the depth of Clustered Index and will reduce the performance a bit and also increase the size of non-clustered index as it is being there as a reference in all non-clustered index.

--> Select less changing or no changing fields for you clustered index as Key value indicates the location of page where actual data resides, if you change this key value, row has to be deleted from that page and has to move to another appropriate page which reduces the performance and increase unnecessary overhead to IO.

Generally whenever you make Primary Key in any of your table, SQL Server itself create clustered index on it but if you want to keep clustered index on any other column(s) due to high selectivity on those column(s), you can do it.

Have you got bored of so long theory? Let us do some practical and check it out?

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 100000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--creating clustered index, however it is not meeting the criteria I have

--given in article, but this is ok for example

CREATE CLUSTERED INDEX idx_refno ON orders(refno)

GO

--run both of the following query with execution plan and see the results in execution plan

--you can see execution plan with the following steps

--first select both of the below given query

--Press Ctrl+M

--press F5

SELECT TOP 10 * from orders where refno=4

SELECT TOP 10 * from orders where OrderDate = '2010-02-01 00:04:00.000'

GO

--if you wish, you can uncomment below code and delete SQLHub database

--use master

--go

--drop database sqlhub

If you execute both the query with “Actual Execution Plan”, you will see first query is having Index Seek and second query is having Index Scan. 

Seek and Scan is really interesting topic which I will cover later but just keep in mind that, Seek is good, Scan is bad as it will check all records of the index.

if you want to refer all other articles related to index, 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

Ask me any SQL Server related question at my “ASK Profile”

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