Question: Which is Optimal – TOP 0 OR WHERE 1 = 2?
Answer: They both are the same!
I had received this question the other day via social media group. This question is also very popular in various organizations as well. I have heard it quite many times at different interview as well. The sad part is that whenever I hear the answer to this question, it is usually wrong but the explanation is also very long. Let us see the answer here.
Let us run following two statements.
SET STATISTICS IO ON GO -- Test 1 SELECT TOP 0 * FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] GO -- Test 2 SELECT * FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] WHERE 1 = 2 GO
When you run both the queries it does not return any results as TOP 0 as well as WHERE 1 = 2 prevents query to return any results.
Additionally, the execution plan also shows that both the queries take exactly the same cost.
If you are wondering WHY both the queries are same here is the answer for you – If you spend more time looking at the execution plan you would realize that the query actually does the constant scan and does not even go to the actual table to read the data. The query optimizer is very smart that it does know that there is no result for this query, hence instead of going for the table to retrieve the data, it returned our data (well, actually no data) directly by doing a constant scan. You can also validate the same by going to a messages section which also shows you there is no access to the data.
Now here is my question back to you –
If I add the following query as an additional test to the original problem, what do you think which query will execute faster?
-- Test 3 SELECT * FROM [AdventureWorks2014].[Sales].[SalesOrderDetail] WHERE 1 <> 1 GO
Reference: Pinal Dave (https://blog.SQLAuthority.com)
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.