Which is Optimal – TOP 0 OR WHERE 1 = 2? – Interview Question of the Week #190

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.

Which is Optimal - TOP 0 OR WHERE 1 = 2? - Interview Question of the Week #190 same

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)

, ,
Previous Post
How to Move SQL Server MDF and LDF Files? – Interview Question of the Week #189
Next Post
Where is the Default Settings for Backup Compression? – Interview Question of the Week #191

Related Posts

16 Comments. Leave new

  • William E Houston
    September 10, 2018 7:17 am

    Hey, love your blog. I’m going with test 3 will be the same as the first two.

    Reply
  • Same as test1 and test2

    Reply
  • It’s disheartening to see such trick questions on interviews. I’d ask what is their purpose, but will always get a contrived answer.

    Reply
    • 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.

      Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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?

    Reply
  • Dmitriy Zubrilin
    September 19, 2018 10:19 am

    Interesting question…
    What about a bit more complex query with joins and etc.
    Wondering if SQL Server takes same time for query compilation?

    Reply
  • Condition where 1=2 will be done faster then top 0, because investigation of query inside optimizer start from predicate.

    Reply
  • 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.

    Reply

Leave a Reply

Menu