title: Non-clustered foreign key index recommendation
body: <p>I have several tables in my database that are parent child relationships.  For example:</p>
  
  <pre><code>CREATE TABLE [dbo].[cntnr_header] 
  (
      [cntnr_id]            [dbo].[uid]     NOT NULL,
      CONSTRAINT [pk_cntnr_header] PRIMARY KEY CLUSTERED ([cntnr_id] ASC),
  );
  
  CREATE TABLE [dbo].[cntnr_content] 
  (
      [cntnr_content_id]  [dbo].[uid]       NOT NULL,
      [cntnr_id]          [dbo].[uid]       NOT NULL,
      CONSTRAINT [pk_cntnr_content] 
         PRIMARY KEY CLUSTERED ([cntnr_content_id] ASC),
      CONSTRAINT [fk_cntnr_content_cntnr_header] 
         FOREIGN KEY ([cntnr_id]) 
         REFERENCES [dbo].[cntnr_header] ([cntnr_id]),
  );
  </code></pre>
  
  <p>Currently as you can see there is not an index on the foreign key <code>cntnr_id</code> in the table <code>cntnr_content</code>. I ran the tuning wizard and I actually saw it suggest a non-clustered index be added for both <code>cntnr_content_id</code> and <code>cntnr_id</code> on the table <code>cntnr_content</code>.  I don't really understand this because <code>cntnr_content_id</code> is already a clustered index.  Why would it recommend an index like this?</p>
  
  <pre><code>CREATE NONCLUSTERED INDEX [_dta_index_cntnr_content_7_821577965__K1_K2] ON [dbo].[cntnr_content]
  (
      [cntnr_content_id] ASC,
      [cntnr_id] ASC
  )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
  </code></pre>
  
  <p>I would think that I probably should add a non-clustered index just on <code>cntnr_id</code> in this table.  </p>
  
  <p>Is there a recommended practice for this scenario? Should I always add certain indexes with relationships like this?</p>
  
  <p>A lot of the queries are either joining these two tables together on <code>cntnr_id</code> or doing a select on <code>cntnr_content</code> by specifying the <code>cntnr_id</code>.  This is also an update/delete heavy table.  Updates and deletes are always done on the primary key (<code>cntnr_content_id</code>).</p>