Why am I causing a Clustered Index Update?

Question

I'm executing the following statement:

UPDATE TOP(1) dbo.userAccountInfo
SET           Flags = Flags | @AddValue
WHERE         ID = @ID;

The column 'ID' is an INT PRIMARY KEY with IDENTITY constraints. Flags is a BIGINT NOT NULL.

The execution path indicates that a Clustered Index Update is occurring. A very expensive operation. There's no indexes covering Flags or ID, except for the primary key. I feel like the actual execution path should be:

Clustered Index Seek => Update

Answer 1

Tables come in two flavors: clustered indexes and heaps. You have a PRIMARY KEY constraint so you have created implicitly a clustered index. You'd have to go to extra length during the table create for this not to happen. Any update of the 'table' is an update of the clustered index, since the clustered index is the table. As for the clustered index update being a 'very expensive operation', now that is an urban legend surrounding basic misinformation about how a database works. The correct statement is 'a clustered index update that affects the clustered key has to update the all non-clustered indexes'.

Answer 2

The clustered index is the physical table, so whenever you update any row, you're updating the clustered index.

See this MSDN article