Index Seek is Necessarily Always Great - Myth or Fact

Index Seek is Necessarily Always Great – Myth or Fact?

You would have heard from many in DBA community or in development community that if there is index seek in the execution plan, its always good. Lets see today whether its a myth or fact.

To clarify further, Index seeks are mostly good when it is with equality operator however when it comes to inequality operator, it can’t be great all the time.

I am using Stackoverflow database in the below example to showcase how Index seek is not great:

As you may see in above example when Inequality operator is involved with Index seek, its not great. So, before making this statement that Index Seek is always great, due consideration needs to be made.

One point is worth to note here, number of rows – As you might notice, number of rows returned is really huge and doesn’t really fit into OLTP environment. You need to ask this question whether this much data is really required and if so then why? Shouldn’t this type of result is more suitable to dataware house solution?

Please leave your comment if you liked this post or have any feedback.

Leave a Comment

Your email address will not be published. Required fields are marked *