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

Index Scan And Index Seek In Sql Server

In this article, we will discuss index scan and index seek in the SQL server.

Software Requirement

Before you start reading this tutorial, you should make sure that SQL server 2005 (or above) is installed on your machine.

Technical Knowledge

To understand this tutorial, you should be aware of 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

Click to Enlarge

On the other hand, in seek SQL server search engine directly navigates to the record.

Picture showing the graphical representation of seek process

Click to Enlarge

So, seek is the quick way to find records than scan.

Types Of Scans And Seek In SQL Server

SQL server supports following types of seeks and scans: -

Table scan
Clustered index scan
Clustered index seek

Let us discuss each one of them.

Table Scan

When a table does not have any index then it means that rows in the table are stored without any order. In that case whenever select query fires the search engine scans the whole table. This is called table scan in SQL server.

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

Picture showing the sample table with records

Click to Enlarge

In this table we have not created any index. Now fire the following select query.

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

But before you run the query, please make sure that actual execution plan is turned on. If the actual execution plan is turned off then you can turn it on using ctrl + M.

Once the select query is executed you could see the following actual execution plan.

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

Click to Enlarge

As you can see the screen shot above that when there is no index on the table the search engine performs 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 then records in the table becomes ordered according to the clustered index key. So clustered index scan is quicker as compared to table scan.

So let us go back to previous example and create a clustered index on the table. Now run the same query on the table and see the actual execution plan

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

Click to Enlarge

As you can see in the figure above the search which search engine is doing is Clustered index scan.

As discussed in the earlier sections seek is always better than scan in terms of performance. So, if you know which all records you want to search then you can specify where clause in your select query and clustered index scan will become 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

Now when you run the query you can see that search engine uses seek to get records.

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

Click to Enlarge

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 [Address] column of the table and fire a select query.

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

Once the select query is fired you can see following execution plan

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

Click to Enlarge

Now let’s change the query and use isnull method in where clause.

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

Now check the execution plan and you will see that index seek is changed to index scan

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

Click to Enlarge

So, you should use ISNULL method when you are not sure whether the column will contain null value or not.

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