Reputation: 769
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.
Upvotes: 5
Views: 9830
Reputation: 3203
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.
Upvotes: 6