patternMinor
What happens to non clustered index when included column is updated using update statement?
Viewed 0 times
clusteredupdatewhatnoncolumnstatementusingincludedwhenhappens
Problem
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
-- this is new non clustered index with included columns
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?
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 = 19If 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?
Solution
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.
Context
StackExchange Database Administrators Q#15241, answer score: 4
Revisions (0)
No revisions yet.