none
Primary Key as an included column

    Question

  • Hi all,

    I have been using the missing index dmv as a guide for indexing opportunities.

    One of the 'missing indexes' identified has listed primary key as an included column. As primary keys are always included in an index - is there any purpose for having a primary key as an included column?

    Note, my primary key is on a single column - it is a surrogate key, bigint.

    And here is the code i'm using to ID potential indexes:

    SELECT mig.*, 
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    s.user_seeks * s.avg_total_user_cost * (s.avg_user_impact * 0.01) AS [index_rating], 
    s.user_scans,
    s.user_seeks,
    s.avg_total_user_cost,
    s.avg_user_impact
    FROM sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)  ON mig.index_handle = mid.index_handle 
    INNER JOIN sys.dm_db_missing_index_group_stats as s WITH (NOLOCK)  ON S.group_handle = mig.index_group_handle
    ORDER BY 
    index_rating desc

    Thanks for reading.

    Clay

    Sunday, July 21, 2013 11:17 PM

Answers

  • DTA is a good tool, but we can not accept all the suggests as it is. Clustered index is a part of all non-clustered index for a clustered table. Hence you can ignore the recommendation, the cluster key as included column.

    To be clear, you may use DBCC IND and PAGE to see the non index index where you can see clustered index is part of the structure.



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, July 22, 2013 4:44 AM

All replies

  • I think it's clustered keys are always effectively included, if this is NOT your PK, that would explain it.

    Josh

    Monday, July 22, 2013 12:14 AM
  • Yeah sorry - I should've said - it is the clustered key.

    And as the clustered key is stored with all indexes for lookup purposes - I cannot see any reason why to store it in an included column.

    My guess is that this is just a shortcoming of the missing index dmvs.

    Thanks for your reply.

    Monday, July 22, 2013 12:37 AM
  • In index suggestions given , if the clustered index key is part of the select statement, the Missing Indexes/ DTA suggests to have it in INCLUDE columns list.  It looks like the way Tuning adviser works. As we knew that Clustered key is also stored along with NCI index key, may be we can ignore that part alone.

    Have look at the below link, http://www.confio.com/logicalread/sql-server-index-hints/#.UeyYhI1mzpU

     

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, July 22, 2013 2:32 AM
  • DTA is a good tool, but we can not accept all the suggests as it is. Clustered index is a part of all non-clustered index for a clustered table. Hence you can ignore the recommendation, the cluster key as included column.

    To be clear, you may use DBCC IND and PAGE to see the non index index where you can see clustered index is part of the structure.



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, July 22, 2013 4:44 AM