Tuesday, 16 April 2013

SQL Server : Index at a glance

In the last ten posts, we have discussed in details about the SQL server index and its storage structure. In this post I will consolidate all the ten post for easy reference.

What is index ?
In the first post, we have discussed about index in general. We tried to understand the clustered  index non clustered index and key lookup  with a sample from real life. Read More

What is heap table?
A table with out clustered index is called heap table. Data in heap table are not stored in any specific order.To understand more about the heap structure Read More

B tree structure of Clustered index

A table with clustered index is called Clustered table.Clustered index stores the actual data in the order of clustering key using a b tree structure.A table can have only one clustered index. To understand more about clustered index table storage and access pattern, read this article 

B tree structure of a non clustered index on clustered table
We can have many non clustered index on a table.To understand about the non clustered index storage structure and the way SQL server use the non clustered index, read here

B tree structure of a non clustered index on heap  table
How non clustered index works on a heap table,Read More

Design Consideration of clustered index key
What you should consider while deciding clustered index key. What will happen if you defined a clustered index on a non unique column ? Read the article Design Consideration of Clustered index.

Overhead on non clustered index on a non unique column 
You ever thought about difference in storage structure non clustered index while defining on non unique column. This post will give you more insight.

How to get rid of key lookup operation ? 
Book mark look up or Key Look up operation is very common in execution plan. How to get rid of that . Learn about covering index and included column in this post

Filtered index with its limitation 
How to define index on part of the table data ? How to define complex unique constraint on a table. How to improve performance of a query which work on small chunk of table data. Filtered index is the new way to solve the issue. Read more

Real time sample of the importance of the index key column order
The column order in the index is very important. It define the way data stored.The data access patter will play a major role in deciding the key column order. Shared some thoughts on this article 

If you liked this post, do like my page on FaceBook


  1. Hey very nice blog!!
    Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
    Appreciate the recommendation! Let me try it out.
    Keep working ,great job!


  2. I loved the way you discuss the topic great work thanks for the share, Let me share this, vmware training in pune