SQL Server keeps a record to identify if a check constraint is trustable or not. When all the records in the database where checked against the constraint it’s trustable, otherwise it’s not.
If the check constraint is trustable, it can be used by the query optimizer. For example, if the check constraint avoid values below 100 in a field and a query for 50 arrives, the query optimizer uses the check constraint to stop the query.
The query optimizer can only use the check constraint if it’s trustable, otherwise it could exist in the table records with values below 100, according to our example, and the query would loose these records.
This means that if the check constraint is marked as untrustable, even if a query arrives asking for a value that shouldn’t exist in the table it will generate readings that could be avoided.
But how this happens ? How the check constraint become untrustable ? How to solve this problem ?
Let’s see an example. First we will create a small table with a constraint and insert some records.
|
1 2 3 4 5 6 7 8 9 10 11 |
create table TestTable (id int identity(1,1), Salary money, constraint chkSalary check (salary > 1000) ) go Insert into TestTable values (2000), (1500), (3000) go |
Check the execution plan of the following query:
|
1 |
select * from TestTable where Salary < 800 option(recompile) |
We have only the ‘Constant Scan’ operator in the query plan. The ‘Constant Scan’ operator is responsible for include constant rows in the result. In this case, with only this operator in the query plan, this means the query optimizer decided to not run the query because there will be no record returned.
The ‘sys.check_constraints’ object catalog view has a field named ‘is_not_trusted’ that we need to check to identify if the constraint is trusted or not. We can use the following query:
|
1 2 |
select name,object_name(parent_object_id) tabela,is_not_trusted from sys.check_constraints |
At this point the result is zero, the constraint is trustable.
Sometimes we need to import data in the table. For this kind of operation, when we already know that the data is valid and we would like to have better performance, we disable the constraints.
|
1 2 3 4 5 6 7 8 |
-- Let's disable the constraints to import a bunch of records alter table TestTable nocheck constraint all go -- Insert some information insert into testtable values (4500) go -- enable the constraints again alter table TestTable check constraint all go |
Now the constraint isn’t trustable any more. You can execute again the query to check and the ‘is_not_trusted’ field will be 1. The value of this field changed after the constraints were disabled and didn’t returned to 0 even after we re-enabled the constraints.
This happens because SQL Server can’t be sure that all the records inserted while the constraint was disabled were valid. If we check the execution plan of the query over the table now, it will read the table pages, becaust the constraint isn’t trustable any more.
The solution is to ensure that all the records were checked against the constraint. We can do this when we re-enable the constraint, asking SQL Server to check the constraint again. The t-sql instruction will be this one:
|
1 |
alter table TestTable with check check constraint all go |
Using the ‘with check’ in the t-sql the constraint is checked against the records and become trustable again.
Load comments