Looking into covering indexes in SQL Server
I have just finished a seminar in SQL Server 2012 where the topic of "indexes" was again a very highly discussed topic.
At one point I had to present a demo on covering indexes.
In this post I will try to shed some light on covering indexes and their use.
The right index on the right column is the basis on which query tuning begins.
On the other hand a missing index or an index placed on the wrong column,or columns can be the basis for all performance problems starting with basic data access, continuing with joins and ending in filtering clauses.
Placing the correct index(es) on table(s) is an art in itself.
A covering index is simply an index that contains the value being queried.
That means that the non-clustered index (covering index) will not use the clustered index to find the data asked from the query. The data is already in the clustered index.We avoid the lookup step.
I have installed a SQL Server 2012 Enterprise edition in my machine.
You can use SQL Server 2005/2008/2012 Express edition as well which is a free edition.
In a new query window type the following
CREATE DATABASE myindexdb
USE myindexdb
go
CREATE table cars
(
id int identity primary key,
length DECIMAL(2,1),
width DECIMAL(2,1),
colour varchar(10)
)
insert into cars values
(3.3, 1.8, 'black'),
(3, 2, 'green'),
(2.9, 1.2, 'blue'),
(2.8, 1.4, 'yellow'),
(3, 1.2, 'white'),
(2.9, 1.5, 'black'),
(2.6, 1.2, 'brown'),
(3.2, 1.2, 'white'),
(2.2, 1.5, 'red')
--the optimiser will do an clustered index scan
select id,length,width from cars
WHERE length =2.9 AND width=1.5
CREATE INDEX lengthwidth ON cars (length,width)
--this time the optimiser will do an non-clustered index seek
select id,length,width from cars
WHERE length =2.9 AND width=1.5
Let me explain what I am doing in this snippet of code above.
First, I crate a dummy database. Then I create a table with 3 columns including a primary key. Because I have a primary key I have a clustered index.
Then I insert some values in it. In line 26 I have a simple select query. The optimiser (enable the actual execution plan) will use a clustered index scan to find the values because it did not have an appropriate index.
Then in line 28 I create a non-clustered index to include the columns (width,length) and then in line 31 I re-run the same query as before.
This time the optimiser will select a different execution plan and will use an non-clustered index seek instead that in general it is a much quicker way to get our data back.
This query execution gives us back the following results
6 2.9 1.5
In the same query windows type the following. With this statement we will get the contents of the non-clustered index.
--get the contents of the non-clustered index
select cast(length as varchar(4)) + ',' + cast(width as varchar(4)) +
',' + cast(id as varchar(4)) from cars order by length, width
[/sourcecode]
When we execute the statement above we get
2.2, 1.5, 9
2.6, 1.2, 7
2.8, 1.4, 4
2.9, 1.2, 3
2.9 ,1.5, 6
3.0, 1.2, 5
3.0, 2.0, 2
3.2, 1.2, 8
3.3, 1.8, 1
As you can see the index contains all the data that we need to satisfy that query so there is no need for a table look-up or any other operation.
The last value in the rows above is the primary key value.
Now, in the same query window let's type another t-sql query.
select id,length,width,colour from cars WHERE length =2.9 AND width=1.5
In this case the non-clustered index cannot be used so the optimiser chooses again a clustered index scan.
If we want to force the optimiser we need to create another covering index.
DROP INDEX lengthwidth ON cars
CREATE INDEX lengthwidthcolour ON cars (length,width,colour)
select id,length,width,colour from cars WHERE length =2.9 AND width=1.5
i drop the original non-clustered index. I create a new one that covers all the columns in the query. Then I run my query again. This time the optimiser chooses a non-clustered index seek.
This is the result of the query above
6 2.9 1.5 black
In the same query window type the following t-sql statement and then execute it.
--get the contents of the non-clustered index
select cast(length as varchar(4)) + ',' + cast(width as varchar(4)) + ', ' + colour +
',' + cast(id as varchar(4)) from cars order by length, width,colour
You will get the following results
2.2,1.5, red,9
2.6,1.2, brown,7
2.8,1.4, yellow,4
2.9,1.2, blue,3
2.9,1.5, black,6
3.0,1.2, white,5
3.0,2.0, green,2
3.2,1.2, white,8
3.3,1.8, black,1
As you can see all the data that I need is in the non-clustered index.
let's see what happens when we do a simple update in the table
[sourcecode language="sql"]
UPDATE cars SET colour ='brown' WHERE id=8
--get the contents of the non-clustered index
select cast(length as varchar(4)) + ',' + cast(width as varchar(4)) + ', ' + colour +
',' + cast(id as varchar(4)) from cars order by length, width,colour
[/sourcecode]
So I execute the simple update above. Obviously the update will be successful.
If we see the contents of the index (by executing the statement)we will see different results than previously. The data in the index page has been re-ordered.
2.2,1.5, red,9
2.6,1.2, brown,7
2.8,1.4, yellow,4
2.9,1.2, blue,3
2.9,1.5, black,6
3.0,1.2, white,5
3.2,1.2, brown,8
3.0,2.0, green,2
3.3,1.8, black,1
Than means that SQL Server had to do some extra work. So we must be careful with covering indexes that include columns that have frequently updates.
Let's find a quick way around that problem. In the same query window type
[sourcecode language="sql"]
drop INDEX lengthwidthcolour ON cars
CREATE INDEX newlengthwidthcolour ON cars (length,width) INCLUDE (colour)
UPDATE cars SET colour ='blue' WHERE id=8
--get the contents of the non-clustered index
select cast(length as varchar(4)) + ',' +
cast(width as varchar(4)) + ', ' + colour +
',' + cast(id as varchar(4))
from cars order by length, width,colour
[/sourcecode]
In this bit of code we drop the index and we create a new one using the INCLUDE keyword for the colour column.
2.2,1.5, red,9
2.6,1.2, brown,7
2.8,1.4, yellow,4
2.9,1.2, blue,3
2.9,1.5, black,6
3.0,1.2, white,5
3.0,2.0, green,2
3.2,1.2, blue,8
3.3,1.8, black,1
You can see that there was no need for the index to reorder its contents, so there is no extra cost.
Try the examples above and you will understand what covering indexes are and why we use them.
Hope it helps!!!!