February 1, 2010 at 10:14 am
I have to say this was the easiest question to date for me!
When I logged in to view the question, there were 3 answer choices.
1. Wrong Answer
2. Right Answer
3. Wrong Answer
Althought tempted by 1 & 3, I decided to go for #2
February 1, 2010 at 10:21 am
Jeff,
Did you get it right?
February 1, 2010 at 10:24 am
Yes. Yes I did 🙂
February 3, 2010 at 1:39 pm
Hugo Kornelis (1/27/2010)
<snip>Other, minor issues are the unneeded brackets around [name] (name is not on the list of reserved keywords, so no delimitation required); the strangely popular but really rather odd EXISTS 1 instead of EXISTS * (EXISTS checks for rows, not values, so what you put there is immaterial - except that * is the standard thhat anyone understands immediately while EXISTS(SELECT 1 makes everybody pause to think); and the broken link in the explanation (the two links both point to the same page).
Bottom line - the only truly correct answer is "it depends". Of the options give, "both are equal" is almost correct. The other two options are plain nonsense.
I actually got into the habit of using ...EXISTS(SELECT 1...) because if you use the construct in an object that will be schema-bound, the "*" isn't allowed (even though it's only used for the EXISTS predicate). Seems an oversight on MS's part (or the SQL standard's).
TroyK
February 6, 2010 at 5:01 am
This is a great question.
It took C# (formerly a vb programmer here) to teach me that collections work quite efficiently using delegates and predicates and, bearing this concept in mind, it makes far more sense (to me) that an "exists" functionality will provide a faster and more efficient query than one searching the index for a value (IN statement). In this respect the query simply records the rows that are true... or well, at least, not being fully aware of the inner workings, it makes sense to me that counting and returning items that are true is more efficient than searching for them by name and returning them.
Jamie
February 8, 2010 at 2:00 am
Jamie Longstreet-481950 (2/6/2010)
it makes far more sense (to me) that an "exists" functionality will provide a faster and more efficient query than one searching the index for a value (IN statement).
It does indeed make more sense - and the SQLServer optimiser knows it too, which is why it treats both versions the same 🙂
February 15, 2010 at 7:42 am
kaspencer (1/28/2010)
However, if you look at an un answered question (next time), you will see that it is actually quite difficult (no doubt deliberately) to read a thread on a question before answering it.
Well, actually no, it's easy. And straightforward. And obvious. If the people who designed the website had been deliberately making it difficult, it probably would have been difficult, but it isn't. Here's how to do it: note the author of the QOTD (or enough of the name to be useful - in this case "Prakriti" is plenty, you don't need the rest of the name) and the topic of the question (in this case query cost). Then go to any of the discussion forums. Click on "Article Discussions by Author" in the heading (and wait for the page to load it will take a few 10s of second if you have a slow connection); then search for the query author's name; then in the set of discussion topics listed click on the one which ois the topic of the QOTD - bingo, you are there.
I know that you can do this because I saw the two wrongs and a right and concluded something pretty weird was going on (I could make a guess why - the QOTD in the email was clearly one of "those" QOTDs), wanted to know what that was before clicking on an answer, so thought about it for about 20 seonds and then went there. I hadn't ever done it, or even thought about it, before. So I know it is not difficult (thos big block headers make it pretty obvious how to do it).
Tom
February 15, 2010 at 7:56 am
Tom.Thomson (2/15/2010)
kaspencer (1/28/2010)
However, if you look at an un answered question (next time), you will see that it is actually quite difficult (no doubt deliberately) to read a thread on a question before answering it.Well, actually no, it's easy. And straightforward. And obvious.
No it isn't, not if you're not clicking a link from an email. The emails contain the author of the question but the question page itself does not, see the attachment of today's question as an example. Of course it is easier if you have that information but I didn't, and I would think kaspencer didn't. Personally I don't have time to answer a question a day when i get the email, i go on once a week or so and answer the ones i've missed. In a situation like that being able to check why a question might look odd isn't really very easy to accomplish.
Paul
February 15, 2010 at 12:06 pm
paul.goldstraw (2/15/2010)
No it isn't, not if you're not clicking a link from an email. The emails contain the author of the question but the question page itself does not, see the attachment of today's question as an example. Of course it is easier if you have that information but I didn't, and I would think kaspencer didn't. Personally I don't have time to answer a question a day when i get the email, i go on once a week or so and answer the ones i've missed. In a situation like that being able to check why a question might look odd isn't really very easy to accomplish.Paul
Good point, but if you don't have the author you can try typing the QOTD topic into the search box at the top right of a formum page and hope that either there are few enough hits to sort through or that what you are looking for shows up on the first page. As I haven't a clue how the search works (ie how the results are determined by what's typed in the box) other than that at least some of the time typing more broadens the search, it doesn't narrow it, and that quotation marks try to match the whole pohrase including the marks, I don't generally that search for anything - I spent some time trying to search for articles when I first met SQLS C, but gave up on it pretty quickly. So when I saw your response, I decided to try it to see how good it is for finding a QOTD discussion. A search on Query cost turned up the current discussion as 7th item on the first page of results - so perhaps I should have done that rather than going back to the email to get the author - but I'd be surprised if it were that good every time.
I too can't generally keep up with SQLSC articles and QOTD on a daily basis, but I catch up by going therough the emails when I have time, since I want to catch up on articles and news as well as on QOTD.
Tom
February 15, 2010 at 9:05 pm
It's actually trivial to read the discussion. I usually do it first thing to check the question before I check on the way it looks to a new user.
On the home page we have the question, and then we have the "discuss" link there.
If you want to read the discussion first, be my guest. This is a tool for you, the user. It's not a ranking, it doesn't imply you have knowledge, and it doesn't imply that you're a guru. You could add to the discussion and show something, but answering a question doesn't necessarily prove anything.
Complaining without any valid reason for doing so, however, does show an impression for someone that might be looking to interview you.
March 15, 2010 at 2:50 pm
LOL!!!
I had 3 choices
1. Wrong Answer
2. Right Answer
3. Wrong Answer
so I chose #2, the "Right Answer"... was this a joke question or did the webpage break??? 🙂
I would like to see the actual or original choices if they are available...
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 15, 2010 at 2:51 pm
NM, I read the forum... I got a point for that??? I almost feel guilty 🙁
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
March 19, 2010 at 4:06 am
Something is not right....
I got three choices
1. Wrong Answer
2. Right Answer
3. Wrong Answer
I got the point by selecting 'Right Answer' 😛
March 30, 2010 at 10:19 am
The optimizer works on a logical tree of operations. That tree is derived from the text of the statement presented. The optimizer has many built in guaranteed-safe transformations which mean that many logically equivalently written queries end up producing an identical, or trivially different, execution plan.
The important thing to realise is that in many cases, queries written with IN and EXISTS are provably identical, and produce identical execution plans. In those cases, debating which is or isn't more efficient is an utterly moot point. The text is different, but the execution is the same.
March 30, 2010 at 10:22 am
Hugo Kornelis (1/27/2010)
the strangely popular but really rather odd EXISTS 1 instead of EXISTS *...
I too find it odd, and always use the star syntax, but it turns out there is a (vanishingly small) difference, related to the checking of column permissions for EXISTS *.
Viewing 15 posts - 61 through 75 (of 83 total)
You must be logged in to reply to this topic. Login to reply