InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 20:38:35 GMT20http://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI really liked this article - very thorough. EXCEPT seems to be an analog to the Oracle MINUS, so sometimes I like it because I feel like I really know what's happening there (Oracle SQL being my first SQL language). But, my alternative is to use NOT EXISTS, so I wonder if you would consider expanding this to include the EXISTS/NOT EXISTS alternatives? I'm curious to see how they stack up within your example stream.In general, I really liked the approach and illustrations (both pictoral and logical).Thank you --DonnaThu, 22 Mar 2012 07:43:35 GMTdburseyhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI agree. I have found 'excep't very useful when many columns are involved in a table and I want to exclude one entire table from the other. I think you will find in that case except will outperform a join or a not in statement. At least that is what I found on sql2008 r1Mon, 26 Dec 2011 10:00:31 GMTvillerskhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThanks for taking the time to create this post. I have often asked myself if there was any advantage in using some of these commands.Sat, 24 Dec 2011 11:44:58 GMTjgrattanhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI've actually used INTERSECT and EXCEPT quite a bit, not as a replacement for inner joins as some of these comments imply, but to simplify the comparison of two complex resultsets.I've also used INTERSECT as a performance tweak:http://www.real-sql-guy.com/2011/11/stop-performance-problem-with-intersect.htmlSat, 24 Dec 2011 08:27:38 GMTTracy McKibbenhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxMy use of Except, Intersect is to prepare a slowly changing dimension table as written below. This will help me to get the changed address as well as new records. How can i re-write this in sql without EXCEPT operandSELECT CustomerID, Address1, District, Phone1, Phone2,....some 50 col FROM CustAddress_OLTPEXCEPTSELECT CustomerID, Address1, District, Phone1, Phone2,....some 50 col FROM CustAddress_SCDFri, 23 Dec 2011 23:17:12 GMTRagesh Chavarattilhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote]Let me get this straight:You stumbled over some new features.[/quote][b]CORRECT[/b]. In the Microsoft course material[quote]You glanced at books online[/quote]Did a bit more than glance and also read what little was available in 2009. Not sure when the Wikipedia article originated as it doesn't have an original date, only a modified date in 2011.I read BOL and the Microsoft course notes plus the standard Googling. What was available looked very like the "Other editions SQL2005" version on BOL.[quote]You put together some trivial tests against a trivial database[/quote][b]CORRECT[/b]. I did say it was Adventureworks. The whole point of using Adventureworks is that is a publicly available database. Any tests or experiments run on Adventureworks can be easily repeated and therefore verified by developers and DBAs of all levels of ability.[quote]You decide you are now enough of an expert to write an article about it[/quote]No, I was asked by the people being put through the course what these operators were and as I hadn't seen them before I did some digging and basic experimenting.As with any experiment1. Document the experiment so it is repeatable and up for critique.2. Document the results so they are verifiableI don't have the attitude of expecting people to bask in my magnificence. I'm not that arrogant.What I want to achieve in any article1. Pass on what I have learnt to those coming up the ranks being very clear about my methods and experiments2. Provoke response from the many ahead of me in the ranks so that I learn more.Actually the forum responses did teach me somethingselect fieldlist from OriginalSource query Where qualifying criteria is metintersectselect fieldlist from TargettableVery useful in data warehouse reconcilliation.I was arrogant enough to think that I could add some clarity to BOL.[quote]Instead you went on the assumption that if you could find good uses for the features then there were no good uses for them.[/quote]I can't recall making that assumption, it certainly doesn't say anything about assumptions in the article?Again the article and results are up for critique and as per the responses in the forum the true use for these operators was revealled.[quote]I will let you supply the adjective for that attitude.[/quote]I don't have that attitude, never have had, never will have. You are very much holding the wrong end of the stick and no, I am not making the assumption that it is a stick in your hand.Merry ChristmasFri, 23 Dec 2011 11:36:52 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxNote: I am an old curmudgeon who speaks bluntly. Nothing that follows is a personal attack. I just see this as a teachable moment.So:Let me get this straight:You stumbled over some new features.You glanced at books onlineYou put together some trivial tests against a trivial databaseYou decide you are now enough of an expert to write an article about itYou write an article that spends more time comparing the performance of trivial tests to six decimal places than into what they actually do.If you had spent even a few minutes on Google or Wikipedia you would have learned a lot and been in a position to write a useful article.Instead you went on the assumption that if you could find good uses for the features then there were no good uses for them. I will let you supply the adjective for that attitude.To everyone who thought it was a great article:It was a good topic to bring up, but did you read the other comments before you added yours?Fri, 23 Dec 2011 08:32:34 GMTsteven.malonehttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxRecently I ran into the difference between UNION and UNION ALL. Pity you don't mention that.I encountered that a query result of (say) 300 records UNION'd with a query result of zero records could end up in an overall result set of [b][u]less[/u][/b] than 300 records!After some googling and stuff I found out that the function UNION in fact also performs a GROUP BY on all fields. In other words: There will no longer be duplicate records in the end result set. If the duplicates are the result of the UNION or not doesn't matter.A query result of (say) 300 records UNION ALL'd with a query result of zero records gets you a result set of exactly (say) 300 records!Fri, 23 Dec 2011 07:17:19 GMTjaguarxj12lhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxHow do we calculate the overall query cost? i.e .05... will it be shown in the Exec plan itselfFri, 23 Dec 2011 07:05:06 GMTarun.mavhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThanks for the feedback. I haven't tried it with a linked server query.Where possible I base my articles and experiments on the sample databases Adventureworks, Northwind or Pubs simply because they provide a consistent base for people to peer review what I am doing.One of the most important scientific practises is to put up, not only the results but also the experiment itself up for review. It is quite easy to devise a flawed experiment and put too much store in a set of results produced by that experiment.What I attempt to do with my articles is put up a good solid [b][u]starting [/u][/b]point for a discussion. I've learnt some valuable lessons from the feedback given which means that (hopefully) I'm getting as much out of the articles as I hope I am giving.Thu, 11 Aug 2011 13:57:56 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxDear David,Thanks for detailed explanation – I use your article as reference for all these topics. Unfortunately unlike your conclusion for EXPECT case I found situation where NOT IN gives dramatically worst performance:I try to compare local and [u]remote[/u] table. For both cases EXCEPT and LEFT JOIN it gives roughly the same performance. See here:[b][EXCEPT][/b][code="sql"]SELECT ac.Article_CodeFROM relAC acEXCEPT SELECT Code FROM [SRV-DCI-PRAHA].[DCI].[dciowner].Products[/code][img]https://lh6.googleusercontent.com/-39BUjRpxW3w/TkOtdK1-daI/AAAAAAAABLQ/tBNtscGdT2Q/EXCEPT.jpg[/img][font="Courier New"]Local table: Scanning a clustered index, entirely or only a range.Remote Table: Send a SQL query to another than the current SQL Server.Remote Table: Compute new values from existing values in a row.Both: Match rows from two suitably sorted input tables exploiting their sort order.[/font][size="2"][b]Cost = 48,5442[/b][/size][b][LEFT JOIN][/b][code="sql"]SELECT ac.Article_CodeFROM relAC AS ac LEFT JOIN [SRV-DCI-PRAHA].[DCI].[dciowner].Products AS pr ON ac.Article_Code = pr.CodeWHERE pr.Code IS NULL[/code][img]https://lh6.googleusercontent.com/-4aBehpeDSMw/TkOtdbKxjlI/AAAAAAAABLU/X1nt5fgMaCI/LEFT_JOIN.jpg[/img][font="Courier New"]Local table: Scanning a clustered index, entirely or only a range.Remote Table: Send a SQL query to another than the current SQL Server.Remote Table: Compute new values from existing values in a row.Both: Match rows from two suitably sorted input tables exploiting their sort orderBoth: Restricting the set of rows based on a predicate.[/font][size="2"][b]Cost = 48,6391[/b][/size][b][NOT IN][/b]Unfortunately using of NOT IN generates local temporary table for better performance for rewinds and generate Nested Loops for each row of local table for comparison of each row in temporary table. These loops dramatically impair performance of actual query. See Execution Plan here:[code="sql"]SELECT ac.Article_CodeFROM relAC acWHERE ac.Article_Code NOT IN ( SELECT Code FROM [SRV-DCI-PRAHA].[DCI].[dciowner].Products[/code][img]https://lh6.googleusercontent.com/-9wZDYy02SO4/TkOtdmdYPpI/AAAAAAAABLY/iywAOCVulJQ/NOT_IN.jpg[/img][font="Courier New"]Local table: Scanning a clustered index, entirely or only a range.Remote Table: Send a SQL query to another than the current SQL Server.Remote Table: Compute new values from existing values in a row.[/font][b][font="Courier New"][size="2"]Remote Table: Stores the data from the input into a temporary table in order to optimize rewinds.Both: For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.[/size][/font][/b][size="2"][b]Cost = 77826,3!![/b][/size]PetrThu, 11 Aug 2011 04:43:52 GMTpitvaxhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxSorry, it was a bit drastic to say it always gives better performance, I should have said normally gives same performance or better. In the example case of the article I got a 75 % reduction in query time. In my experience the EXISTS scales better than NOT IN when there are many rows in the NOT IN / EXISTS table. For production environments EXISTS is a safer solution than NOT IN. In my opinion. :-)JohanTue, 25 May 2010 07:52:31 GMTjohan.lindellhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]noeld (5/24/2010)[/b][hr][quote][b]johan.lindell (5/24/2010)[/b][hr]Good comparison, David, thanks.From a performance perspective, using NOT IN is "always" slower than using EXISTS. If you try running the below queries[code="sql"]-- INTERSECTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID) -- EXCEPTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID)[/code]instead of the NOT IN, you'll get a reduction of query time by 75 % (on my computer). The bigger the tables and the "result" in the NOT IN-query is the bigger the difference. I always try to avoid NOT IN in a case like this./Johan[/quote]I disagree. NOT IN *could* give you the exact same execution plan given the right indexes and stats. The optimizer in many cases knows best and chooses one plan or another depending on the data![/quote]+1Tue, 25 May 2010 07:39:00 GMTAdam Haineshttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]johan.lindell (5/24/2010)[/b][hr]Good comparison, David, thanks.From a performance perspective, using NOT IN is "always" slower than using EXISTS. If you try running the below queries[code="sql"]-- INTERSECTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID) -- EXCEPTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID)[/code]instead of the NOT IN, you'll get a reduction of query time by 75 % (on my computer). The bigger the tables and the "result" in the NOT IN-query is the bigger the difference. I always try to avoid NOT IN in a case like this./Johan[/quote]I disagree. NOT IN *could* give you the exact same execution plan given the right indexes and stats. The optimizer in many cases knows best and chooses one plan or another depending on the data!Mon, 24 May 2010 08:31:34 GMTnoeldhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI like using the EXCEPT clause because I find it makes the query easier to read. If you have any familiarity with Venn diagrams you can simply focus on understanding each of the two select statements independently and then apply set theory to understand the final result. Nice to know your experiment shows that performance is equivalentMon, 24 May 2010 07:44:22 GMTjim.sinclairhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxGood comparison, David, thanks.From a performance perspective, using NOT IN is "always" slower than using EXISTS. If you try running the below queries[code="sql"]-- INTERSECTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID) -- EXCEPTSELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID=10AND NOT EXISTS ( SELECT CustomerID FROM Sales.SalesOrderHeader WHERE OrderDate>='2004-07-01' AND Sales.Customer.CustomerID = Sales.SalesOrderHeader.CustomerID)[/code]instead of the NOT IN, you'll get a reduction of query time by 75 % (on my computer). The bigger the tables and the "result" in the NOT IN-query is the bigger the difference. I always try to avoid NOT IN in a case like this./JohanMon, 24 May 2010 01:48:04 GMTjohan.lindellhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxWhen INNER JOIN returns duplicate rows then INTERSECT returns distinct values only. Thats why the difference in execution time occurs.Sun, 23 May 2010 23:06:05 GMTpvnidheeshhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI'm sure I once read, here, that WHERE IN tends to be expensive. Your test results challenge this assertion. I wonder if SQL Server 2008 is optimising the execution plan a lot more than it used to.Sun, 23 May 2010 18:14:42 GMTAllen Nugenthttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThanks for the replies everyone. I've learned something new today. That alone makes it a good day. :-)Fri, 21 May 2010 15:45:06 GMTtraughberhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThe other, and maybe the main, reason for putting conditionals into the join is to make the query more efficient. If you blind join a table then all matching rows get included in intermediate results (within the server) only to be filtered out later on during the processing of the WHERE clause. I never understood that until I read some very excellent articles on how JOINS work right here on SQLServerCentral.com We were working on a query that just sucked and often timed out. We joined millions of transactions to thousands of customers only to filer that down to hundreds of results. Moving the conditional into the join then joins only those few hundred transactions to the customer set. One heck of performance boost. Check them out.Fri, 21 May 2010 14:54:37 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]Robert Dudley (5/21/2010)[/b][hr][quote][b]traughberk (5/21/2010)[/b][hr]I have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.SELECT C.CustomerID FROM Sales.Customer AS C LEFT JOIN Sales.SalesOrderHeader AS OH ON C.CustomerID = OH.CustomerID AND OrderDate>='2004-07-01' WHERE OH.CustomerID IS NULL AND C.TerritoryID=10Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.Thanks.Kris[/quote]Kris - the "AND OrderDate >= '2004-07-01'" is filtering under the JOIN function. When you JOIN two tables you define how the two relate to each other. In the example, the "C" table links to the "OH" table through the CustomerID field in each table being equal. You could state that they be different, in which case you link each record in table "C" to all other recrods in table "OH" where the CustomerID's are different. Why you would want to do this is beyond me. I am just illustrating some functionality. So the second parameter in the JOIN linking definition is that on top of the fact that the CustomerID's in table "C" and table "OH" have to be the same, the Order Date must also be greater than or equal to July 1st, 2004. An alternative is to drop the AND statement from the JOIN function, and apply it in the WHERE clause. One way may be more efficient than the other.[/quote]Thanks for the reply. I played around with it for a bit and it seems that it does make a difference where the date filter is placed. If it is placed in the FROM clause you are basically joining table C to a subset of table OH. If you put it in the WHERE clause, you are joining table C to all of table OH. It's similar to the example he wrote following that using the IN statement. (Obviously its similar. That's why he wrote it.)Fri, 21 May 2010 14:48:38 GMTtraughberhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThe reason for the AND OrderDate > '2004-07-01' in the JOIN Clause is so that ALL Customers get returned by the left join. If you moved it to the WHERE Clause then any customers who had not had an order after that date would not show up in the result set.--JimFiveFri, 21 May 2010 14:39:23 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI think a good use of INTERSECT and EXCEPT is when you need to compare many fields. It is much quicker than writing a join or where exists on 5 columns.Fri, 21 May 2010 14:17:39 GMTThe Dr.http://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxThank you for a great article, Dave. I never really thought of using any of these commands as I normally would place data into temp tables to run JOIN queries that produce similar results, and I never fully understood their use until seeing it graphically in your article. I can now do a:[code="sql"]SELECT no_match_id FROM [production].[dbo].[ytd_table]EXCEPTSELECT no_match_id FROM [production].[dbo].[current_month_table][/code]To get a list of ID's in my main table that are no longer in the new month's data I load. There's more complexity to it than what I wrote here, but the EXCEPT command will achieve the same results for my table updates than the lengthy queries I'm using now.And I'm sure I can find many other uses for utilizing both INTERSECT and EXCEPT to replace and simplify my current methodologies in which I use JOINs to match on as many as 60+ columns.Thanks again for a great article!Fri, 21 May 2010 12:26:36 GMTDanny Sheridanhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxNever mind folks. I figured it out myself which is what I should have done in the first place. I guess I'm just lazy this morning. Sorry for the wasted post.Fri, 21 May 2010 10:37:44 GMTtraughberhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]traughberk (5/21/2010)[/b][hr]I have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.SELECT C.CustomerID FROM Sales.Customer AS C LEFT JOIN Sales.SalesOrderHeader AS OH ON C.CustomerID = OH.CustomerID AND OrderDate>='2004-07-01' WHERE OH.CustomerID IS NULL AND C.TerritoryID=10Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.Thanks.Kris[/quote]Kris - the "AND OrderDate >= '2004-07-01'" is filtering under the JOIN function. When you JOIN two tables you define how the two relate to each other. In the example, the "C" table links to the "OH" table through the CustomerID field in each table being equal. You could state that they be different, in which case you link each record in table "C" to all other recrods in table "OH" where the CustomerID's are different. Why you would want to do this is beyond me. I am just illustrating some functionality. So the second parameter in the JOIN linking definition is that on top of the fact that the CustomerID's in table "C" and table "OH" have to be the same, the Order Date must also be greater than or equal to July 1st, 2004. An alternative is to drop the AND statement from the JOIN function, and apply it in the WHERE clause. One way may be more efficient than the other.Fri, 21 May 2010 10:22:27 GMTRobert Dudleyhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxNice article. Thanks for sharing.Fri, 21 May 2010 10:09:38 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI have a question about LEFT JOIN example near the beginning where he starts talking about EXCEPT where he wrote the following SQL.SELECT C.CustomerID FROM Sales.Customer AS C LEFT JOIN Sales.SalesOrderHeader AS OH ON C.CustomerID = OH.CustomerID AND OrderDate>='2004-07-01' WHERE OH.CustomerID IS NULL AND C.TerritoryID=10Can someone please explain the "AND OrderDate>='2004-07-01' " in the FROM clause? I've never seen that before.Thanks.KrisFri, 21 May 2010 09:57:55 GMTtraughberhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxWith all respect, the comparisons made for EXCEPT and INTERSECT in the article are kind of like driving a Ferrari 458 to your corner grocery, and from that concluding that it really doesn't perform any better than a Honda Civic. You aren't really using it to it's full potential ;-)INTERSECT and EXCEPT are intended to be used to compare sets; not as alternatives to joins. You don't really see the benefits until you use them for that purpose. They're great for finding changes or matches in wide reporting tables, for example.Fri, 21 May 2010 09:30:51 GMTcphitehttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI would agree, the advantage of except is in comparing multiple columns. I use it routinely to check view results between development and production where there has been schema changes to make sure I have the view dialed in to result parity. Pretty reliable as long as I am explicit about column (no wild cards), and accommodate an difference in null handling.Fri, 21 May 2010 09:29:34 GMTRobin Riversonghttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI agree with WayneS. I have found many occasions where the INTERSECT and EXEPT functions allow for more efficient comparisons between data sets. In the event that I want to compare entire records with multiple fields between two tables or views, the INTERSECT and EXEPT functions come in handy. The alternative would be to use a cursor and evaluate row by row while housing each field in a variable for the comparison, and having to store the differences in memory or a temp table until the cursor completed....Fri, 21 May 2010 09:00:24 GMTRobert Dudleyhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]Dennis O'Connor (7/21/2009)[/b][hr]My default browser has to be IE here at work, so when I selected this report it was opened in IE. As mentioned by others, the examples were very small blocks with no way to expand them.So I copied the URL into my favorite browser - Safari - and everything displayed correctly. I highly recommend Safari on Windows. It is a rare site that is so oriented to IE that I have to switch to IE.[/quote]You apparently don't do much with SSRS.Fri, 21 May 2010 08:25:28 GMTBrett Phippshttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspx[quote][b]Jeff Moden (7/27/2009)[/b][hr][quote][b]phickey (7/27/2009)[/b][hr]EXCEPT and INTERSECT are especially useful for comparing multiple columns of data where (a) there is no primary key or (b) having the same primary key does not guarantee that the associated data is the same.Typical scenario is regression testing: (edited because somewhere between me and sqlservercentral.com is a stupid proxy that blocks EVERYTHING containing that word beginning with s and ending with elect...)If this gives you the same number of rows as OldTable and NewTable, you know your new process is producing the same data as the old process. Use EXCEPT to examine the rows with differences. If you list the columns, you can narrow down the location of your differences by commenting out various columns.The alternatives for this scenario are (a) joining OldTable to NewTable on every single column, or (b) dumping the data to text files and comparing them.[/quote]Cool trick but if you have two identical tables with identical data, you have much bigger problems. ;-)[/quote]Jeff, you may want to re-read phickey's post. He's talking about regression testing -- making sure your new code doesn't break existing systems. Generally in regression testing, your problems start when you're new code [b]isn't[/b] producing identical data to that from your old code.Fri, 21 May 2010 08:06:54 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxDavid - Thanks for taking the time to dig a bit. Same question came up last week at our office - confirmed my gut. Thanks to the other post about the NULL case - good point.SeanFri, 21 May 2010 07:16:58 GMTSean Fynnhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxNice article!Fri, 21 May 2010 07:12:49 GMTsharath.chalamgarihttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI can't help but ask about your final example using ALL. Perhaps I'm not reading it correctly (likely the case), but using the following two queries:#1 SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > ALL(SELECT TotalDue FROM Sales.TopSales) ORDER BY Sales.TotalDue DESC #2 SELECT * FROM Sales.SalesOrderHeader WHERE TotalDue > (SELECT MAX(TotalDue) FROM Sales.TopSales) ORDER BY Sales.TotalDue DESC it seems as though you are not doing the same comparison. In #1 you are comparing Sales.SalesOrderHeader.TotalDue to every possible value that can be returned from Sales.TopSales. In query #2 you are only comparing against the MAX(TotalDue), which is going to be a single value. It seems to me that query #1 would return more results since you are getting every value that is greater than Sales.TopSales.TotalDue whereas query #2 is only going to return the values that are greater than the biggest Sales.TopSales.TotalDue value. These are not the same result sets.Somebody please tell me I'm reading this wrong.Fri, 21 May 2010 07:02:30 GMTDSquaredhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxJust to concur with some of the other posters, for ETL processes the Except function is very useful for comparing 2 tables with the same structure in different databases and picking up the differences.The other method I sometimes use is a calculated checksum column. However this has the disadvantage that different input values are not guaranteed to produce different checksum values, and it also means adding an ETL layer into the production database which I want to avoid.Fri, 21 May 2010 04:40:27 GMTSamuel Vellahttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxUNION, INTERSECT, and EXCEPT round out basic set operations at the domain level (with tables) instead of the tuple level (with joins). Domain level operations can make code in some instances simpler to maintain. I use them extensively in ETL operations and recovery procedures. I also use UNION ALL to keep duplicates.Thu, 20 May 2010 23:35:08 GMTsjsubscribehttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI found EXCEPT and INTERSECT very useful many times, although ,as is written in article, it makes the same like NOT IN (resp. IN). For me, they are much more elegant and straightforward in use.Thx for articleFri, 14 May 2010 04:37:46 GMTvevoda.ulathhttp://www.sqlservercentral.com/Forums/Topic755353-60-1.aspxI concur with the multiple columns logic and query performance improvement with EXCEPT.I currently use Oracle's MINUS SET Operation (similar to EXCEPT) to compare all columns of ETL source files/tables to production tables to identify changes -very fast and simple to implement when dealing with 100s of sources (CSV,BCP files) via External Tables (similar to OPENROWSET(BULK,...). I will eventually get around to building similar code in MSSQL 2008 t-sql and will update this thread with the performance results.Fri, 11 Sep 2009 09:59:15 GMTwickclan