![](https://csdnimg.cn/release/download_crawler_static/10715856/bg12.jpg)
CHAPTER 1 ■ FOUNDATIONS OF T-SQL
8
reStrICtING the Data DOMaIN: a COMparISON
This section has given a brief overview of three methods of constraining the data domain for a column.
Each method restricts the values that can be contained in the column. Here’s a quick comparison of the
three methods:
Foreign key constraints allow SQL Server to perform an automatic check against •
another table to ensure that the values in a given column exist in the referenced table. If
the value you’re trying to update or insert in a table doesn’t exist in the referenced table,
an error is raised and any changes are rolled back. The foreign key constraint provides a
flexible means of altering the data domain, because adding values to or removing them
from the referenced table automatically changes the data domain for the referencing
table. Also, foreign key constraints offer an additional feature known as cascading
declarative referential integrity (DRI), which automatically updates or deletes rows from
a referencing table if an associated row is removed from the referenced table.
Check constraints provide a simple, efficient, and effective tool for ensuring that the •
values being inserted or updated in a column(s) are within a given range or a member
of a given set of values. Check constraints, however, aren’t as flexible as foreign key
constraints and triggers because the data domain is normally defined using hard-coded
constant values or logical expressions.
Triggers are stored procedures attached to insert, update, or delete events on a table or •
view. Triggers can be set on DML or DDL events. Both DML and DDL triggers provide a
flexible solution for constraining data, but they may require more maintenance than the
other options because they’re essentially a specialized form of stored procedure. Unless
they’re extremely well designed, triggers have the potential to be much less efficient
than other methods of constraining data. Generally triggers are avoided in modern
databases in favor of more efficient methods of constraining data. The exception to this
is when you’re trying to enforce a foreign key constraint across databases, because
SQL Server doesn’t support cross-database foreign key constraints.
Which method you use to constrain the data domain of your column(s) needs to be determined by your
project-specific requirements on a case-by-case basis.
Views
A view is like a virtual table—the data it exposes isn’t stored in the view object itself. Views are composed of
SQL queries that reference tables and other views, but they’re referenced just like tables in queries. Views
serve two major purposes in SQL Server: they can be used to hide the complexity of queries, and they can be
used as a security device to limit the rows and columns of a table that a user can query. Views are expanded,
meaning their logic is incorporated into the execution plan for queries when you use them in queries and
DML statements. SQL Server may not be able to use indexes on the base tables when the view is expanded,
resulting in less-than-optimal performance when querying views in some situations.
To overcome the query performance issues with views, SQL Server also has the ability to create a special
type of view known as an indexed view. An indexed view is a view that SQL Server persists to the database
like a table. When you create an indexed view, SQL Server allocates storage for it and allows you to query
it like any other table. There are, however, restrictions on inserting into, updating, and deleting from an