Tips for using constraints in SQL Server 2016
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.
Use default constraints instead of DEFAULT objects.
DEFAULT object are provided for backward compatibility and has been replaced by default
definitions (default constraints) created using the DEFAULT keyword of ALTER or
CREATE TABLEstatements. Default constrains are preferred because they stored with the
table and automatically dropped when the table is dropped.
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 cascading referential integrity constraints instead of triggers whenever possible.
For example, if you need to make cascading deletes or updates, specify the ON DELETE
or ON UPDATE clause in the REFERENCES clause of the CREATE TABLE or ALTER TABLE
statement. The cascading referential integrity constraints are much more efficient than triggers
and can boost performance.
Consider using DROP CONSTRAINT IF EXISTS statement if you need to drop the constraint.
SQL Server 2016 introduces a new DROP IF EXISTS statement, that you can use to drop the
constraint. For example, now you can use the following statement to drop the ConstraintName
constraint:
DROP CONSTRAINT IF EXISTS ConstraintName
instead of:
IF (OBJECT_ID(‘ConstraintName’) IS NOT NULL)
BEGIN
ALTER TABLE [dbo].[TableName]
DROP CONSTRAINT ConstraintName
END
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.
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.
Consider creating an index for every foreign key constraint.
This can greatly maximize the overall performance of the queries against the indexed data.
When you create primary key or unique key constraints, specify the CLUSTERED
or NONCLUSTERED keyword.
By default, when you create a primary key constraint, SQL Server creates clustered index,
and when you create a unique key constraint, SQL Server creates nonclustered index.
A clustered index is a special type of index that reorders the way records in the table
are physically stored. Therefore table can have only one clustered index. A nonclustered
index is a special type of index in which the logical order of the index does not match
the physical stored order of the rows on disk. You should create clustered index on
column(s) that is not updated very frequently. If the primary key column(s) updated
frequently, consider specifying NONCLUSTERED keyword for the primary key and CLUSTERED
for one of the unique key constraints.
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.
Create NONCLUSTERED primary key for identity column.
If a table has surrogate integer primary key declared as IDENTITY and the clustered index
was created on this column, then every time data is inserted into this table, the rows will
be added to the end of the table. When many rows will be added a "hot spot" can occur.
A "hot spot" occurs when many queries try to read or write data in the same area at the same
time. A "hot spot" results in I/O bottleneck. Because, by default, SQL Server creates
clustered index for a primary key constraint, you should explicitly specify NONCLUSTERED
keyword to indicate that a nonclustered index will be created for a primary key constraint.
Consider using constraints on memory-optimized tables.
SQL Server 2016 supports using constraints on the memory-optimized tables also.
Note. In previous SQL Server versions you cannot use the CHECK constraint on
memory-optimized tables, now you can.
When you create primary key or unique key constraints, specify a FILLFACTOR keyword.
The fill factor option specifies how full SQL Server should make each index page. When there
is no free space to insert new row on the index page, SQL Server will create new index page
and transfer some rows from the previous page to the new one. This operation is called page
splits. You can reduce the number of page splits by setting the appropriate fill factor value
to reserve free space on each page of the index. The fill factor is a value from 1
through 100 that specifies the percentage of the index page to be left empty.
Consider using the NOT FOR REPLICATION clause of the FOREIGN KEY and CHECK
constraints.
You should use the NOT FOR REPLICATION option when you need the constraints is not
enforced when replication agents perform insert, update, or delete operations. For example,
if you specify this option than the constraint will be checked on the Publisher only, so the
replication synchronization can be performed faster.