Sql-server – What happens to non clustered index when included column is updated using update statement

clustered-indexsql-server-2008

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?

Best Answer

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.

Related Question