Explaining Set Operators :: SQL Server 2012
You are presenting a session about set operators in a conference. At the end of the session,
you give the audience an opportunity to ask questions. Answer the following questions presented
to you by attendees:
- In our system, we have a number of views that use a UNION operator to combine
disjoint sets from different tables. We see performance problems when querying the
views. Do you have any suggestions to try and improve the performance?
- Can you point out the advantages of using set operators like INTERSECT and EXCEPT
compared to the use of inner and outer joins?
Answers
- The UNION operator returns distinct rows. When the unified sets are disjoint, there
are no duplicates to remove, but the SQL Server Query Optimizer may not realize it.
Trying to remove duplicates even when there are none involves extra cost. So when the
sets are disjoint, it's important to use the UNION ALL operator and not UNION. Also,
adding CHECK constraints that define the ranges supported by each table can help the
optimizer realize that the sets are disjoint. Then, even when using UNION, the optimizer
can realize it doesn't need to remove duplicates.
- Set operators have a number of benefits. They allow simpler code because you don't
explicitly compare the columns from the two inputs like you do with joins. Also, when
set operators compare two NULLs, they consider them the same, which is not the case
with joins. When this is the desired behavior, it is easier to use set operators. With join,
you have to add predicates to get such behavior.