Articles → SQL SERVER → Index scan and index seek in sql server

Index scan and index seek in sql server

In this article I will explain about index scan and index seek in SQL server. This topic contains following section

   1. Software requirement

   2. Technical knowledge

   3. Scan and seek

   4. Types of scan and seek in SQL server

   5. Table scan

   6. Clustered index scan and seek

   7. How isnull functions change the behavior?

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

In order to understand this tutorial you should know

   1. What are tables?

   2. What is an index?

   3. What is the difference between clustered and non-clustered indexes?

   4. What is a primary key?

   5. What is an execution plan?

Scan and seek

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

Click to Enlarge

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

Click to Enlarge

So we can say that seek is the quicker way to find records than scan if number of records are more.

Types of scan and seek in SQL server

SQL server supports following types of seeks and scans

   1. Table scan

   2. Clustered index scan

   3. Clustered index seek

So 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

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.

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.

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

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.

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

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

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

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250