Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”. Expand / Collapse
Author
Message
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: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1061848
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: Monday, December 15, 2014 9:03 AM
Points: 851, Visits: 5,596
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
Posted Thursday, February 10, 2011 5:00 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 5:55 AM
Points: 1,483, Visits: 8,545
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
Blog Site
Post #1061858
Posted Thursday, February 10, 2011 5:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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
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: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1061904
Posted Thursday, February 10, 2011 6:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:06 AM
Points: 1,768, Visits: 8,318
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
Posted Thursday, February 10, 2011 6:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:14 PM
Points: 40,615, Visits: 37,081
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
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: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1062412
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: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1062413
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 1, 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 12»»

Permissions Expand / Collapse