SQL Server’s Optimizer Could Be Smarter About Constraints

Couldn’t We All

---

Let me ask you a question: If I told you that all the numbers in an integer column were either:

• > 0
• >= 1

You’d probably agree that the lower number you could possible see is 1.

And that’s exactly the case with the Reputation column in Stack Overflow.

Non-Alignment Pact

---

Assume that I am being truthful about creating this index:

CREATE INDEX constraints_are_silly
ON dbo.Users
(
Reputation,
UpVotes
) INCLUDE (DisplayName);

Also assume that this is the most important query ever written by human hands for the benefit of humanity:

SELECT TOP (1000)
u.Reputation,
u.UpVotes,
u.DisplayName
FROM dbo.Users AS u
WHERE u.Reputation <= 1
ORDER BY u.UpVotes;

However, I’m dissatisfied with the query plan. This requires no assumption. It’s a bit much for what I’m asking, I think.

Since a current implementation rule for the database is that no one can have a Reputation of 0 or less, I add this constraint hoping that SQL Server will see this and stop sorting data, because it knows that 1 is the lowest integer it will find, and the order of UpVotes won’t reset for Reputation = 0.

ALTER TABLE dbo.Users ADD CONSTRAINT checko CHECK (Reputation > 0);

But I still end up with the same execution plan. In neither case is the plan a) trivial, or b) simple parameterized. We can’t blame the optimizer trying to be helpful.

Now assume that I get really mad and change my constraint. This requires minimal assumption.

ALTER TABLE dbo.Users ADD CONSTRAINT checko CHECK (Reputation >= 1);

And now I get a query plan that does not have a sort in it. My approval does not require assumption.

Why does one constraint remove the need to sort, and one not?

Over My Head

---

The answer is in the query plan. Sometimes I have to be reminded to look at these.

• The Seek Predicate on the left is from when we defined the constraint as > 0. It has a <= 1 predicate.
• The Seek Predicate on the right is an equality on = 1

For a little more detail, I asked a question. Apparently the optimizer… doesn’t consider data types here.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.