HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

What happens to non clustered index when included column is updated using update statement?

Submitted by: @import:stackexchange-dba··
0
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

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?

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.