InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 03:12:10 GMT20http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxOption of the question doesn't look correct to me..Though I made it correct!Tue, 27 Jul 2010 08:16:59 GMTSQL-DBA-01http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxThanks Hugo. I read the blog and the Connect item.Seems to me that things have changed since the Connect item, given the results of your tests.If the information in the blog is still accurate, the only extra cost of SELECT * is metadata expansion at compile time: plan and execution time is identical. Good to know.Conor's active blog is here:[url]http://blogs.msdn.com/conor_cunningham_msft/[/url]I see your COALESCE connect item has been updated...Sun, 04 Apr 2010 15:17:00 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxMy apologies, I was forgotten to follow up here.I got a response from Itzik. His source is an old blog post from Conor Cunningham - another man to take VERY seriously if he writes about SQL Server. Here is a link: [url=http://www.sqlskills.com/BLOGS/CONOR/2008/02/default.aspx?page=2]http://www.sqlskills.com/BLOGS/CONOR/2008/02/default.aspx?page=2[/url]. I think it's an old and abandoned blog, the comments link does not work, but the post itself can still be read.What it amounts to is that SELECT * is first replaced with SELECT <list of columns>, then permissions are checked, and only then does the optimizer realize that it's inside an EXISTS and so removes the column lists again. See the Microsoft feedback on [url=https://connect.microsoft.com/SQLServer/feedback/details/533491/users-with-select-permission-on-one-column-in-a-table-get-errors-from-if-exists-select-from-table]https://connect.microsoft.com/SQLServer/feedback/details/533491/users-with-select-permission-on-one-column-in-a-table-get-errors-from-if-exists-select-from-table[/url] for an explanation of WHY these permissions are checked (though I don't agree 100%).However, the differences between all these variants are incredibly small, and not really worth spending any amount of time or energy on. I mainly use SELECT * in EXISTS because it's the most common and sort of standard version, and because it documents that I am interested in existence of a row.Sun, 04 Apr 2010 14:57:19 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Hugo Kornelis (3/30/2010)[/b][hr]If there's one thing I've learned over the past years, it is to take everything that comes from Itzik VERY seriously! ;)[/quote]Agreed. He has a tricky habit of knowing stuff quite deeply. Good luck.Tue, 30 Mar 2010 13:13:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]Hugo Kornelis (3/30/2010)[/b][hr]I was not able to find any way to get the SELECT * to behave other than the SELECT 1[/quote]Me either! I tried indexes, scalar functions, XML methods...you name it.Looks like Itzik will be revising that paragraph in the next edition.I am relieved - I had posted many times in the past to say that both forms were identical, so was quite shocked to read that there was a difference after all. Never bothered to test it though...so thanks for that.[/quote]I'll contact Itzik and ask him if we are overlooking something. If there's one thing I've learned over the past years, it is to take everything that comes from Itzik VERY seriously! ;)Tue, 30 Mar 2010 13:03:38 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Hugo Kornelis (3/30/2010)[/b][hr]I was not able to find any way to get the SELECT * to behave other than the SELECT 1[/quote]Me either! I tried indexes, scalar functions, XML methods...you name it.Looks like Itzik will be revising that paragraph in the next edition.I am relieved - I had posted many times in the past to say that both forms were identical, so was quite shocked to read that there was a difference after all. Never bothered to test it though...so thanks for that.Tue, 30 Mar 2010 12:56:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]Hugo Kornelis (3/30/2010)[/b][hr]As far as I know, that was once the case. In SQL Server 7.0 or so.[/quote]It's still in Itzik's Inside SQL Server 2008 T-SQL Querying book :-)Do you want to test it or should I? ;-)The effect was always, as I said, vanishingly small. I always use the star syntax. As I think I mentioned.[/quote]I tested it. Below is the code I ran. It shows that both SELECT * and SELECT 1 test for both table- and column-level permissions. (Try changing the granted and denied privileges - I was not able to find any way to get the SELECT * to behave other than the SELECT 1).[code="sql"]create table x (a int, b int);gocreate user TestUser without login;godeny select on x to TestUser;grant select on x(b) to TestUser;goif exists (select * from x) print 'Aye';if exists (select 1 from x) print 'Aye';if exists (select a from x) print 'Aye';goexecute as user='TestUser';goif exists (select * from x) print 'Aye';if exists (select 1 from x) print 'Aye';if exists (select a from x) print 'Aye';gorevert;godrop user TestUser;drop table x;go[/code]Tue, 30 Mar 2010 12:41:01 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Hugo Kornelis (3/30/2010)[/b][hr]As far as I know, that was once the case. In SQL Server 7.0 or so.[/quote]It's still in Itzik's Inside SQL Server 2008 T-SQL Querying book :-)Do you want to test it or should I? ;-)The effect was always, as I said, vanishingly small. I always use the star syntax. As I think I mentioned.Tue, 30 Mar 2010 12:31:05 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Paul White NZ (3/30/2010)[/b][hr][quote][b]Hugo Kornelis (1/27/2010)[/b][hr]the strangely popular but really rather odd EXISTS 1 instead of EXISTS *...[/quote]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 *.[/quote]As far as I know, that was once the case. In SQL Server 7.0 or so.Tue, 30 Mar 2010 12:25:17 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Hugo Kornelis (1/27/2010)[/b][hr]the strangely popular but really rather odd EXISTS 1 instead of EXISTS *...[/quote]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 *.Tue, 30 Mar 2010 10:22:45 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxThe 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.Tue, 30 Mar 2010 10:19:10 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxSomething is not right....I got three choices1. Wrong Answer2. Right Answer3. Wrong AnswerI got the point by selecting 'Right Answer' :-PFri, 19 Mar 2010 04:06:34 GMTAtif-ullah Sheikhhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxNM, I read the forum... I got a point for that??? I almost feel guilty :(Mon, 15 Mar 2010 14:51:58 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxLOL!!!I had 3 choices1. Wrong Answer2. Right Answer3. Wrong Answerso 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...Mon, 15 Mar 2010 14:50:12 GMTPeter Trasthttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxIt'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.Mon, 15 Feb 2010 21:05:46 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]paul.goldstraw (2/15/2010)[/b][hr]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[/quote]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.Mon, 15 Feb 2010 12:06:56 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Tom.Thomson (2/15/2010)[/b][hr][quote][b]kaspencer (1/28/2010)[/b][hr]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.[/quote]Well, actually no, it's easy. And straightforward. And obvious. [/quote]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.PaulMon, 15 Feb 2010 07:56:19 GMTpaul.goldstrawhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]kaspencer (1/28/2010)[/b][hr]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.[/quote]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).Mon, 15 Feb 2010 07:42:27 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Jamie Longstreet-481950 (2/6/2010)[/b][hr]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). [/quote]It does indeed make more sense - and the SQLServer optimiser knows it too, which is why it treats both versions the same :-)Mon, 08 Feb 2010 02:00:53 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxThis 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.Sat, 06 Feb 2010 05:01:27 GMTJamie Longstreet-481950http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]Hugo Kornelis (1/27/2010)[/b][hr]<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.[/quote]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).TroyKWed, 03 Feb 2010 13:39:33 GMTcs_troykhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxYes. Yes I did :)Mon, 01 Feb 2010 10:24:39 GMTJeff Welcome-130556http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxJeff,Did you get it right?Mon, 01 Feb 2010 10:21:28 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxI 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 Answer2. Right Answer3. Wrong AnswerAlthought tempted by 1 & 3, I decided to go for #2Mon, 01 Feb 2010 10:14:52 GMTJeff Welcome-130556http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]sistemas 95572 (1/29/2010)[/b][hr]Assuming that: * the inner select returns a reasonable amount of rows - so it fits in memoryshouldn't the IN statement be better since the inner query executes only once, storing in memory all those returned int values (using some kind of ordered array), and then perform a bin search for each value returned by the outer select?[/quote]Well, that assumes that the inner query would only execute once. I'm not sure we can guarantee that. In fact, I'm such a pessimist that I would expect the worst case, that the inner query would execute once for each row returned by the outer query. Now, I'm quite sure that the optimizer would handle a good bit of this overhead and give me a pleasant surprise, but maybe not.You may be right in this case. I simply can't tell.Fri, 29 Jan 2010 09:02:29 GMTmark.rosshttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxAn administrator changed the possible answers because of the question's subjectivity.Fri, 29 Jan 2010 08:12:18 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxI think the answer options were corrupted somehow. Right now the question shows three answer possibilities, namely "Wrong Answer", "Right Answer" and "Wrong Answer".Fri, 29 Jan 2010 07:43:50 GMTmichael.kjorlinghttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]sistemas 95572 (1/29/2010)[/b][hr]Assuming that: * the inner select returns a reasonable amount of rows - so it fits in memoryshouldn't the IN statement be better since the inner query executes only once, storing in memory all those returned int values (using some kind of ordered array), and then perform a bin search for each value returned by the outer select?[/quote]The plan is more likely to be like this:Both the teachers table and the students table are sorted by teacher_id (if there is an index, this isn't necessary); Duplicates are eliminated from the students list; The server 'walks' down each list, emitting rows when the teacher_ids match.Fri, 29 Jan 2010 06:13:30 GMTmike.anderson 52709http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxAssuming that: * the inner select returns a reasonable amount of rows - so it fits in memoryshouldn't the IN statement be better since the inner query executes only once, storing in memory all those returned int values (using some kind of ordered array), and then perform a bin search for each value returned by the outer select?Fri, 29 Jan 2010 06:00:26 GMTsistemas 95572http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mike.anderson 52709 (1/29/2010)[/b][hr][quote][b]mark.ross (1/28/2010)[/b][hr][quote][b]mike.anderson 52709 (1/28/2010)[/b][hr]I'm curious to know what difference indexing makes to the plan, aside from transforming table scans to index scans?[/quote]Well, depending on the index, the table scan COULD be transformed all the way down to an index seek, which I would expect to perform much better than a table scan.[/quote]I'd agree if there were selection criteria, but there aren't, so a scan will be more efficient than seeks.[/quote]Sorry - I'll correct myself - I was assuming that all (or most) rows will be joined in both tables. If there is a large number of teachers without any students, then seeks will start to be more efficient.Fri, 29 Jan 2010 05:11:50 GMTmike.anderson 52709http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mark.ross (1/28/2010)[/b][hr][quote][b]mike.anderson 52709 (1/28/2010)[/b][hr]I'm curious to know what difference indexing makes to the plan, aside from transforming table scans to index scans?[/quote]Well, depending on the index, the table scan COULD be transformed all the way down to an index seek, which I would expect to perform much better than a table scan.[/quote]I'd agree if there were selection criteria, but there aren't, so a scan will be more efficient than seeks.Fri, 29 Jan 2010 02:58:02 GMTmike.anderson 52709http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxthanks CirquedeSQLeil for the link... even with and without the indexes, query plan was exactly same... :w00t:[quote][b]CirquedeSQLeil (1/26/2010)[/b][hr]Based on your provided reference, I would agree. However, if one examines execution plans for these two queries and run them together, the query optimizer treats them as the same execution plan and equates both queries to the same cost.Here is a nice resource on the topic:[url]http://sqlinthewild.co.za/index.php/2009/08/17/exists-vs-in/[/url][/quote]Fri, 29 Jan 2010 00:50:26 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mike.anderson 52709 (1/28/2010)[/b][hr][quote][b]CirquedeSQLeil (1/28/2010)[/b][hr][quote][b]mike.anderson 52709 (1/28/2010)[/b][hr][quote][b]mark.ross (1/28/2010)[/b][hr]One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.[/quote]Really? Why?[/quote]When running on a million row table, the execution plans still come up the same. That was a part of my initial testing. One thing that does make a difference is indexing. However, the two will still use the same exec plan.[/quote]Thanks Jason.I'm curious to know what difference indexing makes to the plan, aside from transforming table scans to index scans?[/quote]Well, depending on the index, the table scan COULD be transformed all the way down to an index seek, which I would expect to perform much better than a table scan.Thu, 28 Jan 2010 11:32:55 GMTmark.rosshttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mark.ross (1/28/2010)[/b][hr]Because the 'IN' clause, when run against a large number of rows could, potentially, return a large number of values to check against, whereas the 'EXISTS' clause should still only return a boolean indicating that a particular value exists or not. Also, the 'EXISTS' clause should be able to halt as soon as it finds 1 matching value.Hmm, technically, there might be nothing to stop the 'IN' clause from working that way, too. I suppose the optimizer could be set to 'rewrite' any simple 'IN' clause as an 'EXISTS' clause in order to simplify things. The question is, does it work that way? I suspect not, but advances in the query optimizer are unclear to me, so I really have no answer to that.Even if the optimizer DOESN'T work that way, it could in the future. All you really are supposed to expect is a logical and correct result. The 'how' is largely reserved to the optimizer.So, why, indeed? Gee, and I really did mean to defend my position there. Ahh, well.[/quote]Actually, the optimizer is that advanced already. To be honest, I'd fully expect it to do that kind of rewrite in 2000. Thu, 28 Jan 2010 11:01:49 GMTmike.anderson 52709http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxGail Shaw actually covered this very topic in a blog post. In my first reply in this thread I included the link to that Post. It is a worthwhile read.Thu, 28 Jan 2010 10:55:25 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]CirquedeSQLeil (1/28/2010)[/b][hr][quote][b]mike.anderson 52709 (1/28/2010)[/b][hr][quote][b]mark.ross (1/28/2010)[/b][hr]One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.[/quote]Really? Why?[/quote]When running on a million row table, the execution plans still come up the same. That was a part of my initial testing. One thing that does make a difference is indexing. However, the two will still use the same exec plan.[/quote]Thanks Jason.I'm curious to know what difference indexing makes to the plan, aside from transforming table scans to index scans?Thu, 28 Jan 2010 10:47:00 GMTmike.anderson 52709http://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mark.ross (1/28/2010)[/b][hr]Because the 'IN' clause, when run against a large number of rows could, potentially, return a large number of values to check against, whereas the 'EXISTS' clause should still only return a boolean indicating that a particular value exists or not. Also, the 'EXISTS' clause should be able to halt as soon as it finds 1 matching value.Hmm, technically, there might be nothing to stop the 'IN' clause from working that way, too. I suppose the optimizer could be set to 'rewrite' any simple 'IN' clause as an 'EXISTS' clause in order to simplify things. The question is, does it work that way? I suspect not, but advances in the query optimizer are unclear to me, so I really have no answer to that.Even if the optimizer DOESN'T work that way, it could in the future. All you really are supposed to expect is a logical and correct result. The 'how' is largely reserved to the optimizer.So, why, indeed? Gee, and I really did mean to defend my position there. Ahh, well.[/quote]Nice point/counter-point :-DThu, 28 Jan 2010 10:44:54 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspxBecause the 'IN' clause, when run against a large number of rows could, potentially, return a large number of values to check against, whereas the 'EXISTS' clause should still only return a boolean indicating that a particular value exists or not. Also, the 'EXISTS' clause should be able to halt as soon as it finds 1 matching value.Hmm, technically, there might be nothing to stop the 'IN' clause from working that way, too. I suppose the optimizer could be set to 'rewrite' any simple 'IN' clause as an 'EXISTS' clause in order to simplify things. The question is, does it work that way? I suspect not, but advances in the query optimizer are unclear to me, so I really have no answer to that.Even if the optimizer DOESN'T work that way, it could in the future. All you really are supposed to expect is a logical and correct result. The 'how' is largely reserved to the optimizer.So, why, indeed? Gee, and I really did mean to defend my position there. Ahh, well.Thu, 28 Jan 2010 10:39:25 GMTmark.rosshttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mike.anderson 52709 (1/28/2010)[/b][hr][quote][b]mark.ross (1/28/2010)[/b][hr]One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.[/quote]Really? Why?[/quote]When running on a million row table, the execution plans still come up the same. That was a part of my initial testing. One thing that does make a difference is indexing. However, the two will still use the same exec plan.Thu, 28 Jan 2010 10:31:18 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic854122-2605-1.aspx[quote][b]mark.ross (1/28/2010)[/b][hr]One of the reasons that the query plans are identical could be that the table being selected from in the 'IN' and 'EXISTS' clause is small. Try running them against a multi-million row table and the plans might not be quite the same.[/quote]Really? Why?Thu, 28 Jan 2010 09:31:57 GMTmike.anderson 52709