We all know about the indexes and the concept of it. As well as the types of the indexes. During working one query optimization, I got suggested by the SQL Server Optimizer with index creation and that index was Covering index.
What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,
Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.
(Clink on image to enlarge)
I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.
What is covering index?
It is an additional index with includes columns in definition which are exists in select list. Per msdn and book online, The term covering index does not mean a separate kind of index having a different internal structure. Rather, this term is used to describe a certain technique that is used to improve performance. It will better if we go through the demo and example. Let us start with objects creation,
USE DEMO GO -- Creating table which will be used for demo IF (OBJECT_ID('TblCoveringIndex','U') > 0) DROP TABLE TblCoveringIndex CREATE TABLE TblCoveringIndex ( ObjectId bigint, ObjectName varchar(100), CreateDate datetime, ObjectType varchar(100) ) GO -- Inserting some sample records in table INSERT INTO TblCoveringIndex SELECT TOP 80000 convert(bigint,CONVERT(VARCHAR(100),a.object_id) + CONVERT(VARCHAR(100),b.object_id) ), CONVERT(VARCHAR(100),a.name) + CONVERT(VARCHAR(100),b.name) , B.create_date, b.type_Desc FROM SYS.OBJECTS A CROSS JOIN SYS.OBJECTS B GONow we will review the execution plan without any indexes created on the table.
-- Execution plan with any indexes SELECT ObjectId, ObjectName, ObjectType FROM TblCoveringIndex WHERE CreateDate >= GETDATE() - 50 GO
Now We will create a one clustered index, Normal non-clustered index and an additional non cluster index.
-- Creating clustered index on ObjectId column. CREATE CLUSTERED INDEX IX_ObjectId ON TblCoveringIndex (ObjectId) GO -- Creating normal nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_CreateDate on TblCoveringIndex (CreateDate) GO -- Creating covering nonclustered index on CreateDate column. CREATE NONCLUSTERED INDEX IX_CreateDate_Covering ON TblCoveringIndex (CreateDate) INCLUDE (ObjectId,ObjectName,ObjectType) GOWe have created an additional non-clustered index with an included column, which are going to be used in the select list. After index's creation, It is finally time to review for each index created and execution plans of the queries using each of them. Here we force the query to use the normal non-clustered index and addition non-clustered index created.
SELECT ObjectId, ObjectName, ObjectType FROM TblCoveringIndex with (index (IX_CreateDate)) -- Forcing index hint of normal nonclustered index WHERE CreateDate >= GETDATE() - 50 -- Applied column filter on which the indexes created SELECT ObjectId, ObjectName, ObjectType FROM TblCoveringIndex with (index (IX_CreateDate_Covering)) -- Forcing index hint of additional nonclustered index WHERE CreateDate >= GETDATE() - 50 -- Applied column filter on which the indexes created GO
(Clink on image to enlarge)
I hope you like this post. Before apply this type of index please verify execution plans and performance review and decide which indexes are better for the query plan.
Comments
Post a Comment