Performance Improvement using Unique Keys

Back to: SQL Server Tutorial

Performance Improvement using Unique Keys in SQL Server

In this article, I am going to discuss the Performance Improvement using Unique Keys in SQL Server. Please read our previous article where we discussed the basic important concepts of Performance Improvement in SQL Server. Here, in this article, we will see how the Unique Keys improves the table scan performance in the SQL server database. So, as part of this article, we are going to discuss the following pointers.

1. What is table Scan in SQL Server?
2. How much logical reads a table scan do without unique keys?
3. How much logical reads a table scan do with unique keys?

What is Table Scan in SQL Server?

In order to understand the table scan, we are going to use the following Employee table.

The above Employee table at the moment does not have any keys, constraints or indexes. So, please execute the below SQL Script to create and populate the Employee table with data.

CREATE TABLE [Employee](
 [EmployeeID] INT NOT NULL,
 [Name] VARCHAR(50),
 [Code] INT NOT NULL
)
GO

INSERT INTO Employee VALUES(1, 'James', 10001)
INSERT INTO Employee VALUES(2, 'David', 10002)
INSERT INTO Employee VALUES(3, 'Pam', 10003)
INSERT INTO Employee VALUES(4, 'Sara', 10004)
INSERT INTO Employee VALUES(5, 'Smith', 10005)
INSERT INTO Employee VALUES(6, 'Anurag', 10006)
INSERT INTO Employee VALUES(7, 'Preety', 10007)
INSERT INTO Employee VALUES(8, 'Priyanka', 10008)

Once you have created and populate the Employee table then fire the following query. Here, we are fetching the employee info whose code is 10006. The code column is going to contain unique values but at the moment we don’t have any primary or unique key on this column.

SELECT * FROM Employee WHERE Code = 10006;

Once you execute the above query, and then open the Display Estimated Execution Plan window by clicking on the Display Estimated Execution Plan button or simply by pressing (Ctrl + L) keys which should display the following window.

Now let us understand what basically a table scan is in SQL Server?

When your table does not have any kind of keys or indexes then it uses something called table scan to fetch the data. In order to understand the table scan in SQL Server, please have a look at the following image. Table Scan means, it will scan the value record by record from the beginning until it finds the value and then returns that value to the user.

In order to see a difference, your table should contain a huge amount of data. So, let us update our table with a huge amount of data.

Step1: Truncate the table

TRUNCATE TABLE Employee;

Setp2: Insert a huge amount of data into the table using a loop.

DECLARE @NoOfRows INT, @ID INT;
DECLARE @Name VARCHAR(20)
SET @NoOfRows = 10000;
SET @ID  = 1;

WHILE @NoOfRows <= 30000
BEGIN
   SET @Name = 'Name - ' + CAST(@ID AS VARCHAR(10))
   INSERT INTO Employee VALUES(@ID, @Name, @NoOfRows)
   SET @ID = @ID + 1
   SET @NoOfRows = @NoOfRows + 1
END;

Now let us log the number of Physical and Logical Read for the above query without using the unique keys. So, please execute the following query along with the statistics.

SET STATISTICS IO ON
SELECT * FROM Employee WHERE Code = 26640;

Once you execute the above query, now open the message window which appears right to the Results window as shown below. As you can see there are 89 logical reads.

Now, let us create a unique key on Code column and then try to execute the same code,

Creating Unique key: You can also create the column as Primary Key:

ALTER TABLE Employee ADD CONSTRAINT unique_code UNIQUE (Code);

Once you created the unique key, let us try to execute the same SQL statement as shown below.

SET STATISTICS IO ON
SELECT * FROM Employee WHERE Code = 26640;

Once you execute the above query, now open the message window and see the Number of Logical Reads as shown below. As you can see, now there are only 3 logical reads as compared to 85 logical reads.

So, when you created a unique key, the number of input and output is reduced which ultimately improves the performance of the query.

Points to Remember:

1. Unique keys improve table scan performance and we saw how the logical reads are decreased when we created a unique key.
2. Any operator i.e. Logical operator or Physical Operator, when it associated with the unique data i.e. when it associated with a unique key, always the performance increases. So, it is not just about the table scan operator, it’s about any operator of SQL Server; the performance will increase if there is a unique key.
3. So, every table should have unique keys (Primary Key or Unique Key). In case if you don’t have a property that has a unique key, you probably create a unique key with the int data type and create it as an identity column that means that column is an auto-increment. So, every table in your project should have a primary key with int data type for better performance.

That’s it for today. In the next article, I am going to discuss when to choose Table Scan and when to choose Seek-Scan in SQL Server to get better performance. Here, in this article, I try to explain, how to improve the table scan performance using the unique key in SQL Server step by step with some examples. I hope you enjoy this article.