Post reply

Including additional columns in a unique clustered index

  • March 4, 2013 at 5:46 pm

    #1593238

    dembones79 (3/4/2013)

    GilaMonster (3/4/2013)

    So 'keep the keys for a unique index to the columns that need to be enforced unique' is a 'rigid overly-simplistic rule'?

    Bear in mind that this is for an indexed view. Uniqueness is already enforced on the table. I suppose the clustered index on the view does not necessarily need to be unique. Does that paint a slightly different picture for you?

    The clustered index on a view does have to be unique, as per the extract from BoL that I just referenced, so leaving it not unique is not an option here.

    I personally hold to the indexing strategy taught by people like Paul Randal (ex storage engine team developer in case you didn't know), keep the clustered index as narrow as possible because of the impact on nonclustered indexes and on the depth of the clustered index itself. Less important on a view where you probably aren't adding other nonclustered indexes, so in this case, without seeing exec plans or data, I might create the clustered index on region and district and leave unit out entirely. Would have to see queries, table designs and some data to tell for sure.

    Just one other point, you said 'a distinct list of...'. Distinct isn't allowed in an indexed view either, so you might have to look at alternatives here, maybe a group by without aggregation (max, min, avg also aren't allowed). Would need more info to tell for sure.

    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

Viewing post 16 (of 15 total)

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