Another back to basics puzzle with UNION: run three queries using UNION ALL, ORDER BY and DISTINCT, then compare their execution plans.
SQL SERVER – Simple Puzzle with UNION
Try this simple puzzle with UNION and ORDER BY. Guess what each of the three queries returns, and see why one of them fails with Msg 108 and Msg 104.
Rewriting OR Conditions So the Index Gets Used
Rewrite OR conditions with separate seeks, preserve duplicate and NULL behavior, and compare plans before choosing UNION or recompilation.
UNION vs UNION ALL in SQL Server: What Each One Does
UNION vs UNION ALL comes down to duplicates: UNION removes them and UNION ALL keeps every row. This post tests both on two small store tables. It also covers sorting, a total row at the bottom, mixed operators and data type rules.
SQL SERVER – Return Specific Row to at the Bottom of the Resultset – T-SQL Script – Part 2
Part 2 on how to return a specific row at the bottom of the resultset. Earlier I used CASE in ORDER BY, and this time I show the UNION ALL method.
Subtotals and Grand Totals With ROLLUP and GROUPING SETS
Build region, product, and grand totals with ROLLUP, CUBE, and GROUPING SETS, then label subtotal rows and compare plans.
SQL SERVER – UNION Not Allowed but OR Allowed in Index View – Limitation of the View 6
UNION not allowed in an indexed view can surprise you. Limitation of the view 6 shows the script that fails and how the OR operator is allowed instead.