<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
T-SQL (SS2K5)
»
SELECT * versus SELECT 1 when using EXISTS
16 posts, Page 1 of 2
1
2
»»
SELECT * versus SELECT 1 when using EXISTS
Rate Topic
Display Mode
Topic Options
Author
Message
mikedotnet2
mikedotnet2
Posted Friday, September 11, 2009 12:51 PM
Forum 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
Adam Angelini
Adam Angelini
Posted Friday, September 11, 2009 12:58 PM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 8:27 AM
Points: 1,084,
Visits: 808
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
Marcin Gol
Marcin Gol
Posted Friday, September 11, 2009 3:19 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, October 03, 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
Jeff Moden
Jeff Moden
Posted Friday, September 11, 2009 8:45 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910,
Visits: 26,802
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
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/
Post #786787
Marcin Gol
Marcin Gol
Posted Saturday, September 12, 2009 3:00 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Sunday, October 03, 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
Garadin
Garadin
Posted Saturday, September 12, 2009 7:25 AM
SSCommitted
Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525,
Visits: 4,047
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
Paul White
Paul White
Posted Saturday, September 12, 2009 8:28 AM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
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
Jeff Moden
Jeff Moden
Posted Saturday, September 12, 2009 1:24 PM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 8:14 AM
Points: 32,910,
Visits: 26,802
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 "
R
ow-
B
y-
A
gonizing-
R
ow".
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."
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/
Post #786907
mikedotnet2
mikedotnet2
Posted Monday, September 14, 2009 10:02 AM
Forum 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
Paul White
Paul White
Posted Monday, September 14, 2009 3:21 PM
SSChampion
Group: General Forum Members
Last Login: Today @ 1:49 AM
Points: 10,990,
Visits: 10,540
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 »
16 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
