Question: Which is more efficient, TOP 0 or WHERE 1 = 2?
Answer: For the simple SELECT below, both ask SQL Server for an empty result. The optimizer can avoid reading the table’s data for either form, so there is no useful row-reading advantage to choose between them.
This question keeps appearing in interviews and on social media. People often give a long answer about which statement must be faster. The better answer is much shorter: look at the plan and the reads before choosing a winner.
USE AdventureWorks2025;
GO
SET STATISTICS IO ON;
SELECT TOP (0) * FROM HumanResources.Department;
SELECT * FROM HumanResources.Department WHERE 1 = 2;
SELECT * FROM HumanResources.Department WHERE 1 <> 1;
SET STATISTICS IO OFF;Turn on the actual execution plan in SSMS before running the batch.
A Constant Scan supplies the empty result without a Department table scan. Recognizing a Constant Scan elsewhere doesn’t automatically mean zero rows: that operator can produce constant rows in other queries. Check its actual output and context.
The statements still need valid syntax, object resolution and permissions. Returning zero rows doesn’t mean there is no compilation or execution overhead. This comparison also doesn’t claim TOP and WHERE are interchangeable in every more complex query.
My follow-up question is WHERE 1 <> 1. What do you expect? It’s another constant false condition, so the third statement above returns no rows as well. Try the three plans rather than arguing from how the words sound.
Want to try this yourself? Here is how to install AdventureWorks.
TOP 0 is not faster than WHERE 1 = 2, it is the same empty answer, and the plan proves it.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
16 Comments. Leave new
Hey, love your blog. I’m going with test 3 will be the same as the first two.
Thanks for all the love!
Same as test1 and test2
Great.
It’s disheartening to see such trick questions on interviews. I’d ask what is their purpose, but will always get a contrived answer.
Hi John,
Totally, honestly, I always wonder why people ask such questions but trust me, asking trick questions are nowadays norm and never help people’ to identify the true potential of the candidate.
Hello Pinal Sir, i have gone through this and have done one practical on one table and following is my result which shows that 1 = 2 is costly and going for table scan instead of constant scan.
100% constant scan on top 0 and 97% Index scan cost on 1 = 2.
Very interesting Himanshu,
Would you please send me execution plan at pinal at sqlauthority.com ? This will be interesting to understand.
Hi Himanshu,
Are you using variable instead of constant value in the query?
Thanks,
Srini
Please share with us also.
I would like to know.
I’m going to guess that the optimizer recognizes “where 1=2” as a special case, but does not recognize “where 11” and therefore has to go through a full table scan rejecting every row.
Just curious…Why do the “Select” statements show different tables? In the sql statements, they hit [AdventureWorks2014].[Sales].[SalesOrderDetail] and the plan states HumanResources.Department?
Good observation. There is no technical reason behind this. I was testing with different queries and took screenshot from two monitors having different query on each.
Interesting question…
What about a bit more complex query with joins and etc.
Wondering if SQL Server takes same time for query compilation?
Condition where 1=2 will be done faster then top 0, because investigation of query inside optimizer start from predicate.
If both have the same execution plan, I’d opt for the top 0 approach. As it’s more noticeable, if I am writing a lengthy query, it will be easier to maintain.