Earlier I removed duplicate rows with ROW_NUMBER() and a CTE. This time I show a tricky alternative: removing duplicate rows with the traditional UNION operator.
SQL SERVER – Puzzle – Solution – Why Does UNION ALL Work but UNION Produces Error?
Here is the answer to my puzzle: why does UNION ALL work but UNION produces an error with a TEXT column? It comes down to how UNION removes duplicates.
SQL SERVER – Puzzle – Why Does UNION ALL Work but UNION Produces Error?
A puzzle with TEXT columns: UNION ALL works fine, but UNION produces error. Can you tell why? The hint is in how UNION removes duplicates.
SQL SERVER – Select Unique Data From a Column Exist in Two Different Table
A reader wanted to Select Unique Data from a column in two tables, keeping only values found in just one of them. Here is the query that does it.
Interview Question of the Week #017 – Performance Comparison of Union vs Union All
An interviewer asked which is faster, Union vs Union All. My answer: they do different jobs, so comparing them is like comparing apples and oranges.
SQL SERVER – UNION With TEXT DataType Returns Error but and UNION ALL Works – Reasons
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.