sql server - UNION versus SELECT DISTINCT and UNION ALL Performance -

is there difference between these 2 performance-wise?

-- eliminate duplicates using union select col1,col2,col3 table1  union select col1,col2,col3 table2  union select col1,col2,col3 table3  union select col1,col2,col3 table4  union select col1,col2,col3 table5        union select col1,col2,col3 table6        union select col1,col2,col3 table7        union select col1,col2,col3 table8         -- eliminate duplicates using distinct     select distinct * (          select col1,col2,col3 table1      union select col1,col2,col3 table2      union select col1,col2,col3 table3      union select col1,col2,col3 table4      union select col1,col2,col3 table5            union select col1,col2,col3 table6            union select col1,col2,col3 table7            union select col1,col2,col3 table8        ) x    

the difference between union , union all union all not eliminate duplicate rows, instead pulls rows tables fitting query specifics , combines them table.

a union statement select distinct on results set.

if select distinct union result set, output equal to union result set.

edit:

performance on cpu cost:

let me explain example:

i have 2 queries. 1 union 1 union all

set statistics time on go  select distinct * (select * dbo.user_logtime union select * dbo.user_logtime) x  go  set statistics time off  set statistics time on go  select * dbo.user_logtime union select * dbo.user_logtime go  set statistics time off 

i did run both in same query window in smss. lets see execution plan in smss:

what happens is, query union all , distinct take cpu cost more query union.

performance on time:

union all:

(1172 row(s) affected)  sql server execution times:    cpu time = 0 ms,  elapsed time = 39 ms. 

union:

(1172 row(s) affected)  sql server execution times:    cpu time = 10 ms,  elapsed time = 25 ms. 

so union better union distinct in performance-wise