Covered Index

  

In this article we have to learn the covered index. As the name suggest the cover index "COVER" all the columns in the select statements. The indexes are best when they are very small. I think integer data type is a very good data type for index key.

From SQL Server 2005 this functionality is added, nonclustered index is extended by adding non-key columns to the leaf level of the non key columns.  In addition, SQL Server can use these entries in the index's leaf level to perform aggregate calculations.

The Database engine not considers the non key columns when calculating number of index key or the size of the index.

Generally the index takes the 16 key columns and size about 900 bytes. But by adding non key columns we can add more than that and also can add the data types that are not supported by index key.  

But don't be so happy that you must include all of your columns in covered index. Avoid including unnecessary columns.  While covering index boost the retrieval of data they can slow down INSERT, UPDATE and DELETE operations. As because the extra work is needed to maintain the cover index.

The example of covered index is mentioned bellow:

USE my_DB

GO

 

CREATE INDEX IX_Cust_ID

ON mst_Cust (custID)

INCLUDE (CustName, CustAdd)

 

GO

Everything has some good and bad effects. In this case I recommended not using the covered index if necessary.  Not especially any transaction table where we do the frequent data modification and insertion.

Hope you like it.

 

Posted by: MR. JOYDEEP DAS