Post reply

Updating Non clustered indexes included columns or 2nd + columns

  • May 19, 2017 at 8:37 am

    #325835

    Hi

    I'm sure this has been asked before but here I go again.

    How does SQL find columns on non clustered indexes that have their values updates, if they're not the left most column?
    I can't see how it would seek to find them.  Does that mean it would have to scan indexes if they have columns that need updating that aren't the left most?

    So for instance if I have a table People with col1, col2, and col3
    I have a non clustered index on col1, col2 
    and I run
     update people SET col 2 = 'Change' where col3 =2

    How does the SQL engine find the col 2 records to be updated in the non clustered index?

    Thanks for the replies

    Alex

  • May 19, 2017 at 8:50 am

    #1943330

    It can do a seek, since it'll have already updated the clustered index, and hence knows all the values of the other columns in the row that's been changed.

    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

  • May 19, 2017 at 8:54 am

    #1943331

    That makes sense.

    Thanks Gail

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

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