Post reply

non clustered index on unique key column

  • January 29, 2014 at 2:27 am

    #396183

    Hi,

    In our local development database, for some tables we are defining both primary key (clustered)

    and non clustered on the same column.

    But the observation is when there is only primary key is defined, clustered index scan

    is occuring but after that if a non clustered index is defined on the same column

    then I can see clustered index seek.

    I am not sure if this approach is right.

    I need your suggestion on this, Correct me if I am wrong

    Thanks in Advance

  • January 29, 2014 at 2:29 am

    #1684197

    sorry, about the subject heading

    it is "non clustered index on primary key column"

  • January 29, 2014 at 3:05 am

    #1684205

    There are very, very, very few times when it makes sense to put a nonclustered index on the same column as the clustered primary key. Usually it's a waste of space and time. If it's just a nonclustered index on exactly the same column, you won't get an index seek on it unless it's also possible to seek on the clustered index.

    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 29, 2014 at 3:12 am

    #1684208

    Please suggest me a way to avoid clustered index scan when only primary key is defined.

  • January 29, 2014 at 3:24 am

    #1684212

    Write your queries in such a way that they can seek on the primary key, or add indexes to support the queries. Adding a NC index on exactly the same column as the primary key is not going to change a clustered index scan into a clustered index seek.

    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 5 posts - 1 through 4 (of 4 total)

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