SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008)


EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008)

Author
Message
w.durkin@online.de
w.durkin@online.de
SSChasing Mays
SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)SSChasing Mays (641 reputation)

Group: General Forum Members
Points: 641 Visits: 1879
Hi,

thanks very much so far. The information and explanations supplied are clear and understandable. I thought that select 1 would be quicker, now I know! Cool

Thanks again!

GermanDBA

Regards,

WilliamD
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86789 Visits: 45254
Barely. I would doubt it would be visible.

I prefer the .. EXISTS (SELECT <constant> ... format, as it's fairly clear from that that no values are been returned

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


Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3549 Visits: 3889
I am a little confused by this thread.
Ignore my post if you meant the same as I write now:

I am pretty sure that SQL Server does NOT make a difference between
EXISTS(SELECT 1...
or
EXISTS(SELECT *...

Reason is the following: SQL Server does not need to retrieve any column list for the SELECT clause to determine if a FROM clause in conjunction with a WHERE clause return any rows - it is just not relevant. And since SQL Server is smart enough, it (he? she?) simply ignores the SELECT clause.

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.

http://technet.microsoft.com/en-us/library/ms189259.aspx

Best Regards,

Chris Büttner
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86789 Visits: 45254
Check the link in the first post. It's a blog post by Conor Cunningham, former member of the SQL Server Query processing team and former development lead on the Query Optimizer. He explains why there's a (very, ver, very small) difference

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


Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3549 Visits: 3889
Hm, should have checked this link first...
Still I am not yet convinced, or at least a little confused:

Given a table "a" with columns "cola" and "colrestricted".
User "Test" only has access to column "cola", but not to "colrestricted".

Then I execute the following queries:
SELECT * FROM sys.objects WHERE EXISTS(SELECT cola FROM a)
SELECT * FROM sys.objects WHERE EXISTS(SELECT 1 FROM a)
SELECT * FROM sys.objects WHERE EXISTS(SELECT * FROM a)

Now I get an error from both the 2nd and the 3rd query! (Permission denied on colrestricted)
Why is query 2 (SELECT 1... ) giving me an error?

Thought Id ask you first instead of addressing this to Conor directly. There is probably a simple explanation...

Thanks!

Best Regards,

Chris Büttner
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86789 Visits: 45254
No idea, sorry.

If you just do a straight Select 1 FROM a, do you get the same error?

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


Christian Buettner-167247
Christian Buettner-167247
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3549 Visits: 3889
Yes, the same error is raised.

I posted a comment on the blog, maybe Conor will be able to explain this.

Thanks!

Best Regards,

Chris Büttner
Kenneth.Fisher
Kenneth.Fisher
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4276 Visits: 2033
Probably a fairly minor point but even a very very small difference can be worth it when you have a piece of code being run thousands of times a second.

Also another good place to use a 1 instead of a * is when doing a count.
For example

SELECT COUNT(1) FROM sysobjects


vs

SELECT COUNT(*) FROM sysobjects



Again a fairly minor difference but one that can have a large impact if the code is run often enough.

Kenneth

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
satvir.grewal03
satvir.grewal03
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
Hi All

I have a question like if we use
(SELECT top 1 1 from) instead of (SELECT 1 from) will it contribute in some sort of improvement.

I am talking about EXISTS here.



Thanks
Satvir
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86789 Visits: 45254
It will not.

Please in future post new questions in a new thread. Thanks

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


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