Database Reference
In-Depth Information
Figure 7-10.
Part of the execution plan: insert with check constraint
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 118 ms.
Now let's see what happens when we are calling a system function from the check constraint. Let's change the
constraint definition, as shown in Listing 7-14.
Listing 7-14.
Check constaints: Replacing CHECK constraints with one that calls a standard function
alter table dbo.CheckConstraintTest
drop constraint CHK_CheckConstraintTest_Value;
alter table dbo.CheckConstraintTest
with check
add constraint CHK_CheckConstraintTest_Value
check (Right(Value, 1) = 'C')
After we run our insert again, the execution time is as follows:
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 131 ms.
While system functions do not necessarily introduce huge overhead in terms of CPU load and execution time,
user-defined functions are a different story. Let's create a simple UDF, and see how it affects performance. The code is
shown in Listing 7-15.
Listing 7-15.
Check constaints: Replacing CHECK constraints with one that calls a UDF function
create function dbo.DummyCheck(@Value varchar(32))
returns bit
with schemabinding
as
begin
return (1)
end
go
alter table dbo.CheckConstraintTest
drop constraint CHK_CheckConstraintTest_Value;
alter table dbo.CheckConstraintTest
add constraint CHK_CheckConstraintTest_Value
check (dbo.DummyCheck(Value) = 1)
