Covering Index in SQL Server

There is a new type of index called “Covering Index” in SQL Server 2005. This feature gives us the option to put a Non-indexed column to the leaf node of the indexed column. If I am confusing lets look at the below example.

Let’s create a table
--Create demo table
CREATE TABLE Employee
(Emp_ID INT IDENTITY(1,1),
Name VARCHAR(30),
Date_Of_Join DATETIME,
Dept_id INT
)

Let’s insert some dummy data into this table
DECLARE @i INT
SET @i = 1000
WHILE @i > 0
BEGIN
INSERT INTO Employee(Name,Date_Of_Join,Dept_id) VALUES ('TIM' + CAST(@i as varchar),GETDATE(),@i%10)
SET @i = @i - 1
END

Let’s create Non Clustered Index on Emp_ID, Name column.
CREATE NONCLUSTERED INDEX IDX_EMPLOYEE ON EMPLOYEE(DEPT_ID)

Execute the below query and see the execution plan.

SELECT Emp_ID,NAME FROM Employee WHERE Dept_id = 9

If you look at the execution plan it is going for Full table scan because you don’t have index on Dept_ID column. So what we normally do is we add this column to the index definition. This certainly increases maintenance overhead and also if we try to add these various combinations then we will hit max no. of indexes on a table or a max index size etc.

Instead of this, we can make use of Covering Index feature, then with out adding Dept_id column to the Index definition, Optimizer would still pickup the index to return the data.

Let’s create a Covering Index:
CREATE NONCLUSTERED INDEX IDX_EMPLOYEE ON EMPLOYEE(EMP_ID,NAME) INCLUDE (DEPT_ID)

You can create Covering index only for Non Clustered Index types.

Execute the below query and see the execution plan.
SELECT Emp_ID,NAME FROM Employee WHERE Dept_id = 9