Enforcing Data Integrity

Data integrity can be enforced using declarative or procedural methods. Declarative methods are generally simpler and more efficient, whereas procedural methods can enforce more complex rules.

Declarative Data Integrity

Declarative integrity is enforced within the database using constraints, rules, and defaults. This is the preferred method of enforcing integrity as it has low overhead and is programmatically simple. It is also more centrally managed and consistently enforced because it resides in the database.

Procedural Data Integrity

Procedural integrity is enforced with stored procedures, triggers, and application code. Procedural integrity generally has a higher overhead and can be quite complex, but it can enforce far more extensive business rules than declarative integrity. If implemented on the application side, procedural integrity can reduce load on the database, but it can allow discrepancies to creep into the data if data is loaded or modified with tools other than the application. For this reason, procedural integrity should be implemented to complement, but not replace, declarative integrity.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features