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)