Post reply

Clustered and NonClustered Index

  • January 13, 2016 at 11:54 pm

    #1851820

    This is the reason we don’t include Clustered index columns in Non Clustered indexes because even though you mention clustered index columns as a part of your non-clustered index key or not, clustered index columns comes to leaf level of non-clustered index .

    Incorrect. If you need the column there, explicitly specify it. If you specify the clustered index key as part of your index key, it will be part of the index key, not the leaf level.

    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

  • January 14, 2016 at 2:18 am

    #1851837

    Thanks Gail, for correcting me. I just tested that.

    So what I see that for non-unique nonclustered index, clustered index keys becomes the part of nonclustered index keys.

    However for unique non-clustered index, after explicitly writing clustering index key, they become a part of nonclustered index keys.

    But, in what scenario adding clustered index keys in unique non clustered index can be useful since the unique non clustered index has unique column which is easily seekable

  • January 14, 2016 at 2:58 am

    #1851842

    er.mayankshukla (1/14/2016)

    But, in what scenario adding clustered index keys in unique non clustered index can be useful since the unique non clustered index has unique column which is easily seekable

    And if someone changes the clustered index? If you haven't explicitly specified the clustered index keys and you need them, suddenly your index isn't covering any longer.

    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

  • January 14, 2016 at 3:13 am

    #1851846

    Yes that makes sense.

    Thank you

    I will correct my post

  • January 15, 2016 at 1:36 am

    #1852175

    Interesting and I admit I hadn't thought the whole indexing thing that far through yet (**), but it sure makes sense.

    So, in a way this means there is no such a thing as a non-unique index then? =)

    Cu

    Roby

    (**: I'm sure there are thousands of optimisations in there that I would never have come up with myself. Respect to the people making MSSQL such a great tool!)

  • January 15, 2016 at 2:15 am

    #1852179

    deroby (1/15/2016)

    So, in a way this means there is no such a thing as a non-unique index then? =)

    http://sqlinthewild.co.za/index.php/2009/02/09/all-indexes-are-unique/

    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

  • January 15, 2016 at 5:24 am

    #1852216

    deroby (1/15/2016)

    So, in a way this means there is no such a thing as a non-unique index then? =)

    When looking at low-level implementation details, yes.

    Functionally, no. There are lots of functional differences between unique and nonunique indexes. So you will want to continue defining indexes are unique when the combination of indexed columns is unique.

    (* You probably already knew this; I just wanted to make sure that others reading this will not come to the wrong conclusion)

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 15, 2016 at 5:33 am

    #1852219

    When looking at low-level implementation details, yes.

    Functionally, no. There are lots of functional differences between unique and nonunique indexes. So you will want to continue defining indexes are unique when the combination of indexed columns is unique.

    Hi Hugo,

    Can you mention those differences or guide me to some blog on that ?

  • January 15, 2016 at 6:12 am

    #1852227

    er.mayankshukla (1/15/2016)

    When looking at low-level implementation details, yes.

    Functionally, no. There are lots of functional differences between unique and nonunique indexes. So you will want to continue defining indexes are unique when the combination of indexed columns is unique.

    Hi Hugo,

    Can you mention those differences or guide me to some blog on that ?

    If you define an index as unique, SQL Server will ensure that no duplicate values occur for the combination of all indexed columns. Just as with a PRIMARY KEY or UNIQUE constraint.

    Also, if an index is defined as unique, SQL Server can use its knowledge of the uniqueness to create execution plans that are more efficient.

    My personal preference is to use PRIMARY KEY and UNIQUE constraints to enforce business rules, and use indexes for performance only. But if an index happens to include a set of columns that makes it unique, I will declare it as such to give SQL Server that information. (And some business rules cannot be enofrced with a generi UNIQUE constraint, but can be enforced with e.g. a filtered index - in cases like that I will have to accept an exception to my generic rule)

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • January 16, 2016 at 6:30 am

    #1852364

    Thanks Hugo for explaining that

Viewing 10 posts - 16 through 24 (of 24 total)

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