Sqlserver Indexes

In this post I will be explaining indexes ,So the big question is  What are Indexes?

Well Indexes are used mainly for performance so that the optimizer can choose the best method for fetching records for a query ,Before going in to the details of indexes,  we will look at, how exactly Sql sever retrieves the information when we issue a query against its tables,Basically what  Sqlserver does, when haven't got indexes on relevant table is, it  will make a table scan to find the relevant information  ,So what is a table scan?In a table scan  what Sqlserver does is that ,it goes to each row of the table   to find the relevant match.

How Does it Effects the Performance,Assume if you got Millions of  records and If  you  scan all the rows than  it consumes lot of time  as you have to go to each and every  row , this consumes lot of time that is why the concept of indexes was introduced.remember a lot factors contribute to the optimization of query but  i want to  limit my discussion only to indexes ,which play an  important role in improving performance,I will discuss rest of the associated factors with indexes and query performance like statistics and fragmentation in future posts

What Indexes does is it  stores the data in an orderly way ,So if you issue a query against a table which contain indexes ,the execution engine will see the existence of the index   and it  knows where exactly those values are going to reside  and picks up the relevant records quickly  by taking a particular logical path ,this operation  is called  a seek a operation,to do this seek operation it takes B tree approach Namely Balanced Tree approach

 

Picture Taken from Msdn

This Balanced Tree Approach Makes the whole process of fetching the records  easy  as it takes relevant path as shown in the above figure as per its data to find the records   and this avoids row by row operation and improving performance

Before Going any further I will explain the two Main Type of Indexes  Namely

Clustered and NonClustered

So what is a Clustered Index.A Clustered Index is an Index  which will order all the records of a table in an orderly way.So this naturally will lead to a question ,How many Clustered Indexes a table can have?well the answer is simple, physically we can order a table in   in only  one way ,So the answer is simple    there will be only  one Clustered Index per table .Remember Clustered Index orders the entire table in a particular order and one more important thing to understand before going to Non Clustered Index is that the Leaflevel Nodes of a Clustered indexes are actual Data.

Now we will  dig deeper into NonClustered Index.

In non clustered index the leaf level nodes of the Btree is not  actual data as in clustered, but it contains a pointer, so you may think how this pointer will actually help to find me the actual record  ,well this pointer points to  either a RowID of the table or a Clustered index Key if there is a Clustered index on the table to fetch the actual data.As you might be wondering why you use a non clustered index when there is this extra process of pointer to fetch the records which will effect performance,  well we know we can only create one  clustered index per table but  we can create as many as non clustered indexes i will explain this further in detail later ,but for now remember even though  the performance of non clustered index is low to clustered index but it gives a flexibility to create more than one non clustered index and for some queries how the performance an be improved without the need of a clustered  index , and non-clustered index is far better than  than row by row operation for highly selective  columns of large tables

Before you read any further I strongly recommend to understand the above process,this might be basic but is lays  a solid foundation how the Optimizer will use the indexes and  fetches  the records for performance,  rest of the post is totally dependent on this.

I will explain in detail more about indexes in next  post .