Q:

Why does SQL Server SELECT 0 UNION SELECT 0 result in 1 record

Query:
select 0
union
select 0

Result:

0

A:

select 0
union all
select 0

results in 2 records. The difference is that union removes duplicates and union all does not.

A:

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION vs UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
See Examples.

