Using: SQL Server 2008 R2
I am currently stepping through a query execution plan, and have come across an instance of a clustered index update on a table. The issue here is that the columns that are being updated are NOT part of the clustered index.
<table> id INT IDENTITY(1,1) -- Clustered Index , name VARCHAR(20) -- Nonclustered Index , status CHAR(1) , quantity INT , price FLOAT
UPDATE a SET a.status = @status , a.quantity = @quantity , a.price = @price FROM <table> a WHERE a.name = @name
The execution plan shows an Eager Spool at 36% cost, a Clustered Index Update at 55% cost, and an Index Seek on the name index at 9%, among Compute Scalar and Top items at 0% cost.
Why is the plan showing a clustered index update? What could I do to prevent this, and prevent the eager spool?