Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » “SELECT 1″ rather than a “SELECT *” when...
Add to briefcase
15 posts, Page 1 of 2
12»»

“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
deepak.a
Posted Thursday, February 10, 2011 4:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861
Hi All,

Can any body please explain ,While Checking the existance of records using "exists". Which method will be more sufficient and WHY ?

“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.


Thanks & Regards
Deepak.A
Post #1061848
Ken McKelvey
Posted Thursday, February 10, 2011 4:48 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 822, Visits: 5,101
In Theory, it makes no difference what you SELECT in an EXISTS sub-query as it is just syntactical sugar. (ie Nothing is actually selected.)

I seem to remember someone doing a test in SQL2000 which showed that SELECTing a constant was marginally quicker than SELECT * as SQL2000 seemed to look up the column names. I have no idea if this is still true with SQL2008.

A lot of development shops specify that you should use SELECT 1, in EXISTS sub-queries, so they can easily check for lazy developers putting SELECT * in the rest of their code.
Post #1061853
BrainDonor
Posted Thursday, February 10, 2011 5:00 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:22 AM
Points: 1,400, Visits: 6,888
http://www.sqlservercentral.com/Forums/Topic786650-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx

Marginal differences, it would appear.

BrainDonor.

BrainDonor
Linkedin
Post #1061858
GilaMonster
Posted Thursday, February 10, 2011 5:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 37,671, Visits: 29,925
No difference. No matter what you use, it's removed early in the parsing stage. I use SELECT 1 to make it extremely clear that the exists is not returning any columns.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1061879
deepak.a
Posted Thursday, February 10, 2011 6:18 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861
Hi GilaMonster,

Thanks for reply,if we use columns or * will it make any performance difference? and also

using top 1 1 instead 1 will give better peroformance ?

Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)


using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?


Thanks & regards
Deepak.A
Post #1061904
Dave Ballantyne
Posted Thursday, February 10, 2011 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227
There are the same , count(1) get converted to count(*) by the optimizer / parser.

See this post on my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx


Clear Sky SQL
My Blog
Kent user group
Post #1061913
GilaMonster
Posted Thursday, February 10, 2011 6:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:49 PM
Points: 37,671, Visits: 29,925
deepak.a (2/10/2011)
Thanks for reply,if we use columns or * will it make any performance difference? and also
Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)


No and no


using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?


Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.

Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1061931
deepak.a
Posted Friday, February 11, 2011 2:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861
Dave Ballantyne (2/10/2011)
There are the same , count(1) get converted to count(*) by the optimizer / parser.

See this post on my blog
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2010/07/27/count-or-count-1.aspx


thanks dave for your reply and sharing nice article
Post #1062412
deepak.a
Posted Friday, February 11, 2011 2:29 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, August 13, 2012 10:04 AM
Points: 554, Visits: 861
GilaMonster (2/10/2011)
deepak.a (2/10/2011)
Thanks for reply,if we use columns or * will it make any performance difference? and also
Like ex : if exists(Select 1 ... ) or if exists(select Top 1 1 ....)


No and no


using a SELECT 1, avoids having to look at any of the meta-data that is not even needed to check the existancee of the records form the table?


Got nothing to do with metadata. EXISTS only cares about whether there is a row or not, it doesn't look at columns, it doesn't care.

Don't waste your time trying to do micro-optimisations. Silly tricks like this do not make major performance differences.


Thanks a lot Gail Shaw for your reply
Post #1062413
pdanes2
Posted Friday, February 11, 2011 7:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 01, 2011 2:06 AM
Points: 86, Visits: 462
My two cents worth: I use 'SELECT Null FROM ...' when using an EXISTS clause. As Gail has pointed out, the speed difference is probably trivial at best, more likely non-existent, but it emphasizes (to me, anyway) that I'm not looking for any data in such a phrase, just the bare existence of at least one record matching certain conditions.
Post #1062567
« Prev Topic | Next Topic »

Add to briefcase
15 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.