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 1 of 3

1 2 3 »»»

Caution with EXCEPT

Rate Topic

Display Mode

Topic Options

Author

Message

zintp

Posted Saturday, January 16, 2010 6:28 PM

SSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 12:43 PM
Points: 206, Visits: 296

Comments posted to this topic are about the item Caution with EXCEPT

Post #848796

mohd.nizamuddin

Posted Sunday, January 17, 2010 11:58 PM

Old Hand

Group: General Forum Members
Last Login: Friday, July 29, 2011 4:14 AM
Points: 318, Visits: 198

Very nice and informative article...
This is what we do in real life as well (Use of SELECT *). Even though we hear from everybody that to avoid SELECT * always.
So, if we make practice of writing SELECT col1, col2, col3,.... then we would not get that problem.
Enjoy.

Post #848983

Tobar

Posted Monday, January 18, 2010 4:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 167, Visits: 550

Another issue with select * is that if you have identity keys and the tables are independently generated (as in deve and staging) and an identical data entry has separate "identities" on the different boxes select * will always return all the rows.

Instead of the Union I usually just

select * from A
except
select * from B

select * from B
except
select * from A

then you can it is easier to know where the "differences" originate.

<><
Livin' down on the cube farm. Left, left, then a right.

Post #849082

Tobar

Posted Monday, January 18, 2010 4:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 167, Visits: 550

mohd.nizamuddin (1/17/2010)

Sorry for 2 responses. More thoughts occurred as the coffee kicked in.

I "always" Wink

start to worry when someone used the phrase "always", there are always reasons why always is never appropriate. (Same goes with "never"). Sometimes it is just the thing to do.

When needing to "select col1, col2, etc." I use the Management Studio tactic of "Script table as->Select into->new query window" and then just hack out any columns I do not want. It "Always" works for me.

<><
Livin' down on the cube farm. Left, left, then a right.

Post #849085

Stephen Hirsch

Posted Monday, January 18, 2010 5:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 3:53 PM
Points: 127, Visits: 85

Here's a better way:

SELECT 'IN A NOT B' location, a, b, c, d, ...
FROM tablea
EXCEPT
SELECT 'IN A NOT B' location, a, b, c, d, ...
FROM tableb
UNION ALL
SELECT 'IN B NOT A' location, a, b, c, d, ...
FROM tableb
EXCEPT
SELECT 'IN B NOT A' location, a, b, c, d, ...
FROM tablea

No parentheses this way.

Post #849097

ChrisM@Work

Posted Monday, January 18, 2010 6:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 5,609, Visits: 10,971

Stephen Hirsch (1/18/2010)

Wait a minute! If ever there was a case for parentheses, it's right here - what was the intent of the programmer? Parentheses make the intent self-documenting:

SELECT 'IN A NOT B' location, a, b, c, d, ...
FROM tablea
EXCEPT
(SELECT 'IN A NOT B' location, a, b, c, d, ...
FROM tableb
UNION ALL
SELECT 'IN B NOT A' location, a, b, c, d, ...
FROM tableb
EXCEPT
SELECT 'IN B NOT A' location, a, b, c, d, ...
FROM tablea)

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #849113

Tobar

Posted Monday, January 18, 2010 7:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 167, Visits: 550

The programmer "intended" it to work the way the programmer thought they were programming it to work. BigGrin

[this said in the universal sense]

<><
Livin' down on the cube farm. Left, left, then a right.

Post #849145

ChrisM@Work

Posted Monday, January 18, 2010 7:14 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:00 AM
Points: 5,609, Visits: 10,971

Tobar (1/18/2010)

The programmer "intended" it to work the way the programmer thought they were programming it to work. BigGrin

[this said in the universal sense]

LOL you used to work there too!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #849147

bugmenot-573553

Posted Monday, January 18, 2010 7:39 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, September 28, 2010 4:16 AM
Points: 71, Visits: 5,439

We have seen poor performance with EXCEPT on large data sets.

Post #849163

Sam Jumper

Posted Monday, January 18, 2010 8:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 12:49 PM
Points: 4, Visits: 53

Thanks for the article, I've never used EXCEPT because I didn't realize it existed.

On the point of poor performance with large datasets, how can we expect anything else? Probably too many columns to put in a covering index. SQL has to compare every column to every column, it's going to either table scan or bookmark lookup every row. Give your SQL Server a break! I suppose when I think about it, you could include a WHERE statement to break your large datasets in to smaller ones if appropriate to what you are looking for.

Post #849182

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions