Reduce Query Resource Use - SQL Server Query Performance Tuning - page 374

As you can see, the EXISTS technique used only three logical reads compared to the 1,246 used by the COUNT(*)
technique, and the execution time went from 29ms to 4ms. Therefore, to determine whether data exists, use the
EXISTS technique.
Use UNION ALL Instead of UNION
You can concatenate the result set of multiple SELECT statements using the UNION clause as follows, as shown in
Figure 19-3 :
SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 934
UNION
SELECT *
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 932;
Figure 19-3. The execution plan of the query using the UNION clause
The UNION clause processes the result set from the two SELECT statements, removing duplicates from the final
result set and effectively running DISTINCT on each query. If the result sets of the SELECT statements participating
in the UNION clause are exclusive to each other or you are allowed to have duplicate rows in the final result set, then
use UNION ALL instead of UNION . This avoids the overhead of detecting and removing any duplicates and therefore
improves performance, as shown in Figure 19-4 .
Figure 19-4. The execution plan of the query using UNION ALL