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.
 
Search WWH ::




Custom Search