Back in my university days, a lecturer of mine would say how programmers are experts in “creative laziness” He was referring to practices such as using methods and classes in C# – constructs that mean as a developer, you follow the DRY (Don’t Repeat Yourself) ethos – you are saving yourself time by writing things once and therefore if you need to change them, you only change things once.
I often think back to this phrase when looking at some of the things SQL Server’s query optimizer does – it has some pretty cool features that make it “work smart, not hard” one example of which I will share here.
Take the following query against the StackOverflow2010 database provided under cc-by-sa 4.0 license from Stack Exchange Data Dump and running in compatibility level 130 on a SQL Server 2022 instance installed on a VM with 8 cores and 35GB RAM.
SET STATISTICS IO, TIME ON
SELECT Id,
Title,
Score
FROM dbo.Posts
WHERE 1=2;
The query returns 0 rows because logically there are no rows in the Posts table where the predicate 1=2 is true due to 1=2 being a logical contradiction. SQL Server is smart and knows this too, therefore when it sees 1=2 or some other logical impossibility, it will apply some creative laziness and work smart, not hard. We can see this in the output of SET STATISTICS IO, TIME ON for the query:
(0 rows affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
It took 0 milliseconds to execute and performed 0 logical reads (it didn’t print out any read information).
If we look in the execution plan, we can see why:
It performs a “Constant Scan” which in this case means it just drew up a blank result set with the desired headings and spat it out. There are no other operators; it didn’t access the data of any table. This behaviour comes from a part of the query optimization process called Contradiction Detection.
We can do something similar in a larger query:
SELECT p.Id,
p.Title,
p.Score,
u.DisplayName,
v.BountyAmount
FROM dbo.Posts p
JOIN dbo.Users u
ON p.OwnerUserId = u.Id
JOIN dbo.Votes v
ON 1=2
WHERE p.AnswerCount > 0 AND
u.DisplayName LIKE 'John%' AND
v.BountyAmount > 0;
Note that I’ve put the 1=2 in an inner join in this example and the result is once again the same:
Why is this useful?
You could argue it isn’t useful — after all, who would deliberately write code containing a logical inconsistency? Below are some scenarios where I think it is relevant, a few of which I have encountered in real life:
- It is possible, though probably unlikely, that a query may be written mistakenly with such a logical inconsistency – we’re all human after all (except ChatGPT, but I won’t go there)
- You may have a reporting application that exposes some SQL queries to a user and allows the user to amend the logic via a front end by means of editing a WHERE clause and maybe you want to “turn that query off” but the system doesn’t let you.
- Feature flagging – Similar to the above, adding a WHERE 1=2 to a view definition will “turn it off” which may be useful when
- Deprecating old objects
- When a view begins performing poorly due to a data change – perhaps lots of data has been dumped into the underlying tables and the view no longer has an optimal plan but the users just keep running it and burning the server down. This way, you can “turn it off”
- Developers sometimes use WHERE 1=2 deliberately to grab an empty result set with the correct schema — for example to create a temp table structure without any data. Knowing SQL Server handles this at zero cost is a useful reassurance.
Some of the scenarios above are real edge cases and forcing in a logical inconsistency is a sure path to technical debt but as with many things – it’s another tool in the box that can be dangerous if not used with care.
This example serves as a reminder to me that the SQL Server optimizer is clever – it doesn’t always do what you think – it is designed to minimize the work required to perform a query, so make sure you check plans and verify behaviour.
Conclusion
SQL Server’s contradiction detection is a small but telling window into how the query optimizer thinks. It isn’t just executing your SQL – it is reasoning about it first, and if it can prove upfront that a result is impossible, it won’t waste any resources trying to return the results. That is creative laziness at its finest
References / Further Reading
Conor Cunningham – Contradictions Within Contradictions
Erik Darling – Decrypting Insert Query Plans