title: When to use composite index and covering index in SQL Server?
body: <p>I have 2 tables <code>tb_player1</code> and <code>tb_player2</code>.</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>The <code>tb_player1</code> has a composite index and the <code>tb_player2</code> has included column (covering index).</p>
  
  <p>I run the following SQL statements against the <code>tb_player1</code> and <code>tb_player2</code>, but the actual execution plan of <code>tb_player1</code> and <code>tb_player2</code> are the same.</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>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.</p>