Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Caution with EXCEPT Expand / Collapse
Author
Message
Posted Monday, January 18, 2010 3:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 12, 2014 1:44 PM
Points: 89, Visits: 793
Nice article. Thank you for taking the time to do this.
Post #849435
Posted Monday, January 18, 2010 5:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, January 18, 2010 11:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Monday, January 18, 2010 11:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 20,460, Visits: 14,086
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


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
Posted Tuesday, January 19, 2010 10:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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

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
Posted Tuesday, January 19, 2010 6:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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.


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #850169
Posted Wednesday, January 20, 2010 1:00 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:35 PM
Points: 265, Visits: 84
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
Posted Wednesday, February 10, 2010 1:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, October 29, 2012 9:48 PM
Points: 57, Visits: 217
Great article. Thanks!
Post #862998
Posted Sunday, January 02, 2011 12:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 31, 2013 12:31 AM
Points: 2, Visits: 27
Just an info,
not to add a column of type timestamp, because it will always be different.

Thanx on the good article.
Post #1041602
Posted Friday, November 04, 2011 11:51 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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 »

Add to briefcase «««123

Permissions Expand / Collapse