Database Reference
In-Depth Information
Figure 18-15.
Cost of a query with and without different
INDEX
hints
Using Domain and Referential Integrity
Domain and referential integrity help define and enforce valid values for a column, maintaining the integrity of the
database. This is done through column/table constraints.
Since data access is usually one of the most costly operations in a query execution, avoiding redundant data
access helps the optimizer reduce the query execution time. Domain and referential integrity help the SQL Server
2014 optimizer analyze valid data values without physically accessing the data, which reduces query time.
To understand how this happens, consider the following examples:
NOT NULL
constraint
•
The
•
Declarative referential integrity (DRI)
NOT NULL Constraint
The
NOT NULL
column constraint is used to implement domain integrity by defining the fact that a
NULL
value can't
be entered in a particular column. SQL Server automatically enforces this fact at runtime to maintain the domain
integrity for that column. Also, defining the
NOT NULL
column constraint helps the optimizer generate an efficient
processing strategy when the
ISNULL
function is used on that column in a query.