The usage and semantics of the EXCEPT logical operation
Questions tagged [except]
46 questions
33
votes
2 answers
Using EXCEPT in a recursive common table expression
Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion..
with cte as (
select *
from (
values(1),(2),(3),(4),(5)
) v (a)
)
,r as (
select a
from cte
…
Tom Hunter
- 2,179
- 3
- 16
- 11
20
votes
4 answers
Easily show rows that are different between two tables or queries
Imagine you have two different tables/queries that are supposed to have/return identical data. You want to verify this. What's an easy way to show any unmatched rows from each table just like the example below, comparing every column? Assume there…
ErikE
- 4,355
- 4
- 29
- 39
18
votes
3 answers
EXCEPT operator vs NOT IN
The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?
Does it do the same? I would like a simple explanation with an example.
Heisenberg
- 1,505
- 5
- 18
- 31
16
votes
5 answers
SQL Server Linked Server performance: Why are remote queries so expensive?
I have two database servers, connected via Linked Servers. Both are SQL Server 2008R2 databases, and the linked server connection is made via a regular "SQL Server" link, using the current login's security context. The linked servers are both in the…
vstrien
- 547
- 2
- 6
- 15
15
votes
4 answers
Quick way to validate two tables against each other
We're doing an ETL process. When all is said and done there are a bunch of tables that should be identical. What is the quickest way to verify that those tables (on two different servers) are in fact identical. I'm talking both schema and data. …
RThomas
- 3,446
- 6
- 30
- 48
14
votes
1 answer
Should NOT IN be avoided?
Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (example).
Is there any truth to that?
Is there, for…
Heinzi
- 3,210
- 2
- 32
- 43
13
votes
3 answers
Identifying which values do NOT match a table row
I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.
To better explain, here's what I would do now, to check which IDs of the list "1, 2, 3, 4" do not exist in a table:
SELECT *…
NReilingh
- 785
- 2
- 9
- 27
12
votes
2 answers
An efficient way to compare two large data sets in SQL
Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations.
The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and end May 2014 (result = 450K lines). The 2nd data…
Pierre Pretorius
- 127
- 1
- 1
- 7
11
votes
1 answer
What is the algorithm behind the EXCEPT operator?
What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare?
David Lozinksi ran a study, SQL: Fastest way to insert new records where one doesn’t already…
user162241
9
votes
2 answers
EXCEPT & INTERSECT: Purpose of Passive Projection in Logical Plan
The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT.
What is the purpose of the projection? e.g. Is there a different EXCEPT query where the outer projection would specify…
Paul Holmes
- 889
- 7
- 16
9
votes
1 answer
What is most efficient way to compare two large result sets in SQL Server 2012
Current advice for the most efficient way to compare two large result/row sets seems to be to use the EXCEPT operator. This self contained SQL script below gets very inefficient as row sizes increase (change @last values). I have tried to find…
Will Healey
- 93
- 1
- 4
8
votes
2 answers
How do I replace this where clause with a join?
Typically when I see SQL that uses something like:
select * from employees where epmloyeeTypeId in (select id from type where name = 'emp')
I replace the where with this:
select e.* from employees e
inner join type t on t.id=e.epmloyeeTypeId and…
kacalapy
- 2,062
- 3
- 27
- 36
6
votes
2 answers
Unusual column comparisons and query performance
We have some consultants working on expanding an inhouse data warehouse. I was doing a code review and ran across this pattern in all of the load procs:
MERGE [EDHub].[Customer].[Class] AS TARGET
USING (
SELECT
…
Bob Probst
- 267
- 1
- 2
- 7
6
votes
1 answer
T-SQL MINUS operator
These are the tables I have created and inserted values in it:
CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
Customer_Name VARCHAR(30) NOT NULL
)
CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
DVD_Name…
learningIT
- 155
- 1
- 3
- 7
6
votes
3 answers
Contained DB Collation error
When changing a database to partially contained I am getting the following error:
Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "Latin1_General_100_CI_AS_KS_WS_SC" in the EXCEPT operation.
Errors were encountered in the…
Tom
- 1,569
- 6
- 29
- 43