Post reply

Composite Index VS Unique Non Clustered Index

  • April 26, 2017 at 12:24 am

    #324893

    Hello,

    Good afternoon.

    Currently i am trying to understand about Index structure in SQL server. 

    I am refering to AdventureWorks2012 database to get better understanding about the topic.

    So what i want to understand is

    1. Why is it necessary to arrange attributes as composite index? Is it no advisible to create composite unique non clustered index !

    Please refer to the below image for your kind perusal.

    Thank you.

  • April 26, 2017 at 3:20 am

    #1939888

    That table just has a 2-column primary key. It's now the designers of the database set it up. It probably shouldn't be  (The SalesOrderDetailID column is unique by itself iirc), but that's bad database design for you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 26, 2017 at 10:49 am

    #1940063

    Choosing to compound keys is something you can do for performance because of how the keys are stored or because of how the tables are accessed. It's not necessarily bad design to choose to combine a naturally unique column with another column as the key. I have an example of different database designs, one of which only has a single column key and the other with compound keys. Neither is required or absolute. You need to measure your system and determine what is correct for a given situation.

    "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 3 posts - 1 through 2 (of 2 total)

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