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 * versus SELECT 1 when using EXISTS Expand / Collapse
Author
Message
Posted Friday, September 11, 2009 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 3:08 PM
Points: 9, Visits: 69
When using EXISTS is there a difference when using EXISTS (SELECT 1...) OR EXISTS(SELECT *...). I've seen differing opinions on this.

For example this was an argument for using "SELECT 1" or a constant: http://books.google.com/books?id=lBdTTXpVc3wC&pg=PA247&lpg=PA247&dq=SQL+SERVER+EXISTS+SELECT+*+VERSUS+SELECT+1&source=bl&ots=sbwNZKcDTX&sig=9e8iCswWhkIb9WFOaqcXJtDcXfY&hl=en&ei=L5qqSvOQL9CYlAeT6KncBg&sa=X&oi=book_result&ct=result&resnum=4#v=onepage&q=&f=false

While I've seen posts on other forums where people have said that it doesn't matter,
because the SELECT list is ignore when coupled with EXISTS.


My apologies. Found a similar post that argues this very topic on the forum.

http://www.sqlservercentral.com/Forums/Topic453737-338-1.aspx?Highlight=EXISTS
Post #786650
Posted Friday, September 11, 2009 12:58 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 1,143, Visits: 1,001
I've seen arguments both ways, but I always recommend trying both solutions yourself, checking query plans, and deciding which is better in your situation. I suspect both will produce the same plan, but it's always best to test it out.
Post #786659
Posted Friday, September 11, 2009 3:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
in case of exists/no exsits - it is the only place where you can use star without loosing performance and maitainability
Post #786733
Posted Friday, September 11, 2009 8:45 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
I agree. I also agree with very specifically with what Adam said. Even if the pros say something, you should always test it yourself. The reason is that a lot of folks also believe in certain myths and end up posting what they've heard rather than what they've tested.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #786787
Posted Saturday, September 12, 2009 3:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
Jeff - so in your opinion using star in exists/no exists queries is bad? (or something more then a bad habit?)
Post #786840
Posted Saturday, September 12, 2009 7:25 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 1,519, Visits: 4,075
I think he was just advocating testing things yourself and not always taking other people's word for it, not taking a side on this issue in particular. IIRC, 1 and * do exactly the same thing in exists because exists doesn't actually return data, it merely verifies the existence of a row matching criteria, so your select is more or less ignored.

Guess I should test it myself to verify that though .


Seth Phelabaum
Consistency is only a virtue if you're not a screwup.

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #786863
Posted Saturday, September 12, 2009 8:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Marcin Gol [SQL Server MVP] (9/11/2009)
in case of exists/no exsits - it is the only place where you can use star without loosing performance and maitainability

...COUNT(*) versus COUNT(1) is the other example.

Star versus constant or NULL - it makes no difference in modern SQL Engines. DB2 and Oracle had a preference for indexes columns and constants respectively way way way back in the day, but no longer.

I don't expect anyone to take my word for it though. * shrug *

Some people prefer not to use SELECT * since it makes searching for dumb uses of the construct harder.
Some people prefer SELECT * because it mirrors the COUNT(*) idea.
Some people prefer SELECT * because SELECT [constant] is so arbitrary.

I like star.

I find COUNT(1) particularly amusing since the XML query plan still shows the operation as "count_star"
...EXISTS (SELECT NULL seems confused to me.
...EXISTS (SELECT 42.9853 seems peculiar, but equivalent to using 1 or 0 or whatever.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #786878
Posted Saturday, September 12, 2009 1:24 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
Garadin (9/12/2009)
I think he was just advocating testing things yourself and not always taking other people's word for it, not taking a side on this issue in particular. IIRC, 1 and * do exactly the same thing in exists because exists doesn't actually return data, it merely verifies the existence of a row matching criteria, so your select is more or less ignored.

Guess I should test it myself to verify that though .


Correct... not advocating either method (mostly because it doesn't make a difference in performance) but am advocating that people actually test for themselves and find out.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #786907
Posted Monday, September 14, 2009 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 24, 2010 3:08 PM
Points: 9, Visits: 69
Thanks for the replys. I agree that checking it out myself is always the best route, and I generally don't take someones word (especially on the internet) at face value without verifying it myself.

That being said, I had no idea how to verify this statement from the link above: "However, from an 'internal' standpoint, SELECT * causes the SQL engine to check the data dictionary unnecessarily" and for this reason it seems the author was advocating the use of a constant instead of SELECT *.

How would you test this? Would something like this show in an execution plan?
Post #787582
Posted Monday, September 14, 2009 3:21 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, October 17, 2014 8:13 AM
Points: 9,926, Visits: 11,188
Dude, it's a myth. Metadata is never checked for COUNT(*) or EXISTS(SELECT *...a quick Google will provide you with testimony to that fact from Itsik Ben-Gan, Erland Sommarskog, Joe Celko...anyone you happen to believe in

There is no difference between the star syntax and the constant-value syntax. None, aside from the spelling!

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #787780
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse