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.

@Kenny_Lee the plans are not exactly the same, if you look at the index seek operation in the last two examples you will see that the first query seeks on firstname and lastname whereas the second seeks on firstname only. This is not significant in your case because you have very few rows, but may have an impact if you have a large number of matching rows. — Steve Ford
– Steve Ford, Commented Dec 15, 2014 at 10:50

Deepak Mishra · Accepted Answer · 2014-12-15 10:53:54Z

6

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.
The INCLUDE columns(non-key) will not count towards the max index size (900 bytes).
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.
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.

answered Dec 15, 2014 at 10:53

Deepak Mishra

3,2531 gold badge29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related