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
Hey Nelson John...You rock all the time!!! I really like your all the post.ReplyDelete
Thank you reading post and passing the inspiring commentDelete
Its a good and simple explanation of the indexes.ReplyDelete
Pretty neat. Great examples. You say there are more posts on clustered and non clustered indexes but I dont see any.ReplyDelete
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
Good explanation. Could you please point to part 2 of this topic.ReplyDelete
Good Explanation !!ReplyDelete
Thank you for this valuable information. The information you shared is very interesting.ReplyDelete
web design chennai | web development in chennai
Your website content nice nice and interesting to observe.ReplyDelete
Pretty blog, so many ideas in a single site, thanks for the informative article, keep updating more article.ReplyDelete
Digital marketing course in chennai
نقل عفش من الرياض الى الامارات نقل عفش من الرياض الى الامارات
shareit for android
shareit for pc
shareit for ios
thanks for the informative article, keep updating more article.
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 companyReplyDelete
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 hostingReplyDelete
من افضل شركات منطقة مكة المكرمة التي تعمل في مجال نقل العفش مع الفك والتركيب تلك الشركة التي تسمى شركة نقل عفش بجدة وهي التي تختص بنقل العفش من منزل الى منزل آخر ومن مميزات شركات نقل العفش بجده ان تلك الشركات تمتلك سيارات نقل اثاث مؤمنة ومجهزة لأن شركه نقل عفش بجده تهتم بعفش واثاث منزلك اثناء النقل وقد تميزنا نحن بأننا شركه نقل عفش بجده رخيصه تناسب جميع المستويات وفي نفس الوقت تقدم خدمات جيدةReplyDelete
Thank you for sharing your info. tutuappReplyDelete
I am looking for and I love to post a comment. meloncube.net provide free high performance minecraft servers.
to know more about this click above.
joomla development company delhiReplyDelete
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