What is difference between composite index and cover index?

Question

when we create index then we can mention multiple column name and that is called composite index but when we create cover index then we create index on one column and for cover index we mention other column in include function.

so i like to know what is difference between composite index and cover index?
and also tell me which one i should use ?

give me 2 sample scenario where composite index will be right choice and another scenario where cover index will be right choice.

thanks

Answer 1

I have said this before, but it appears that I need to repeat it.

An index alone cannot be said to be a covering index. An index can cover a specific query, and thus be a covering index for that query. At the same time, the same index may be useful for another query, even if it is not a covering index for that query.

A composite index is an index with multiple index key. A composite index can very well be covering index for a query.

If you are asking about the INCLUDE clause, you would typically add columns to the INCLUDE clause when you are not using them to find data. Say that you have

SELECT a, b FROM tbl WHERE c = @val AND d = @val2

These are all covering indexes for this query:
CREATE INDEX ix1 ON tbl(c, d, a, b)
CREATE INDEX ix2 ON tbl(c, d) INCLUDE (a, b)
CREATE INDEX ix3 ON tbl(d) INCLUDE (a, b, c)
CREATE INDEX ix4 ON tbl(c) INCLUDE (a, b, d)

as is any other permutation of a, b, c, d. But the first two would be more efficient than ix3 and ix4 for this query, and ix4 would be better than ix3.

ix2 is the "best" index, because a and b will not take up space in the higher levels of the index tree. Also, if a or b are updated, in ix2 there can be no page splits or similar as the index tree is unaffected.

Then again, there can be other queries that also includes a maybe also b in the WHERE condition, and then you want ix1.

Answer 2

Hi @Sudip Bhatt ，

Both composite index and covering index are non-clustered indexes.You need to understand the storage principle of non-clustered index: the non-clustered index does not store the actual data, but the location information of the stored data. When querying, first find the location information of the data through the non-clustered index, and then go to the basic table to find the data.
Simply put, the covering index contains the data to be searched through include, so that the SQL query can get the required data without reaching the basic table. The composite index is to create an index on the combination of multiple columns, these columns may contain all the columns of the query, or may not contain. When the column in the composite index contains all the columns in the query, the index is also a covering index.When the composite index does not contain all the data to be queried, the query needs to find the location information of the data through the non-clustered index, and then go to the basic table to find the data.
Generally speaking, building a covering index is more efficient than building a composite index.

The following is a practical example, you can clearly see the difference between composite index and covering index：

CREATE TABLE dbo.Users  
(  
  username  VARCHAR(14)  NOT NULL,  
);  
  
CREATE TABLE dbo.Sessions  
(  
  id INT NOT NULL IDENTITY primary key,  
  username VARCHAR(14) NOT NULL,  
  starttime DATETIME2(3) NOT NULL,  
  endtime DATETIME2(3) NOT NULL,  
  pty INT NOT NULL,  
  CONSTRAINT CHK_endtime_gt_starttime  
    CHECK (endtime > starttime),  
  CONSTRAINT CHK_priority_range  
    CHECK (pty BETWEEN 1 AND 31)  
);  
  
-- Sample data (small)  
--TRUNCATE TABLE dbo.Sessions;  
--TRUNCATE TABLE dbo.Users;  
  
INSERT INTO dbo.Users(username) VALUES('User1'), ('User2'), ('User3');  
INSERT INTO dbo.Sessions(username, starttime, endtime, pty) VALUES  
  ('User1', '20160101 08:00:00.000', '20160101 08:30:00.000', 1),  
  ('User1', '20160101 08:05:00.000', '20160101 08:35:00.000', 2),  
  ('User1', '20160101 08:00:00.000', '20160101 08:30:00.000', 3),  
  ('User2', '20160101 08:00:00.000', '20160101 10:30:00.000', 3),  
  ('User2', '20160101 08:30:00.000', '20160101 10:00:00.000', 2),  
  ('User2', '20160101 09:00:00.000', '20160101 09:30:00.000', 1),  
  ('User2', '20160101 11:00:00.000', '20160101 12:00:00.000', 3),  
  ('User2', '20160101 11:30:00.000', '20160101 12:30:00.000', 2),  
  ('User2', '20160101 11:40:00.000', '20160101 12:40:00.000', 3),  
  ('User2', '20160101 12:00:00.000', '20160101 13:00:00.000', 2),  
  ('User3', '20160101 08:00:00.000', '20160101 09:00:00.000', 1),  
  ('User3', '20160101 08:00:00.000', '20160101 08:30:00.000', 2),  
  ('User3', '20160101 08:30:00.000', '20160101 09:00:00.000', 3),  
  ('User3', '20160101 09:30:00.000', '20160101 09:31:00.000', 1);  
  
IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;  
GO  
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE  
AS  
RETURN  
  WITH  
    L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),  
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),  
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),  
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),  
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),  
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),  
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum  
         FROM L5)  
  SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n  
  FROM Nums  
  ORDER BY rownum;  
GO  
  
DECLARE   
  @num_users      AS INT      = 2000,  
  @intervals_per_user AS INT      = 500,  
  @start_period       AS DATETIME2(3) = '20160101',  
  @end_period     AS DATETIME2(3) = '20160107',  
  @max_duration_in_ms AS INT  = 3600000; -- 60 nimutes  
    
--TRUNCATE TABLE dbo.Sessions;  
--TRUNCATE TABLE dbo.Users;  
  
INSERT INTO dbo.Users(username)  
  SELECT 'User' + RIGHT('000000000' + CAST(U.n AS VARCHAR(10)), 10) AS username  
  FROM dbo.GetNums(1, @num_users) AS U;  
  
WITH C AS  
(  
  SELECT 'User' + RIGHT('000000000' + CAST(U.n AS VARCHAR(10)), 10) AS username,  
    DATEADD(ms, ABS(CHECKSUM(NEWID())) % 86400000,  
  DATEADD(day, ABS(CHECKSUM(NEWID())) % DATEDIFF(day, @start_period, @end_period), @start_period)) AS starttime,  
    ABS(CHECKSUM(NEWID())) % 3 + 1 AS pty  
  FROM dbo.GetNums(1, @num_users) AS U  
    CROSS JOIN dbo.GetNums(1, @intervals_per_user) AS I  
)  
INSERT INTO dbo.Sessions WITH (TABLOCK) (username, starttime, endtime, pty)  
  SELECT username, starttime,  
    DATEADD(ms, ABS(CHECKSUM(NEWID())) % @max_duration_in_ms + 1, starttime)  
  AS endtime,  
    pty  
  FROM C;  

 

--create a compound index  does not include the pty column, the query needs to go to the basic table   
--to find the data to be returned  
create nonclustered index index1 on dbo.Sessions(username,starttime)  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515' or starttime='20160101 08:00:00.000'  
  
--covering index the pty column is included in the index, and all the data to be returned can be found   
--on the non-clustered index without going to the basic table to find it.  
DROP index index1 on dbo.Sessions  
create nonclustered index index2 on dbo.Sessions(username) include(pty)  
DBCC dropcleanbuffers --Empty the cache  
set statistics io on --Open IO statistics   
select username,pty from dbo.Sessions where username='User0000000515'  
  
  
DROP index index1 on dbo.Sessions  
DROP index index2 on dbo.Sessions  

  
drop table dbo.Users  
drop table dbo.Sessions  

When there are multiple conditional columns behind where, you can consider building a composite index on these columns. When the columns returned by select are not many, consider building a covering index on these columns，include the columns to be returned in include().

Of course, this is just a simple description, there are many factors that need to be considered when indexing.Can't tell you which one should be used, what type of index to build depends on the actual data and your needs. Of course, you can try according to the basic principles I mentioned above, and then see which index is more efficient. Or publish your data and requirements when you need to build an index, and let the experts advise you.

Best Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.