Caution with EXCEPT

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stephen Tirone » Caution with EXCEPT

30 posts, Page 3 of 3

«««1 2 3

Caution with EXCEPT

Rate Topic

Display Mode

Topic Options

Author

Message

JasonRowland

Posted Monday, January 18, 2010 3:55 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 89, Visits: 684

Nice article. Thank you for taking the time to do this.

Post #849435

susan.falcon

Posted Monday, January 18, 2010 5:33 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 31, 2011 3:48 AM
Points: 24, Visits: 25

Good article. What many people may not realize is that EXCEPT is a SET OPERATOR. The other SET OPERATORS available in MS SQL Server are UNION (ALL) and INTERSECT.

The purpose for using a set operator is to combine the results of multiple select statements into a single result set.

UNION returns all distinct rows from all statements.
UNION ALL returns all rows, including duplicates from all statements.
INTERSECT returns only rows that exist in all statements.

As you pointed out, any time you use a set operator, the number and order of the columns must be the same in all queries and the data types must be compatible.

Post #849455

Clive Chinery

Posted Monday, January 18, 2010 11:00 PM

Ten Centuries

Group: General Forum Members
Last Login: Friday, September 02, 2011 3:22 AM
Points: 1,227, Visits: 154

Thank you for the article.

Post #849515

SQLRNNR

Posted Monday, January 18, 2010 11:52 PM

SSCoach

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:07 PM
Points: 18,733, Visits: 12,332

Thank you for this article. It is a nice demonstration of another reason why one should avoid the use of

Select *

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #849523

jdurandt

Posted Tuesday, January 19, 2010 10:59 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145

Hi Jeff

I do not have the code handy at present, and am too sick right now to recreate it Smile

But in my case I was filtering rows out based on a primary key, so I was using except instead of:
- Select ... from tab1 where tab1.key not in (select key from tab2)
- Select ... from tab1 left outer join tab2 on tab1.key = tab2.key where tab2.key is null

It was faster than both on my test system, using a dbcc freeproccache, dbcc dropcleanbuffers and checking the total cost of the queries.

Post #849942

Jeff Moden

Posted Tuesday, January 19, 2010 6:07 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 32,906, Visits: 26,792

Thanks for the feedback and I hope you get well soon.

As a side bar, if "total cost" is coming from the execution plan, then possibly a bad test. I can show you code wth two queries of 0% and 100% yet when they run, exactly the opposite is true even though the actual execution plan says it didn't happen that way. Tongue

--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."

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 #850169

SQLista

Posted Wednesday, January 20, 2010 1:00 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 05, 2012 3:56 PM
Points: 265, Visits: 78

I think it's worth mentioning that there's also an INTERSECT function for finding the overlapping rows. I spend many, many hours validating data and even I don't use intersect nearly as much as except, it can still be extremely useful.

Great article highlighting an under-used function!

Post #850779

CyclingRabbit

Posted Wednesday, February 10, 2010 1:27 AM

Valued Member

Group: General Forum Members
Last Login: Monday, October 29, 2012 9:48 PM
Points: 57, Visits: 217

Great article. Thanks!

Post #862998

san_kan1gb

Posted Sunday, January 02, 2011 12:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 15, 2011 3:28 AM
Points: 2, Visits: 26

Just an info,
not to add a column of type timestamp, because it will always be different.

Thanx on the good article.

Post #1041602

Ion Freeman

Posted Friday, November 04, 2011 11:51 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:38 AM
Points: 55, Visits: 122

Yes, Except seems like the hardest of the join operators for the query engine. Wouldn't the two-way compare

SELECT * FROM
 (
 SELECT * FROM Staging.dbo.WIDGET
 EXCEPT
 SELECT * FROM Production.dbo.WIDGET
 ) LEFT_DIFFS
 UNION
 SELECT * FROM
 (
 SELECT * FROM Production.dbo.WIDGET
 EXCEPT
 SELECT * FROM Staging.dbo.WIDGET
 ) RIGHT_DIFFS

Work better as

SELECT * FROM
 (
 SELECT * FROM Staging.dbo.WIDGET
 UNION ALL
 SELECT * FROM Production.dbo.WIDGET
 ) BOTH_SETS
 EXCEPT
 SELECT * FROM
 (
 SELECT * FROM Production.dbo.WIDGET
 INTERSECTION
 SELECT * FROM Staging.dbo.WIDGET
 ) INTSCT

Post #1200807

« Prev Topic | Next Topic »

30 posts, Page 3 of 3

«««1 2 3

Permissions