Non-Clustered index updates

  • Hi All,

    Basic question is: When does the non-clustered index update (when we do any updates on table)?

    We have created a non-clustered index, when we update the table's column which is the key of non-clustered index then the execution plan is showing only clustered index update.

    What about the non-clustered index update?

    How the non-clustered index key values modified?

    Include Column index:

    If we have a non-clustered index by including the column

    Ex: TestTable -- id int P.K, Name varchar(50), DeptID INT

    i have created non-clustered index on Name by including the DeptID

    create index idx_name

    on TestTable (Name) Include (Deptid)

    that means the non-clustered index leaf level pages contains DeptID also.

    My Question is when we update Deptid column of TestTable, how the non-clustered index leaflevel will be updated? Will this non-clustered index update? my execution plan is not showing the non-clustered update.

    Please find the attached scripts/execution plan of update statement.

    🙂

  • During the execution of the insert/update/delete always. A nonclustered index is never out of date.

    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
  • That is correct but how it will do?

    Say in our case TestTable if we update DeptID of any user then the actual data page of clustered index will be updated first (this is the only actual physical page), and question comes to Non-clustered will the Depid also update at the non-clustered leaf level?

    That means two updates at page levels?

    Thank you

    🙂

  • Absolutely. Has to be, otherwise the nonclustered indexes don't match the table (and that's called corruption). Could be more than that if there is an index on that column as the non-leaf levels may need updating too.

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

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