(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In today’s blog posting I want to talk about a quite simple question in SQL Server: is a SELECT statement always executed? The question itself seems to be quite easy, but the answer to it isn’t that simple. Let’s look at the following example:

SELECT * FROM Person.Person

As you can see this is a simple SELECT statement, and of course SQL Server has to execute it as you can see from the following Execution Plan:

Let’s rewrite this query now a little bit:

SELECT * FROM Person.Person
WHERE 1 = 2

Let’s concentrate now in the first step on this query: We are using here now a Filter Predicate with 1 = 2. How many records are you expecting from this query? You are expecting of course no records, because the integer value 1 never ever will be equal to the integer value 2. This is just a simple contradiction.

But how is SQL Server handling this contradiction? Does it access the table Person.Person physically and afterwards filter out all returned rows? That would be possible but it would be quite inefficient. Image your table returns billions of rows. So let’s execute now the query from above, and let’s have a look again at the Execution Plan:

As you can see now, the Execution Plan only consists of a Constant Scan operator. The underlying table Person.Person is never, ever physically accessed during Query Execution. Therefore you have no I/O, Locking, and Latching involved on the table itself. The Constant Scan operator only returns you the metadata of your result set from the system tables. More or less your query was not really executed anymore.

This is a feature in SQL Server and is called Contradiction Detection. This approach can be quite useful if you want to know how the schema of a table looks like, just restrict on 1 = 2, and SQL Server will not really access your table anymore. Or imagine you want to create an empty copy of a table. You could do the following:

SELECT * INTO temp FROM Person.Person
WHERE 1 = 2

The same thing applies when you are working with Constraints in SQL Server. Let’s have a look at the following table definition.

CREATE TABLE CheckConstraint
(
    Value INT NOT NULL DEFAULT 1 CONSTRAINT ck_Value CHECK (Value = 1)
)
GO

As you can see here, the column Value can’t store physically a different value than 1. It is just prohibited through the defined Check Constraint. And now imagine you are running the following query against that table.

SELECT * FROM CheckConstraint
WHERE Value IN (0, 0)

When you look again at the Execution Plan, you can see that SQL Server is just using the Constant Scan operator again. It’s again a contradiction that you have written in your query, and SQL Server was able to detect it (as long as the query is not parametrized).

A few days ago, Klaus Ondrich has tweeted the following interesting query on Twitter:

Guess what happens if you execute:
SELECT * FROM dbo.anytable
WHERE EXISTS (SELECT 1/0);
Surprised? #sqlserver

— Klaus Ondrich (@kondrich) August 14, 2018

Do you think that this query triggers a Divide by Zero Exception? Not really, because the used Subquery is never ever executed. It doesn’t really make sense to execute it. And therefore you are not getting a Divide by Zero Exception!

Summary

As you have seem from this blog posting, in some cases SQL Server is able to detect contradictions in your queries, and just doesn’t execute your queries anymore. This concept is called Contradiction Detection and can be quite useful in some scenarios.

Thanks for your time,

-Klaus