When to use a composite index and a coverage 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) 

tb_player1 has a composite index, and tb_player2 contains a column (coverage index).

I run the following SQL statements for tb_player1 and tb_player2 , but the actual execution plan for tb_player1 and tb_player2 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 a composite index and a coverage index in SQL Server? What are the differences between the two? Why is the plan for their implementation not different.

Answer 1

• A composite index (where all columns are “key” columns) will carry data at all levels of the index; the INCLUDE index will only contain non-key columns in the node sheet. Take away: the INCLUDE index will be less than the equivalent composite index.

• INCLUDE columns (without a key) will not be counted at the maximum index size (900 bytes).

• Updates to non-classical columns in INCLUDE indexes will not contribute to index fragmentation; Updating unused "key" columns in a composite index will lead to fragmentation ... IMO, this is a biggie.

• Assuming fragmentation with a low index, both composite and INCLUDE indices will be performed relatively. All columns in a composite index do not have to be in the query predicate.