8

Question on non clustered index with included columns (DB - MS SQL Server). I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is executed and clustered index and non clustered index are defined for table.
I have question on non clustered index with included columns. I'm referring same example provided by blogger

CREATE TABLE T (PK INT, A INT, B INT, C INT, D INT, E INT)
CREATE UNIQUE CLUSTERED INDEX TPK ON T(PK)

CREATE INDEX TB ON T(B)
CREATE INDEX TCD ON T(C,D)
CREATE INDEX TE ON T(E)

-- this is new non clustered index with included columns

CREATE INDEX TF ON T(E) INCLUDE(A)


INSERT T VALUES(0, 10, 20, 30, 40, 50)

UPDATE T SET A = 19

If no index TF is defined then only update on clustered index will be performed and nonclustered index insert and delete operations will not be performed. But what will happen when TF is defined?

Improve this question

3 Answers 3

Reset to default
4

The blog post you reference also indicates how you could have answered this yourself.

If you execute

SET SHOWPLAN_TEXT ON;
GO
UPDATE T SET A = 19;

The plan looks like

  |--Clustered Index Update(OBJECT:([AdventureWorks2008].[dbo].[T].[TPK]), OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), SET:([AdventureWorks2008].[dbo].[T].[A] = [@1]))
       |--Compute Scalar(DEFINE:([Expr1009]=[Expr1009]))
            |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN CASE WHEN [AdventureWorks2008].[dbo].[T].[A] = [@1] THEN (1) ELSE (0) END THEN (0) ELSE (1) END))
                 |--Top(ROWCOUNT est 0)
                      |--Index Scan(OBJECT:([AdventureWorks2008].[dbo].[T].[TF]), ORDERED FORWARD)

showing a per-row / narrow plan where the index TF is listed as one of the objects updated.

Improve this answer
7
  • After executing "update T set A = 20" statement leaf_update_count is changed instead of leaf_insert_count. It means when update is performed on included column then non clustered leaf node gets updated. It does not perform delete and then insert operation. index_id leaf_insert_count leaf_update_count 5 0 1
    – Delta
    Mar 19, 2012 at 14:59
  • @Delta - So TF has indexid of 5 and its leaf_update_count = 1 (that was in response to earlier version of comment). And yep that's what I would have expected for an included column. Delete/Insert generally happens if you update a key column as then it might need to move. e.g. try UPDATE T SET E = 25
    – Martin Smith
    Mar 19, 2012 at 15:08
  • Yes 5 is index id for TF and its leaf_update_count = 1. When I queried leaf_delete_count and non_leaf_delete_count value for table, these two columns have 0 values. Its difficult to understand why just insert count is updated not the delete count.
    – Delta
    Mar 19, 2012 at 15:11
  • @Delta - Look at leaf_ghost_count. Records get marked as ghosts first then cleaned up later.
    – Martin Smith
    Mar 19, 2012 at 15:12
  • you mean delete_count never contains deleted count where as ghosts contains these values and then later on they are also cleaned up?
    – Delta
    Mar 19, 2012 at 15:20
4

Just as when you update a value in an index that's stored in leaf and non-leaf pages, all those pages get updated with the new value. Columns stored at the leaf level only through include are updated when you update values. It's possible for this to lead to spage splits too.

Improve this answer
2

If TF is defined, it will have to be updated. This is because the value of A is included on the TF leaf level. An update on column A will have to reflect this value on index TF.

This is the reason you have an include index TF, so that you will not have to do a look up on A, when you only need the columns E or A.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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