Some basics about Index in SQL Server

I wrote an article about index “Be selective while creating Index” on my blog few days back based on the question I have received in my “ASK Profile” in BeyondRelational.Com.

After writing above article I felt to write few more stuff regarding Index as I still see so many queries, concerns regarding index in developer’s mind, especially who are .NET developer and have very less touch with SQL Server. I know this is not the task of .NET developer but there are so many companies in which .NET developer used to undergo the task of creating database table, SPs etc.

Since most of the people knew that Index is the key of good performance over SELECT statements, very few of them really know how to utilize this sword which has blade on both the side, if you don’t use it wisely, it will harm your performance too.

BTW, all discussion about index in this article is generic for SQL Server; it is not related to specific SQL Server version.

Before jumping more into the Index concept, let me tell you that all data in SQL Server being stored in page, one page consume 8KB. So whenever you create any table and insert data, it goes to one page, suppose you have 4KB of data in one row than two row will comes in one SQL Server page.

Understanding of Index:

Index is nothing but just a kind of data structure which helps optimizer to find data row easily and fast. You can compare it with the “Index” you get in almost all books. If you know the topic you wanted to find, look for it in Book’s Index and you will get page number where that topic is explained.

Generally you can define index on one or more than one column, if you define your index on more than one column, it is called “Composite Index”. All the columns you have created an Index on are called Key Columns.

Have you ever studied “B-Tree” in any of the OOPs languages like C++, C# etc.? Index is kind of “B-Tree” and store data in “B-Tree” structure. It suppose to have one Root Page, it may or may not have Intermediate page (based on the size of data) and one or more than one leaf pages. In sort, Root page would be the top of index structure and leaf page would be lower part of the index structure.

You will have entry of each row of your data table in leaf level pages sorted in logical order.  Mainly there are two type of indexes you can create on SQL Server table (I am not considering all new type of index comes up with each new version of SQL Server, I may cover those up in future article).

1.)    Clustered Index

2.)    Non-Clustered Index

You can have only one clustered Index on each table as Clustered Index stores data in logical order leaf pages of your clustered index has actual sorted data within and this is the reason why you can’t have more than one clustered index on your table.

As long as non clustered index concern, you can have up to 249 Non-Clustered Index per table up to SQL Server 2005 and in later version, you can have limit of 999. Non-Clustered Index don’t store data sorted physically in its leaf level pages but it stores the pointer of the each row of the included column. Pointer may points to clustered index key and if you don’t have clustered index in your table than pointer points to “Row Identifier”.

 “Row-Identifier” is nothing but the unique combination of File ID and Page Number and Slot Index crated by SQL Server to identify each row uniquely in absence of Clustered Index.

So this is it, for the basics of Index, I will come up with some more detailed article regarding each type of index very soon.

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”