June 4, 2009 at 12:28 am
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
June 4, 2009 at 1:17 am
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
June 4, 2009 at 1:19 am
-
www.sql.lu
SQL Server Luxembourg User Group
June 4, 2009 at 2:31 am
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
June 8, 2009 at 3:02 am
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
June 8, 2009 at 9:14 am
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
June 8, 2009 at 11:07 am
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]
June 8, 2009 at 12:10 pm
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
June 8, 2009 at 2:21 pm
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]
June 9, 2009 at 2:36 am
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply