locked
exists(select 1 from) vs exists(select top 1 1 from) RRS feed

  • Question

  • Logically it seems that if exists(select top 1 1 from) should be efficient than if exists(select 1 from). but IO stats shows that both have same number of reads. Also some blogs have mentioned that there is no difference. Is there really no difference?

    Wednesday, July 8, 2020 5:02 AM

Answers

  • Logically it seems that if exists(select top 1 1 from) should be efficient than if exists(select 1 from). but IO stats shows that both have same number of reads. Also some blogs have mentioned that there is no difference. Is there really no difference?

    There is no difference. When it comes to the EXISTS, you can consider the SELECT clause to be noise which is there to make the syntax nice, but it serves absolutely no practical purpose.

    As for Olaf's post, there is apparently a "not" missing there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Wednesday, July 8, 2020 8:13 AM
    Wednesday, July 8, 2020 7:51 AM

All replies

  • It is the same statement  and  SQL Server interprets  TOP 1 1 exactly as TOP 1... No differnce and you proved it with your test  

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, July 8, 2020 6:24 AM
    Answerer
  • Logically it seems that if exists(select top 1 1 from) should be efficient than if exists(select 1 from)

    The database engine is smart enough to optimize this; I would expect a difference.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 8, 2020 6:39 AM
  • hi Uri, the satement is if exists(select top(1) 1 from ) and if exists(select 1 from). its not top 11 and top 1.

    Olaf, you mean  exists(select top(1) 1 from ) would be faster?

    Wednesday, July 8, 2020 6:59 AM
  • Logically it seems that if exists(select top 1 1 from) should be efficient than if exists(select 1 from). but IO stats shows that both have same number of reads. Also some blogs have mentioned that there is no difference. Is there really no difference?

    There is no difference. When it comes to the EXISTS, you can consider the SELECT clause to be noise which is there to make the syntax nice, but it serves absolutely no practical purpose.

    As for Olaf's post, there is apparently a "not" missing there.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Curendra Wednesday, July 8, 2020 8:13 AM
    Wednesday, July 8, 2020 7:51 AM

  • As for Olaf's post, there is apparently a "not" missing there.

    OMG, it's contagious! ;-)


    Adding that one can compare the execution plans to determine if there's actually a difference in the way queries are actually executed. Ideally, semantically identical queries will yield the same (optimal) execution plan.



    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Wednesday, July 8, 2020 11:42 AM