我有两个表tb_player1
和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
有一个组合索引,而tb_player2
有包含列(覆盖索引)。
我对tb_player1
和tb_player2
运行了以下SQL语句,但是tb_player1
和tb_player2
的实际执行计划是相同的。
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'
何时在SQL Server中使用复合索引和覆盖索引?它们之间有什么区别?为什么它们的执行计划看起来没有任何不同。