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 ««123»»

EXISTS Subqueries: SELECT 1 vs. SELECT * (Database Weekly 11.02.2008) Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2008 9:42 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 20, 2014 9:33 AM
Points: 449, Visits: 1,866
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!

Thanks again!

GermanDBA



Regards,

WilliamD
Post #455182
Posted Wednesday, February 13, 2008 1:29 PM


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 @ 2:08 AM
Points: 40,415, Visits: 36,864
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 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 #455350
Posted Wednesday, February 13, 2008 2:16 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #455405
Posted Wednesday, February 13, 2008 9:49 PM


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 @ 2:08 AM
Points: 40,415, Visits: 36,864
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 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 #455500
Posted Thursday, February 14, 2008 12:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #455541
Posted Thursday, February 14, 2008 1:18 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 @ 2:08 AM
Points: 40,415, Visits: 36,864
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 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 #455549
Posted Thursday, February 14, 2008 3:31 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:56 AM
Points: 2,842, Visits: 3,876
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
Post #455587
Posted Thursday, February 14, 2008 2:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:05 AM
Points: 3,467, Visits: 1,831
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
Post #456014
Posted Friday, June 13, 2014 4:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 4:26 AM
Points: 1, 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
Post #1580439
Posted Friday, June 13, 2014 4:26 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 @ 2:08 AM
Points: 40,415, Visits: 36,864
It will not.

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



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 #1580440
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse