Query Optimization and Execution - Pro SQL Server Internals - page 467

Listing 25-15. Halloween Protection
create table dbo.HalloweenProtection
(
Id int not null identity(1,1),
Data int not null
);
insert into dbo.HalloweenProtection(Data)
select Data from dbo.HalloweenProtection;
The execution plan of the insert statement is shown in Figure 25-12 . SQL Server uses the Table Spool operator to
cache the data from the table prior to insert beginning to avoid an infinite loop during execution.
Figure 25-12. Halloween Protection execution plan
As I mentioned in Chapter 10, “User-Defined Functions,” it is important to use the WITH SCHEMABINDING option
when you define scalar user-defined functions. This option forces SQL Server to analyze if a user-defined function
performs data access and avoids extra Halloween Protection-related Spool operators in the execution plan.
Listing 25-16 shows an example of the code that creates two user-defined functions, using them in where clause
of update statements.
Listing 25-16. Halloween Protection and user-defined functions
create function dbo.ShouldUpdateData(@Id int)
returns bit
as
begin
return (1)
end
go
create function dbo.ShouldUpdateDataSchemaBound(@Id int)
returns bit
with schemabinding
as
begin
return (1)
end
go
update dbo.HalloweenProtection
set Data = 0
where dbo.ShouldUpdateData(ID) = 1;
update dbo.HalloweenProtection
set Data = 0
where dbo.ShouldUpdateDataSchemaBound(ID) = 1;