In this article we look at the subtle differences between an Index Scan and Index Seek mechanism in SQL Server
Running a query in SQL Server is certainly something we would do on a regular basis. But what often troubles users about working with queries in SQL Server is the amount of time taken by them to give the required result. To do away with this delay, the users make use of the Query optimizer feature, but do you know how does the feature work? What is it about this feature that helps it return the results faster than running a query in regular way? The answer to this and other questions would be answered soon , keep reading.
When you run query optimizer, it reads the given index in two separate ways. These ways are called Index Scan and Index seek. These are two different mechanisms that are undertaken by the query optimizer to yield results for the entered query. Their working is explained below.
Index Seek
This is the mechanism that helps the query optimizer return the results faster than the other mechanism. In Index seek, the optimizer simply seeks the contents of the index, and returns all the fields that meet any given criteria in the query, thus giving quicker results. When using Index seek, you will get all fields in the index, instead of a few relevant ones. For an expansive indexes, and for not so important queries this should be used, as it would give you most part of the table as result, and would not cost much.
Index Scan
This is the lengthier and the more time consuming mechanism undertaken by the query optimizer if no relevant results are returned through Index Seek. In Index scan it keenly goes through each and every field in the index, and tries to find the fields matching the criteria given in the query. This gives you limited fields as result and makes it easier to make sens of the results from query optimizer. However this is the more expensive solution and should ideally be used for more important queries, where you need limited and accurate data.
The query optimizer, much like us humans, tries to take the easy way out, and only if things do not work out well, goes the high road. Initially the optimizer runs only the index seek mechanism, and if it fails to give sufficient results, it makes use of Index scan. Often the user is able to get access to satisfactory results by simply applying the Index seek mechanism as well, index scan comes into picture when the optimizer recognizes that index seek is not providing sufficient results.
If you are able to get query results quickly, there is a possibility that only index seek was used, and if your query takes long, it might be because the optimizer is making use of index scan. These features have been introduced not just to make the user experience better but also to highlight the potential and versatility of these applications, which have been provided a feature for all kinds of situations.
Protecting the data present in an SQL Server database in the event of a database crash is always a challenge. Invest in a SQL Server recovery tool to avoid any incidents of data loss.
Author Introduction:
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including mdb corruption and sql recovery software products. For more information visit https://www.datanumen.com/
Leave a Reply