ARTICLES

Microsoft SQL Server 2005 introduces many new useful stored procedures, functions and operators.
Some actions you can perform without these new features, but usually using them provides more
easy and effective way to accomplish the same goal. For example, Microsoft introduces new cascade
integrity constraints in SQL Server 2005. Microsoft introduces cascading referential integrity
constraints in SQL Server 2000, but in SQL Server 2005 two new cascading referential integrity
constraint actions were added. There are SET NULL and SET DEFAULT.

In this article, you can find some tips to use constraints in SQL Server 2005.

Consider using ON DELETE SET NULL action in the REFERENCES clauses of the
CREATE TABLE and ALTER TABLE statements.
The ON DELETE SET NULL action is a new feature of SQL Server 2005. If this action is used than
when you delete a row that referenced by foreign keys all foreign key columns of the target table
in the rows that contain those foreign keys will be set to NULL.
Note. To execute this constraint, you should ensure that all foreign key columns of the target
table are nullable.

Consider using ON DELETE SET DEFAULT action in the REFERENCES clauses of the
CREATE TABLE and ALTER TABLE statements.
The ON DELETE SET DEFAULT action is a new feature of SQL Server 2005. If this action is used than
when you delete a row that referenced by foreign keys all foreign key columns of the target table
in the rows that contain those foreign keys will be set to their default value.
Note. To execute this constraint, you should ensure that all foreign key columns of the target
table have the default values.

Try to use CHECK constraints instead of triggers whenever possible.
Constraints are much more efficient than triggers and can boost performance. Constraints are
also more consistent and reliable in comparison with triggers, because you can make errors when
you write your own code to perform the same actions as the constraints. So, you should use
constraints instead of triggers whenever possible.

Try to avoid using CHECK_CONSTRAINTS hint with bulk copy program.
Using this hint can significantly degrade performance of the bulk copy operation, because for each
row loaded the CHECK constraints defined on the destination table will be executed. Without the
CHECK_CONSTRAINTS hint, any CHECK constraints will be ignored.
Note. The UNIQUE, PRIMARY KEY, FOREIGN KEY and NOT NULL constraints are always enforced.

Use CHECK constraints instead of rules.
Rules are provided for backward compatibility and have been replaced by CHECK constraints.
Constraints are much more efficient than rules and can boost performance. Rules have some
restrictions. For example, only one rule can be applied to a column, but multiple CHECK
constraints can be applied. Rules are created as separate objects and then bound to the column,
while CHECK constraints are specified as part of the CREATE TABLE statement.

Consider creating column-level constraints instead of the table-level constraints.
For example, you can create CHECK constraint, which is attached to a single column, or you can
create CHECK constraint, which is attached to the entire table. Because a column-level constraint
is more efficient than a table-level constraint, you should create column-level constraints
whenever possible.

Consider creating an index for every foreign key constraint.
This can greatly maximize the overall performance of the queries against the indexed data.

Try to create a single column constraint.
The more columns the constraint will have, the slowly it will work and the more stored space
it will require.

Consider disabling a CHECK constraint during replication if the constraint is
specific to the source database.
During replication the table data are copied from the source database to a destination database.
If the CHECK constraints specific to the source database, they can prevent new data from being
copied in the destination database.

Consider creating a surrogate integer primary key.
Every table should have a primary key (a unique identifier for a row within a database table).
A surrogate primary key is a field that has a unique value but has no actual meaning to the
record itself, so users should never see or change a surrogate primary key. Some developers use
surrogate primary keys, others use data fields themselves as a primary key. If a primary key
consists of many columns and has a big size, consider creating a surrogate integer primary key.
This can improve performance of your queries.