Trusting Constraints

Dennes Torres talks about whether a constraint is trustworthy:

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.

Dennes then goes on to show how you can have non-trustworthy constraints and how to fix the issue.

Related Posts

The Importance of Aliasing in Subqueries

Gail Shaw explains an unexpected result when writing a statement with a subquery: The column name in the temp table is missing an I, probably just a typo, but it has some rather pronounced effects. The obvious next question is why the select with the subquery in it didn’t fail, after all, the query asks […]

Read More

SARGability and Date Functions

Erik Darling shows why you don’t want to use YEAR() or MONTH() in the WHERE clause when querying a large table: If you’ve been query tuning for a while, you probably know about SARGability, and that wrapping columns in functions is generally a bad idea. But just like there are slightly different rules for CAST and […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031