When to use composite index and covering index in SQL Server?

Question

I have 2 tables tb_player1 and tb_player2.

CREATE TABLE tb_player1 
(
    pk_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
    first_name CHAR(16),
    last_name CHAR(16),
    age INT
)

CREATE NONCLUSTERED INDEX ix_nonclustered_name ON tb_player1(first_name, last_name)

CREATE TABLE tb_player2 
(
    pk_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
    first_name CHAR(16),
    last_name CHAR(16),
    age INT
)

CREATE NONCLUSTERED INDEX ix_nonclustered_name ON tb_player2(first_name)
INCLUDE (last_name)

The tb_player1 has a composite index and the tb_player2 has included column (covering index).

I run the following SQL statements against the tb_player1 and tb_player2, but the actual execution plan of tb_player1 and tb_player2 are the same.

INSERT INTO tb_player1 VALUES('kenny', 'lee', 29)
INSERT INTO tb_player1 VALUES('rose', 'chao', 27)
INSERT INTO tb_player1 VALUES('mark', 'chao', 25)

INSERT INTO tb_player2 VALUES('kenny', 'lee', 29)
INSERT INTO tb_player2 VALUES('rose', 'chao', 27)
INSERT INTO tb_player2 VALUES('mark', 'chao', 25)



select first_name, last_name from tb_player1 where first_name = 'kenny'
select first_name, last_name from tb_player2 where first_name = 'kenny'

select first_name, last_name from tb_player1 where last_name = 'lee'
select first_name, last_name from tb_player2 where last_name = 'lee'

select first_name, last_name from tb_player1 where first_name = 'kenny' AND last_name = 'lee'
select first_name, last_name from tb_player2 where first_name = 'kenny' AND last_name = 'lee'

select first_name, last_name from tb_player2 where last_name = 'lee' AND  first_name = 'kenny'
select first_name, last_name from tb_player1 where last_name = 'lee' AND  first_name = 'kenny'

When to use composite index and covering index in SQL Server? What's the differences between them? Why the execution plan of them looks no different.

Answer 1

1. The composite index (where all of the columns are “key” columns) will carry the data in all levels of the index; the INCLUDE index will only carry the “non key” columns in the leaf node. Take away: The INCLUDE index will be smaller than the equivalent composite index.

2. The INCLUDE columns(non-key) will not count towards the max index size (900 bytes).

3. Updates to non-key columns in INCLUDE indexes will not contribute to index fragmentation; updates to unused “key” columns in the composite index will result in fragmentation… IMO, this is a biggie.

4. Assuming low index fragmentation, both composite and INCLUDE indexes will perform comparably. All columns in the composite index need not be in the query predicate.