Database Reference
In-Depth Information
When we run the same insert again, the execution time is as follows:
SQL Server Execution Times:
CPU time = 375 ms, elapsed time = 475 ms.
As you see, now it takes five times as long to run as when table did not have a check constraint specified.
■
We will discuss the performance implications of user-defined functions in greater detail in Chapter 10,
“Functions.”
Note
As with other constraint types, check constraints help us enforce data integrity and, in some cases, lead to better
execution plans. It is a good idea to use them as long as you can live with the overhead that they introduce during data
modification. You can get information about check constraints from the
sys.check_constraints
catalog view.
Wrapping Up
One other important aspect that you need to keep in mind when dealing with foreign key and check constraints is if
the constraints are trusted. When a constraint is not trusted, SQL Server will not guarantee that all data in the table
complies with the constraint rule. Moreover, SQL Server does not take untrusted constraints into consideration during
the query optimization stage. You can see if a constraint is trusted by examining the
is_not_trusted
column in the
corresponding catalog view.
■
in some cases, sQL server can still benefit from untrusted foreign key constraints. they can trigger the Query
optimizer to explore additional join strategies (star join extensions) when the table structure belongs to star or snowflake
schema in Data Warehouse environments.
Note
SQL Server validates constraints during data modifications regardless of whether they are trusted or not. Having
an untrusted constraint does not mean that SQL Server permits violations of it. It means that old data was not
validated at the moment the constraint was created.
You can control if a constraint is created as trusted by using the
WITH CHECK
/
WITH NOCHECK
parameters of the
ALTER TABLE
statement. By using the
WITH CHECK
condition, you force SQL Server to validate if existing data complies
with a constraint rule, which would lead to the table scan. The problem here is that such an operation requires
schema modification (Sch-M) lock, which makes the table inaccessible to other sessions. Such a scan can be very
time consuming on large tables. Alternatively, creating untrusted constraints with the
WITH NOCHECK
condition is a
metadata operation.
■
Note
We will talk about schema locks in greater detail in the Chapter 23, “schema Locks.”
Finally, you always need to name constraints explicitly, even if it is not a requirement, as it is inconvenient to deal
with auto-generated names. With auto-generated names, you need to query the catalog views every time you access
constraints programmatically. Use of auto-generated names also reduces the supportability of a system. For example,
it is very hard to know what a constraint with the name
CK__A__3E52440B
does without diving deeper into the details.
