Why does UNION with TEXT datatype return an error while UNION ALL works? It comes down to how UNION removes duplicate rows. Here is the reason.
SQL SERVER – UNION and UNION ALL with TEXT DataType – Observation
UNION fails on a TEXT column with Msg 5335, yet UNION ALL with TEXT datatype works fine. Here is the code so you can try the difference yourself.
UNION vs UNION ALL: Reading the Operators Each One Adds to a Plan
Compare UNION vs UNION ALL through actual plans, duplicate-removal operators, memory grants and reads, then prove when branches cannot overlap.
Set Operator Rules: Column Names, Types and ORDER BY With UNION
Why did my UNION change its headers, fail on a word, or sort wrong? Small examples show each rule and the error it produces.
SQL SERVER – Simple Puzzle with UNION Part 5
In Puzzle with UNION Part 5, the final part, two almost identical queries return the same answer, yet their execution plans are very different. Why?
SQL SERVER – Simple Puzzle with UNION Part 4
Puzzle with UNION Part 4 is for readers who want to think: three queries using UNION, a subquery and NOT EQUAL TO return the same result. Compare their plans.
SQL SERVER – Simple Puzzle with UNION Part 3
Simple Puzzle with UNION Part 3 has three T-SQL scripts on the UNION clause, and only one is correct. Can you spot it without running them in Management Studio?