SA0154 : Constraint is not trusted

In SQL Server, constraints play a vital role in maintaining data integrity and assisting the query optimizer in improving performance. However, if constraints become not trusted, the optimizer cannot rely on them, potentially resulting in less efficient query execution plans.

For example:

1-- Query creating an untrusted constraint
2ALTER TABLE SalesOrder
3      NOCHECK CONSTRAINT FK_CustomerID;
4
5-- Re-enabling the constraint without checking the existing data
6ALTER TABLE SalesOrder
7      CHECK CONSTRAINT FK_CustomerID;

In this example, re-enabling a constraint without specifying WITH CHECK leaves it untrusted. This means SQL Server will ignore the constraint during query optimization, which might lead to inefficient query plans.

• Untrusted constraints are not considered by the SQL Server query optimizer, potentially leading to suboptimal execution plans and slower performance.

• Neglecting to ensure constraints are trusted can result in inconsistencies, as the assumption of data integrity is compromised.

`

Ensure that constraints are trusted to optimize query performance in SQL Server by re-enabling them with data validation.

Follow these steps to address the issue:

1. Identify the constraints that are currently untrusted using a query to check constraints status:

2. Use the ALTER TABLE syntax to re-enable each untrusted constraint with validation. Replace table_name and constraint_name with the appropriate values:

For example:

1-- Example query to find untrusted constraints
2SELECT name AS ConstraintName, 
3       OBJECT_NAME(parent_object_id) AS TableName
4FROM sys.check_constraints
5WHERE is_not_trusted = 1;
6
7-- Correct re-enabling of a constraint
8ALTER TABLE SalesOrder 
9    WITH CHECK CHECK CONSTRAINT FK_CustomerID;

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Performance Rules, Bugs

Can you trust your constraints?

Other Resources