| Author |
Topic |
|
shapper
Constraint Violating Yak Guru
450 Posts |
 Posted - 2007-02-01 : 17:46:39
|
| Hello,I created the following to check if a record exists:IF (EXISTS (SELECT LevelName FROM dbo.by27_Levels WHERE LOWER(@LevelName) = LOWER(LevelName))) Return (1)ELSE Return (0)And I also found in a web page another solution: IF EXISTS(SELECT 1 FROM TABLENAME WHERE LevelName=@LevelName) SELECT 1 ELSE SELECT 0 - Which approach should I use?- Why "SELECT 1 FROM"?- And when should I use SELECT or RETURN?I will use this procedure on an ASP.NET 2.0 / C# web site.I am not sure if this important but anyway ...Thank You,Miguel |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-01 : 18:10:07
|
| As mentioned in the other thread, I would use OUTPUT parameters. I use RETURN to indicate success or failure of a stored procedure but not to return data to the application.Tara Kizer |
 |
|
|
cornetto
Starting Member
2 Posts |
Posted - 2007-02-02 : 07:33:03
|
| The "Select 1 from" part simply selects the number "1" if TABLENAME has any LevelName values equal to @LevelName. I suppose you have to select -something- and selecting 1 is fast. You could also use Select *, which would give SQL server the option to use any index it likes to do the select, which could be fast too. Specifying SELECT LevelName will mean that there must be a good index on levelname in order for SQL Server to do the select quickly. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-02 : 07:44:00
|
| 1. No need to use LOWER() or UPPER() function in comparison, unless you have Case-sensitive collation2. If this is the only code in the SP, I would prefer RETURN statement than SELECT, because RETURN statement causes return value to be sent as a default scalar output parameter (rather than resultset as with the SELECT statement).3. SELECT 1 or SELECT * or SELECT col-name are equivalent performance-wsie. This style to select a constant has come from previous versions SQL Server/ORACLE where it would give you slightly better execution plan.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-02 : 11:23:36
|
| Harsh, actually SELECT * should be used. It is the best performance-wise, although only minimally different from SELECT 1. This is true when used with IF EXISTS or IF NOT EXISTS.Tara Kizer |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-02 : 12:06:28
|
| Thanks for the information, Tara. Does it generate a differenet Exec. plan?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-02-05 : 08:32:25
|
| Check the execution plan of both, SET STAISTICS IO ON and check the no of logical reads for comparison, this would give u a better idea which one to use. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-05 : 08:45:44
|
| hrishi, There is no difference in either Execution plan or logical reads. That's why I wanted to know how Tara determined which one is good?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-05 : 08:55:23
|
My two-pennyworth is that SELECT *, used in an EXISTS clause, is the convention for SQL Server (and indeed all the examples I have seen in BoL use that style) and as such may be treated favourably, and therefore I have taken the view that variants, whilst appearing to perform equally well, may miss the opportunity of optimisation some how, either now or in some future version.BoL:quote:
The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery.
Kristen |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-05 : 09:01:46
|
| Hmmm...That can be a reason (may be SQL server do some optimization for SELECT * queries with IF EXISTS() since it is mostly used dialect)Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
hrishi_des
Yak Posting Veteran
76 Posts |
Posted - 2007-02-27 : 04:37:01
|
| Hi Harsh,That would be scary!!!When solution is simple, God is answering…. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-27 : 05:49:35
|
quote:
Originally posted by hrishi_des
Hi Harsh,That would be scary!!!When solution is simple, God is answering….
Why is that?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
|
Check if record exist. Which one should I use?