Pages

Thursday 11 October 2012

SQL Server : Index Part 1 : Basics of index



While conducting interviews , I have noticed that, many people does not have clear picture about index.Many of them does not have clear picture about the difference between clustered and non clustered index.When people ask about index, it is tough to explain in one go. I feel it is worth to write details post about SQL server indexes in a simple understandable way even if we have tones of article available in the internet.

In short words, indexes helps the database engine to find the requested data efficiently using the minimal resource.Indexes also helps in data integrity through uniqueness of the column but it is not mandatory to define index on unique column. In a busy system it helps to improve the performance by increasing the concurrency. Multiple indexes on a same table can be used to cater the request issued by different users, but many indexes on a table will create a overhead also.Indexes are stored in different pages, it is like data stored in multiple places, and should be in sync with underlying table. Any insert,update or delete on a table should do same operation on all indexes defined on that table. Index help us improve the performance of the data retrieval but has an overhead on DML operation.In the case of Delete and Update ,index will helps the database engine to find the record that need to be modified. There is no thumb rule about the number of indexes on a table. If you need better performance for your read operation, go with more number of indexes and if you need better performance for the DML operation, keep minimal number of indexes.

SQL server support two types of indexes :

  • Clustered Index (CI) 
  • Non Clustered Index (NCI) 

Let us try to understand these two indexes using real life example. Assume that,your neighbour came to your house and asked for the telephone number of 'Robert Mike'. In this scenario, telephone directory will act as Clustered Index. You will open the directory by skipping almost 3/4th of pages assuming that his name appear in the last part of the directory.After turning couple of pages forward or backward , you reach the page where the name 'Robert Mike' is listed. Now you read out the number to your neighbour.So what happened here? When you reached the page where the name 'Robert Mike' is listed, you have all the information requested by your neighbour (client).

Let us assume that, your neighbour came to your house and asked the email id of 'Robert Mike' and you do not remember his email id. In this situation, telephone directory will act as Non Clustered Index. You will open the directory by skipping almost 3/4th of pages assuming that his name appear in the last part of the directory.After turning couple of pages forward or backward , you reach the page where the name 'Robert Mike' is listed. Now you dial 'Robert Mike' and ask him his email id.After disconnecting the line, you will hand over the email id to your neighbour.So what happened here ? When you reached page where the name ' 'Robert Mike' is listed, you do not have information requested by your neighbour (client). You have to do one more operation (dial the number ) to get the information requested by your neighbour (client). In SQL server, this additional operation is called as Bookmark or RID Lookup.

Hope this will give you pictorial idea about the cluster and non clustered index. In the coming post, we will discuss in details about the clustered index and non clustered index.

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


19 comments:

  1. Hey Nelson John...You rock all the time!!! I really like your all the post.

    ReplyDelete
    Replies
    1. Thank you reading post and passing the inspiring comment

      Delete
  2. Its a good and simple explanation of the indexes.
    -Prasad.

    ReplyDelete
  3. Pretty neat. Great examples. You say there are more posts on clustered and non clustered indexes but I dont see any.

    ReplyDelete
  4. I agree with Undecided. This was a great post, but a follow up with the promised part two on the detailed explanation of the differences in clustered and non-clustered indexes would be very helpful. Still, great job, Nelson.

    ReplyDelete
  5. Good explanation. Could you please point to part 2 of this topic.

    ReplyDelete
  6. Thank you for this valuable information. The information you shared is very interesting.
    web design chennai | web development in chennai

    ReplyDelete
  7. Your website content nice nice and interesting to observe.
    seo lüdenscheid

    ReplyDelete
  8. I want to thank the author for publishing this great read. I love your effort for putting in this blog. 24X7ServerSupport is a leading server management and server support providers in India. Visit on server support company

    ReplyDelete
  9. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment. meloncube.net provide free high performance minecraft servers hosting and other valuable services at the reasonable price. Visit us on free minecraft server hosting

    ReplyDelete
  10. happymod
    happymod apk
    happymod android
    I am looking for and I love to post a comment. meloncube.net provide free high performance minecraft servers.

    ReplyDelete
  11. Web facilitating is a program that enrichments to creation, and substances to upstanding a site or site page on the web.https://onohosting.com/

    ReplyDelete