Untrusted Check Constraints

What this check looks for

Check constraints across every database where is_not_trusted is 1 in sys.check_constraints. Constraints created NOT FOR REPLICATION are excluded, because they are permanently untrusted by design.

Why it matters

This is the sibling of the untrusted foreign key finding, and the one nobody looks for.

A check constraint that is not trusted is still enforced on every write, so no bad data gets in. What it has stopped doing is informing the optimizer, and for check constraints the thing that buys is predicate elimination.

Here is the mechanism. Suppose an archive table carries CHECK (OrderDate < '2020-01-01') and the current table carries CHECK (OrderDate >= '2020-01-01'), with a view over both. A query asks for orders in March 2024. With trusted constraints, the optimizer notices that the archive table’s constraint contradicts the query’s predicate: no row in that table can possibly match. It removes the archive table from the plan without reading a page of it.

Untrusted, it cannot prove that. So it reads the archive table, applies the predicate, and returns nothing, having done all the work to discover what the constraint already stated.

That is what makes the archive table and partitioned view patterns cheap, and it is the entire performance argument for them. A partitioned view over ten yearly tables with trusted constraints touches one table for a single year query. Untrusted, it touches all ten.

The same elimination applies more narrowly elsewhere:

• A constraint such as CHECK (Status IN ('A','P','C')) lets the optimizer discard a query asking for Status = 'X' immediately.
• CHECK (Amount > 0) lets it discard WHERE Amount < 0.
• A NOT NULL-equivalent check helps the optimizer reason about nullability in joins and aggregates.

Those are smaller wins than join elimination, which is why this finding is lower severity than its foreign key sibling. The partitioned view case is the one where it is dramatic.

What untrusts a check constraint is exactly the workflow archive tables are loaded by:

• A bulk load that omits CHECK_CONSTRAINTS, which is the default for BULK INSERT and bcp.
• Most restore and merge operations.
• Adding a constraint WITH NOCHECK to avoid the scan on a large table.
• Re-enabling one WITH NOCHECK after a load.

So the archive tables that benefit most from trusted constraints are the tables most likely to have untrusted ones, which is the unfortunate symmetry at the center of this finding.

How to confirm it yourself

Every untrusted check constraint:

SELECT DB_NAME()                          AS [database_name],
       OBJECT_SCHEMA_NAME(cc.[parent_object_id]) AS [schema_name],
       OBJECT_NAME(cc.[parent_object_id])        AS [table_name],
       cc.[name]                          AS [constraint_name],
       cc.[definition],
       cc.[is_disabled],
       cc.[is_not_trusted]
  FROM sys.check_constraints AS cc WITH (NOLOCK)
 WHERE cc.[is_not_trusted] = 1
   AND cc.[is_not_for_replication] = 0
 ORDER BY [table_name], cc.[name];

Read the definition column. A constraint on a date range or a status value is one that can drive predicate elimination; a constraint on a length or a format is not, and re-checking it buys nothing beyond tidiness.

Across every database:

EXEC sp_MSforeachdb N'
USE [?];
IF DB_ID() > 4
SELECT DB_NAME() AS [database_name],
       OBJECT_NAME(cc.[parent_object_id]) AS [table_name],
       cc.[name]                          AS [constraint_name],
       cc.[definition]
  FROM sys.check_constraints AS cc WITH (NOLOCK)
 WHERE cc.[is_not_trusted] = 1 AND cc.[is_not_for_replication] = 0;';

What the re-check will cost, which sets the order of work:

SELECT OBJECT_SCHEMA_NAME(cc.[parent_object_id]) AS [schema_name],
       OBJECT_NAME(cc.[parent_object_id])        AS [table_name],
       cc.[name]                                 AS [constraint_name],
       SUM(p.[rows])                             AS [table_rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [table_mb]
  FROM sys.check_constraints   AS cc WITH (NOLOCK)
 INNER JOIN sys.partitions     AS p  WITH (NOLOCK) ON p.[object_id] = cc.[parent_object_id]
                                                  AND p.[index_id] IN (0, 1)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE cc.[is_not_trusted] = 1 AND cc.[is_not_for_replication] = 0
 GROUP BY cc.[parent_object_id], cc.[name]
 ORDER BY [table_rows];

And whether any row already violates it, which a re-check would surface the hard way:

-- for CHECK (OrderDate < '2020-01-01')
SELECT COUNT(*) AS [violations]
  FROM [dbo].[OrdersArchive]
 WHERE NOT ([OrderDate] < '2020-01-01');

The convincing demonstration is to look at a partitioned view’s plan before and after. Query one year’s worth of data through the view, and count how many tables appear in the plan. With trusted constraints it should be one.

How to fix it

One ALTER TABLE ... WITH CHECK CHECK CONSTRAINT per constraint, cheapest table first.

ALTER TABLE [dbo].[OrdersArchive]
  WITH CHECK CHECK CONSTRAINT [CK_OrdersArchive_OrderDate];

The doubled CHECK CHECK is correct. The first is WITH CHECK, meaning verify the existing rows; the second is CHECK CONSTRAINT, meaning enable it.

Generate them in ascending table size:

SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(cc.[parent_object_id])) + '.'
       + QUOTENAME(OBJECT_NAME(cc.[parent_object_id]))
       + ' WITH CHECK CHECK CONSTRAINT ' + QUOTENAME(cc.[name]) + ';' AS [statement],
       SUM(p.[rows]) AS [table_rows]
  FROM sys.check_constraints AS cc WITH (NOLOCK)
 INNER JOIN sys.partitions   AS p  WITH (NOLOCK) ON p.[object_id] = cc.[parent_object_id]
                                                AND p.[index_id] IN (0, 1)
 WHERE cc.[is_not_trusted] = 1 AND cc.[is_not_for_replication] = 0
 GROUP BY cc.[parent_object_id], cc.[name]
 ORDER BY [table_rows];

Worst table last, so the cheap ones are done before anything blocks for long. The re-check scans the table under a schema modification lock, so it blocks every reader and writer on that table for the duration. On anything large it wants a maintenance window.

All constraints on one table at once, if that suits the window better:

ALTER TABLE [dbo].[OrdersArchive] WITH CHECK CHECK CONSTRAINT ALL;

If a re-check fails, the data violates the constraint and that is the more important finding. Fix the data, then re-check. On an archive table this usually means rows that landed in the wrong partition table, which is worth knowing about for its own sake.

Then fix what untrusted them. A bulk load without CHECK_CONSTRAINTS will do it again on the next run:

BULK INSERT [dbo].[OrdersArchive]
  FROM 'D:\Loads\orders_2019.csv'
  WITH (CHECK_CONSTRAINTS, TABLOCK, FORMAT = 'CSV');

For bcp, the equivalent is -h "CHECK_CONSTRAINTS".

A scheduled load is worth fixing once rather than re-checking every week. That is the durable outcome, and without it this finding returns after every load.

And while you are in the archive tables, confirm the partitioned view pattern is actually giving you elimination now. Run the year query and count the tables in the plan. If it is still reading all of them, check that the constraints are mutually exclusive and cover the full range, because a gap between two ranges also prevents elimination.

How long it takes

About half an hour for most databases. A very large archive table’s re-check needs a maintenance window of its own.

Related reports

Related checks

Frequently asked questions

Is bad data getting in? No. The constraint is still enforced on every insert and update. It has only stopped being usable as evidence by the query optimizer.

What is predicate elimination worth? On a partitioned view over ten yearly tables, a single year query touches one table instead of ten. Elsewhere it is a smaller win, and on a constraint about formats or lengths it is effectively nothing.

Which constraints are worth re-checking first? Ones on date ranges and status values, which can drive elimination. Read the definition column; a check on a string length is tidiness rather than performance.

My partitioned view still reads every table after re-checking. Confirm the constraints are mutually exclusive and cover the full range with no gaps. A gap between two ranges prevents the optimizer from proving exclusivity.