Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
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
Ken McKelvey
Ken McKelvey
SSC Eights!
SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)SSC Eights! (958 reputation)

Group: General Forum Members
Points: 958 Visits: 7249
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.
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2018 Visits: 11184
http://www.sqlservercentral.com/Forums/Topic786650-338-1.aspx
http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx

Marginal differences, it would appear.

BrainDonor.

Steve Hall
Linkedin
Blog Site
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47200 Visits: 44367
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, 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


deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
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
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47200 Visits: 44367
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, 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


deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
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
deepak.a
deepak.a
Mr or Mrs. 500
Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)Mr or Mrs. 500 (562 reputation)

Group: General Forum Members
Points: 562 Visits: 863
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
pdanes2
pdanes2
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search