Articles → SQL SERVER → Index Scan And Index Seek In Sql Server

Index Scan And Index Seek In Sql Server

This article describes index scan and index seek in the SQL server.

Software Requirement

Before reading this article, ensure that SQL Server Management Studio is installed on your machine.

Technical Knowledge

Before proceeding, you should know the following concepts: -

What are tables?
What is an index?
What is the difference between clustered and non-clustered indexes?
What is a primary key?
What is an execution plan?

Scan And Seek

In English, scan means – Looking at all parts of something to get relevant information. In the SQL server as well, when you execute the SELECT query, the SQL server search engine scans the whole table to find the record.

Picture showing the graphical representation of scan process

Conversely, in a seek operation, the SQL Server search engine directly navigates to the record.

Picture showing the graphical representation of seek process

Thus, seek is a faster way to find records compared to scan.

Types Of Scans And Seek In SQL Server

SQL server supports the following types of seeks and scans: -

Table scan
Clustered index scan
Clustered index seeks

Let us discuss each one of them.

Table Scan

When a table lacks an index, its rows are stored without any specific order. As a result, when a SELECT query is executed, the search engine scans the entire table to locate the required data. This process is known as a table scan in SQL Server.

Let us work on an example. Consider the table below: -

Picture showing the sample table with records

This table does not have any indexes. Now, execute the following SELECT query.

SELECT 
  [PersonId], 
  [PersonName], 
  [Address] 
FROM 
  [Test].[dbo].[Person]

Before query execution, ensure that the execution plan is turned on (Use CTRL + M key to turn execution plan on). Once the select query is executed, you can see the following execution plan.

Picture showing the execution plan of the select query on the table

As you can see, the screenshot above indicates that when there is no index on the table, the search engine performs a table scan.

Adding Primary Key And Clustered Index On The Table

Before we move ahead, please perform the following actions: -

Set primary key for PersonId.
Add a non-clustered index on the Address column.

Clustered Index Scan And Seek

When you create a clustered index on a table, the records are ordered according to the clustered index key. Therefore, a clustered index scan is quicker than a table scan.

Let us return to the previous example and create a clustered index on the table. Then run the same query on the table and observe the actual execution plan.

Picture showing the execution plan of the select query with clustered index scan

As you can see in the figure above, the search that the search engine is performing is a clustered index scan.

As discussed in the earlier sections seek is always better than scan in terms of performance. So, if you know which records you want to search, then you can specify a where clause in your select query and a clustered index scan will become a seek. So, if we change the query and specify a where clause in select as shown below.

SELECT 
  [PersonId], 
  [PersonName], 
  [Address] 
FROM 
  [Test].[dbo].[Person] 
where 
  Personid = 1

When you execute the query, you can see that the search engine uses seek to get records.

Picture showing the execution plan of the select query with clustered index seek

How ISNULL Functions Change The Behavior?

Here we are discussing a very special case. For that, let us first add the non-clustered index on the [Address] column of the table and fire a select query.

select 
  [Address] 
from 
  Person 
where 
  [Address] = 'Address 1'

Once the SELECT query is executed, you can see the following execution plan.

Picture showing the execution plan of the select query with clustered index scan with isnull function

Change the query above and use the ISNULL method in the WHERE clause.

select 
  [Address] 
from 
  Person 
where 
  Isnull([Address], '') = 'Address 1'

Check the execution plan and you will notice that index seek is changed to index scan.

Picture showing the execution plan of the select query with clustered index seek with isnull function

Use the ISNULL method when uncertain if the column contains a ISNULL value.

Posted By -	Karan Gupta

Posted On -	Wednesday, October 1, 2014

Updated On -	Thursday, September 29, 2022

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**