Why does SQL evaluate statements in the true section of the if exists construct, even if `if exists` returns false?

Question

(I apologize in advance for the terrible explanation, but if you run the queries below, you'll see what I mean!)

Why does MSSQL evaluate statements in the true section of the if exists construct, even if if exists returns false, causing errors?

For example, in the two queries below, the first checks if the table exists (what it does), and also checks if there are certain columns in this table. For some reason, running this query causes the following errors because the table exists but the columns do not.

 Msg 207, Level 16, State 1, Line 21 Invalid column name 'colB'. Msg 207, Level 16, State 1, Line 21 Invalid column name 'colC'. Msg 207, Level 16, State 1, Line 21 Invalid column name 'colA'. 

The behavior I was expecting here was that SQL simply moved to the falsepart construct without causing errors. (As with the next request).

However, the second script (which is identical, the names of the table tables) is successful. This is because the table the query is looking for does not exist.

 --Scripts to setup the example. CREATE DATABASE TEST GO USE TEST GO CREATE TABLE t1 (colD VARCHAR(255)) --Create a table with the correct name, but incorrect column names. GO --This query fails, because t1 exists, even though the columns in t1 don't. IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' AND COLUMN_NAME IN ('colA','colB','colC')) BEGIN SELECT colA FROM t1 WHERE colB = 0 AND colC = 1 END ELSE BEGIN SELECT 'FALSE' END GO --This query executes ok, because t2 does not exist. IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't2' AND COLUMN_NAME IN ('colA','colB','colC')) BEGIN SELECT colA FROM t2 WHERE colB = 0 AND colC = 1 END ELSE BEGIN SELECT 'FALSE' END 

Can someone explain to me why the first request errors when the second request is working fine?

So far, I have only been able to verify this in Microsoft SQL Server 2012.

Answer 1

To answer the first part of this question. Assuming familiarity with a language (like C #) that has some kind of runtime type check (like Reflection).

Suppose you have a code like this:

 SomeType t = GetSomeTypeFromSomewhere(); if(t.GetType().GetMethod("FunTimes")!=null) { t.FunTimes(); } 

And suppose SomeType does not contain a public method called FunTimes . Although I wrote a defender trying to call the FunTimes method, I get an error. And, in particular, I get a compile-time error - the C # compiler cannot even generate code, let alone get closer to running the code, getting the result from GetMethod() and deciding not to run the code inside the nested block.

To get back to your code, the same type of analysis applies here:

 IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't1' AND COLUMN_NAME IN ('colA','colB','colC')) BEGIN SELECT colA FROM t1 WHERE colB = 0 AND colC = 1 END ELSE BEGIN SELECT 'FALSE' END 

SQL Server tries to compile this batch and fails. It never executes code, so it cannot decide which branch ( IF or ELSE ) to accept.

So, if all of the above is true, why then does the second part of the code work? This is due to a feature of T-SQL called Pending Name Resolution . Basically, there is a special rule that applies when the missing object is a table (or view, since they are indistinguishable until the object is found). In this particular case, SQL Server will not immediately signal a compilation error.

According to the resolution of the deferred name, execution is started and, if something causes a change in the scheme (for example, by adding the missing table / view), this forces the system to recompile the rest of the code.

Answer 2

I think that you are not evaluating the results correctly (And this is not your IMHO mistake).

The EXISTS part returns FALSE in both cases. However, the SQL query parser is funny, it parses internal expressions and gives an error before executing statements, only if the column is missing, it does not give an error if the table is missing.

In your first query, where it seems to evaluate TRUE, try changing the table name to something like t2, and you will see that it works and evaluates FALSE in both.