Databases Reference
In-Depth Information
SELECT
*
INTO
dbo
.
Product
FROM
Production
.
Product
Listing 6-6.
Run the following
UPDATE
statement, which produces the execution plan on Figure 6-7.
UPDATE
dbo
.
Product
SET
ListPrice
=
ListPrice
*
1.2
Listing 6-7.
Figure 6-7:
An update without Halloween protection.
No Halloween protection is needed in this case, as the statement updates the
ListPrice
column, which is not part of any index, and so updating the data does
not move any rows around. Now, to demonstrate the problem, I'll create a clustered
index on
ListPrice
column.
CREATE
CLUSTERED
INDEX
cix
ON
dbo
.
Product
(
ListPrice
)
Listing 6-8.
Run the same
UPDATE
statement from Listing 6-7 again. The query will show a similar
plan, but this time including a Table Spool operator, which is a blocking operator,
separating the read section from the write section. A blocking operator has to read
all
of
the relevant rows before producing any output rows to the next operator. In this example,
