Post reply

Primary Key and unique index on the same fields.

  • February 25, 2009 at 9:15 am

    #218754

    I have a table where the clustered primary key is on (A,B).

    I also have an index on fields (B,A).

    Is there any advantage on having the above index marked as unique? (As the uniqueness is already enforced by the primary key.) Or is it simply a cost with no benefits?

    Thanks,

    David McKinney.

  • February 25, 2009 at 9:36 am

    #950103

    I'm not sure why you'd have an index like that at all, but yeah, if each of those indexes is used independently by your queries, you will see a benefit from marking the second one as unique. The optimizer takes advantage of that information when it makes choices about how to process your queries.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply