Difference between count and exists

  • Hi,

    I have to check whether I have a particular value in a table. eg I want to check whether a particular employee Id exists or not.

    What I usually do is check in the following way,

    declare @count1 int

    select @count1=count(*) FROM table1 WHERE id='TEST'

    print @count1

    if the count is greater than 0 means such a value exists.

    Now I have also found another way of doing this

    IF EXISTS (SELECT *FROM table1 WHERE id=@id)

    BEGIN

    RETURN 1

    END

    ELSE

    BEGIN

    RETURN 0

    END

    Now pls let me know which is more efficient and why is it so?

    Regards

    cmrhema

  • Hello,

    May be take a look at the following article:-

    http://milambda.blogspot.com/2006/10/exists-or-not-exists-that-is-question.html

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • -

    www.sql.lu
    SQL Server Luxembourg User Group

  • http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If you are asked 'Is anyone in the office wearing a red shirt' what do you do?

    a) Go round the entire office counting the number of people and making a note of what colour shirt each one is wearing. Then add up all the counts and if the number wearing red shirts is greater than 1 shout out YES!!!

    b) Take a quick look round the office and as soon as you see that someone in a red shirt exists, shout out YES!!!

    Both will give you the right answer, but option b) will get you there sooner.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • EdVassie (6/8/2009)


    If you are asked 'Is anyone in the office wearing a red shirt' what do you do?

    a) Go round the entire office counting the number of people and making a note of what colour shirt each one is wearing. Then add up all the counts and if the number wearing red shirts is greater than 1 shout out YES!!!

    b) Take a quick look round the office and as soon as you see that someone in a red shirt exists, shout out YES!!!

    Both will give you the right answer, but option b) will get you there sooner.

    Thank you All for replying,I went through the explanations in detail given by Gila and Marsh, But EdVassie, you have great sense of humor I believe, by the way are u teacher or lecturer of sorts

  • EdVassie (6/8/2009)


    If you are asked 'Is anyone in the office wearing a red shirt' what do you do?

    Well the first thing that *I* do when asked this is to make sure that I'm not wearing a red shirt and that it isn't James T. Kirk asking me. Because if it is, then I do the fastest Exit, Stage Left, you ever saw.

    😀

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (6/8/2009)


    EdVassie (6/8/2009)


    If you are asked 'Is anyone in the office wearing a red shirt' what do you do?

    Well the first thing that *I* do when asked this is to make sure that I'm not wearing a red shirt and that it isn't James T. Kirk asking me. Because if it is, then I do the fastest Exit, Stage Left, you ever saw.

    😀

    Watched the latest StarTrek movie recently?

    Redshirt, meet Mining Beam. *sizzle*

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/8/2009)


    RBarryYoung (6/8/2009)


    EdVassie (6/8/2009)


    If you are asked 'Is anyone in the office wearing a red shirt' what do you do?

    Well the first thing that *I* do when asked this is to make sure that I'm not wearing a red shirt and that it isn't James T. Kirk asking me. Because if it is, then I do the fastest Exit, Stage Left, you ever saw.

    😀

    Watched the latest StarTrek movie recently?

    Redshirt, meet Mining Beam. *sizzle*

    Not yet, too busy. 🙁

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • are u teacher or lecturer of sorts

    No, I am a database architect.

    But I have attended lots of Bonnie Baker seminars. She has a knack of explaining sometimes complex database issues in simple terms. If you get the chance then listen to what she has to say, even if it is about DB2 instead of SQL Server.

    BTW, I cannot remember if Bonnie gave the count v exists example or if I worked that out myself.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply