What happens when a Query execute against a table with a covering index and another query with the same index structure but without the included columns?
“The covering index gives you the
knack to avoid the trip back to the table for satisfying the request, as all of
the columns requested already included in non-clustered index. It means you are
able to escape the logical or physical reads to trip back to read the table for
that information.”
But we can’t ensure this is not enough avoidance of logical or physical
reads. There is also the consideration of the necessary task between the
columns in the non-clustered index and the columns that need to be looked-up in
the table, you can say putting data together.
To explain this let's create two
identical tables; same schema, same data:
CREATE TABLE TSQL2012.dbo.EmplyeeDetails1
pkid INT
IDENTITY(1,1) NOT NULL,
EmployeeId VARCHAR(20)
NOT NULL,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
address1 VARCHAR(100) NULL,
address2 VARCHAR(100) NULL,
stateregion VARCHAR(100) NULL,
Areapostal_code VARCHAR(10) NULL,
Empcompanyid VARCHAR(20) NULL,
registration_notes VARCHAR(200) NULL
ALTER TABLE dbo.EmplyeeDetails1 ADD CONSTRAINT
PK_EmplyeeDetails1 PRIMARY KEY CLUSTERED
,
STATISTICS_NORECOMPUTE =
OFF
CREATE TABLE TSQL2012.dbo.EmplyeeDetails2
pkid INT
IDENTITY(1,1) NOT NULL,
EmployeeId VARCHAR(20)
NOT NULL,
firstname VARCHAR(50) NOT NULL,
lastname VARCHAR(50) NOT NULL,
address1 VARCHAR(100) NULL,
address2 VARCHAR(100) NULL,
stateregion VARCHAR(100) NULL,
Areapostal_code VARCHAR(10) NULL,
Empcompanyid VARCHAR(20) NULL,
registration_notes VARCHAR(200) NULL
ALTER
TABLE dbo.EmplyeeDetails2
ADD CONSTRAINT
PK_EmplyeeDetails2 PRIMARY KEY CLUSTERED
,
STATISTICS_NORECOMPUTE =
OFF
Data in both the table is
identical, also added identical clustered index. Due to auto incremented
identity column added fill factor =100%.
Now making some differences
between tables.
Adding non clustered index on firstname
and lastname with an included column (EmployeeId)
CREATE NONCLUSTERED INDEX [IX_EmplyeeDetails1_lastname] ON [dbo].EmplyeeDetails1
, STATISTICS_NORECOMPUTE = OFF
Adding non clustered index on firstname
and lastname but without the inclusion of column.
CREATE NONCLUSTERED INDEX [IX_EmplyeeDetails2_lastname] ON [dbo].EmplyeeDetails2
, STATISTICS_NORECOMPUTE = OFF
We have two identical tables, each having 5000
records. The only difference is in their single non-clustered
indexes; one has an included column, the other does not. They have
identical primary keys and clustered indexes.
If we execute the following query
against the table with the included column in the clustered index we see the
following execution plan:
SELECT employeeid, lastname, firstname
WHERE lastname = 'samsamada43032' AND firstname = 'samada43032';
SELECT employeeid, lastname, firstname
WHERE lastname = 'samsamada43032' AND firstname = 'samada43032';
The whole action occurs within the
non-clustered index itself. We never have to travel back to the table for more
data. If we execute the identical query against the table with the same index
structure, minus the included column in the non-clustered index, we get
different behavior.
Since the non-clustered index
doesn't contain all the information we need to satisfy the query, it's necessary
to hit the index for the base information, and then using the row pointer in
the non-clustered index, pull in the employeeid from the table and run a Nested
Loop Join in order to marry the two working sets of data together before
returning them to the end user. We can see the difference in expense for reads
when looking at the I/O statistics from the two queries:
Note that these are all logical
reads since the pages were already in the buffer. If this was a situation where
it was a much larger data set you were contending with on an active server with
the pages still on disk, this could be a very expensive operation.
