When to Choose Table Scan and when to choose Seek Scan

When to Choose Table Scan and when to choose Seek-Scan

In this article, I am going to discuss when to choose Table Scan and when to choose Seek-Scan in SQL Server to get better performance. Please read our previous article where we discussed how to improve the table scan performance using unique keys in SQL Server.

When to choose Table Scan and when to choose Seek-Scan in SQL Server?

When your table having a small number of records or data then choose Table Scan and when your table having a huge amount of data or records then choose a seek-scan for better performance.

In our previous article, we discussed the table scan in detail, so, in this article, we are not going to discuss what table scan is? Here, in this article, first, we will discuss what is Seek-scan? And then we will see how a table scan improves the performance when there is a small amount of data and how a seek-scan used to improve the performance when there is a huge amount of data.

Table Scan in SQL Server:

A table scan is better when your table is having 30 or 40 records so that it will scan the data quickly and get the data quickly. But when your table is having a huge amount of data, then it will take more time as it scans the data row by row until it finds the record. So, in situations like this, Seek-Scan gives you better performance.

What is Seek-Scan in SQL Server?

SQL Server uses Seek-Scan only if your table having indexes. So, whenever you created an index (either clustered or non-clustered) on a table, it internally creates a B-Tree (Balanced Tree) structure and seek-scan uses the B-Tree structure to scan the records.

Understanding the B-Tree structure:

Whenever we created an index on a table, then it internally arranges the data into B-Tree structure i.e. Root Node, Non-Leaf Node, and Leaf Node. All the leaf nodes actually contain the data.

Please have a look at the below diagram to understand the Balanced Tree. As you can see, we have two Root Nodes which contain 30 and 50. The root has pointed to the non-leaf nodes. Here, we have four non-leaf nodes that contain 10, 30, 40, and 50. The non-leaf nodes point to the leaf nodes which actually contain the data.

When to Choose Table Scan and when to choose Seek-Scan in SQL Server

Suppose, you want to search 50 here, then what the search engine internally does is it will start the search from the root node. First, it will check whether the value 50 is less than or equal to 30 or not. And it found that 50 is not less than or equal to 30, so all the non-leaf nodes and their corresponding leaf nodes which come under the root node 30 is completely bypassed.

Then the search will go to the next node i.e. 50 and check whether 50 is less than or equal to 50. And it found that 50 equals to 50 and hence the condition satisfies. So, it goes to the non-leaf nodes (40, 50) which are under the root node 50. It will check whether 50 is less than or equal to 40 and the condition failed, so, it will bypass all the corresponding leaf nodes of the non-leaf node 40. Then the search engine will check the other non-leaf node i.e. 50 and it founds the condition as true as 50 equals 50. So, it goes and scans the leaf nodes which come under the non-leaf node 50. So, it approximately scans 10 records to get the data. On the other hand, a table scan needs to scan 49 records to get the same data.

Scenaio1: Table having a huge amount of data.

What we need to do is, we need to create a table let say Product table with a huge amount of data. In order to make sure, it uses a table scan, we are not going to create any keys or indexes. So, please use the below SQL Script to create and populate the Product table with a huge amount of data.

CREATE TABLE Product(
 [ID] INT NOT NULL,
 [Name] VARCHAR(50),
 [Code] INT NOT NULL
)
GO

DECLARE @NoOfRows INT, @ID INT;
DECLARE @Name VARCHAR(20)
SET @NoOfRows = 10000;
SET @ID  = 1;
WHILE @NoOfRows < 40000
BEGIN
   SET @Name = 'Product - ' + CAST(@ID AS VARCHAR(10))
   INSERT INTO Product VALUES(@ID, @Name, @NoOfRows)
   SET @ID = @ID + 1
   SET @NoOfRows = @NoOfRows + 1
END;

Now check the total number of rows by executing the following query. It will show 30000 records in the table.

SELECT COUNT(*) FROM Product;

Using table Scan:

Let us execute the following code and see the estimated execution plan.

SELECT * FROM Product WHERE Code = 28391;

Once you execute the above select statement, open the Display Estimated Execution Plan window either by clicking on the Display Estimated Execution Plan button or simply by pressing (Ctrl + L) keys which should display the execution query plan as shown below.

Table scan in SQL Server

As you can see in the above image, it uses a Table scan to search the data. Now let us see the number logical reads by executing the following query.

SET STATISTICS IO ON
SELECT * FROM Product WHERE Code = 28391;

Once you execute the above query, click on the Message window which appears right to the Results window as below. As you can see, with table scan the numbers of logical reads are 139.

Logical Read in Table scan with huge amount of adta

Using Seek-Scan:

In order to make sure, the search engine uses a seek-scan, let us first create a clustered index on the Code column by executing the following SQL Script.

CREATE CLUSTERED INDEX IX_Product_Code ON Product(Code ASC);

Once you create the clustered index by executing the above query, now let us issue the same select query and see the estimated execution plan.

SELECT * FROM Product WHERE Code = 28391;

Once you execute the above query, open the Display Estimated Execution Plan window either by clicking on the Display Estimated Execution Plan button or simply by pressing (Ctrl + L) keys that display the execution query plan as shown below.

Seek Scan in SQL server

As you can see in the above image, it uses an Index Seek i.e. seek-scan to search the data. Now let us see the number logical reads by executing the following query.

SET STATISTICS IO ON
SELECT * FROM Product WHERE Code = 28391;

Once you execute the above query, click on the Message window which appears right to the Results window as shown below. As you can see, with seek-scan the numbers of logical reads are only 2.

Logical Reads in seek scan with huge amount of data

So, this proves that, when your table having a huge number of records, then seek-scan gives you better performance compared to a table scan.

Scenaio2: Table having a small amount of data.

Here, in this case, what we need to do is, we will create a table let say StatusMaster with a small amount of data. In order to make sure, it uses a table scan, initially, we are not going to create any kind of keys or indexes in the table. So, please execute the below SQL Script to create and populate the StatusMaster table.

CREATE TABLE [StatusMaster](
 [ID] INT NOT NULL,
 [Name] VARCHAR(50)
)
GO

INSERT INTO StatusMaster VALUES(1, 'InProcess')
INSERT INTO StatusMaster VALUES(2, 'Pending')
INSERT INTO StatusMaster VALUES(3, 'Success')
INSERT INTO StatusMaster VALUES(4, 'Failed')

As you can see, here we created and populate the StatusMaster table with four records.

Using table Scan:

Let us execute the following SQL Query and see the estimated execution plan.

SELECT * FROM StatusMaster WHERE ID = 2;

Once you execute the above query, open the Display Estimated Execution Plan window either by clicking on the Display Estimated Execution Plan button or simply by pressing (Ctrl + L) keys which should display the estimated query execution plan as shown below.

Table scan in SQL Server with a small number of records

As you can see, it uses a Table scan to get the data. Now let us see the number logical reads by executing the following query.

SET STATISTICS IO ON
SELECT * FROM StatusMaster WHERE ID = 2;

Once you execute the above query, click on the Message window. Here, you can see, with table scan the number of logical reads is 1.

Logical Read in Table scan with small amount of data

Using Seek-Scan:

In order to make sure the search engine uses a seek-scan, let us first create a clustered index on the ID column of the StatusMaster table by executing the below Script.

CREATE CLUSTERED INDEX IX_StatusMaster_ID ON StatusMaster(ID ASC);

Once you created the clustered index, now let us see the estimated execution plan by executing the same SQL query.

SELECT * FROM StatusMaster WHERE ID = 2;

Seek Scan in SQL Server with small amount of data

As you can see in the above image, it uses an Index Seek i.e. seek-scan to search the data. Now let us see the number logical reads by executing the following query.

SET STATISTICS IO ON
SELECT * FROM StatusMaster WHERE ID = 2;

Once you execute the above query, click on the Message window. Here, you can see, with seek-scan the numbers of logical reads are 2.

Logical Reads in Seek Scan with small amount of data

So, this proves that, when your table having a small number of records, then a table scan gives you better performance compared to seek-scan.

That’s it for today. In the next article, I am going to discuss how to use a Covering Index to reduce RID lookup in SQL Server which will improve the SQL Query performance in detail Here, in this article, I try to explain when to choose Table Scan and when to choose Seek-Scan in SQL Server to get better performance with some examples. I hope you enjoy this article.