I have one question regarding include column in the indexes. If I have an include column which is updating its value quite often, does it update the index every time it's updating the value or does the index only update when key columns are updating? Does include column updates add performance issues? According the example below, if the Balance and DiscountedAmount keep updating does it update the index IX_Temp_RegistrationNumber for every updates?

CREATE TABLE #Temp (
    TheID BIGINT
    ,RegistrationNumber BIGINT
    ,ClientName VARCHAR(50)
    ,Balance NUMERIC(10, 2)
    ,DiscountedAmount NUMERIC(10, 2)
    )

CREATE NONCLUSTERED INDEX [IX_Temp_RegistrationNumber] ON [#Temp] ([RegistrationNumber] ASC) INCLUDE (
    [Balance]
    ,[DiscountedAmount]
    )
GO 
  • 1
    Of course, values in included columns must be updated. If a covering index is used, all data will be read from it without touching a table (or a clustered index). It would be impossible if the data weren't updated. – Denis Rubashkin Dec 5 at 12:39
  • Thank you Denis. – ChetanD Dec 5 at 17:49

Actually if you get Actual Plan, you will be able to see, indexes that modified by every update.

When you do update, Cluster Index and all indexes that keys and included column , had updated, will be modified.

Example 1 :
update #Temp set ClientName = 'foo'
Execution plan : The clustered index will be update

Example 2:
update #Temp set Balance = 'foo'
Execution plan : The clustered index and [IX_Temp_RegistrationNumber] will be update.

Tip :If your table haven't so many indexes or update operations isn't high, then there isn't any performance issue.

  • 1
    But the overhead is lower compared to have the column(s) in the key. The data is there, but only at the leaf level. And the included columns aren't sorted (so the row doesn't have to move if the value change). – Tibor Karaszi Dec 5 at 11:53
  • Thank you Tibor. I got understanding now. – ChetanD Dec 5 at 17:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.