Friendy Friendy - 6 months ago 12
SQL Question

Will "Where 0=1" parse full table or just return column names

Came across this question

SQL Server: Select Top 0?

I want to ask if I use query

SELECT * FROM table WHERE 0=1


or

SELECT TOP 0 * FROM table


will return just the column names instantly, or will it keep on parsing the whole table and in end return zero results?

I have a production table with 10000 rows, will it check
WHERE
condition on each row ?

Answer

The SQL Server query optimizer is smart enough to figure out that this WHERE condition can never ever produce a true result on any row, so it doesn't bother actually scanning the table.

If you look at the actual execution plan for such a query, it's easy to see that nothing is being done and the query returns immediately:

enter image description here