Well. Most of you know the diffrence between the two.

http://blog.sqlauthority.com/2007/03/10/sql-server-union-vs-union-all-which-is-better-for-performance/

http://www.codethinked.com/post/2007/11/UNION-versus-UNION-ALL2c-lessons-in-minutiae.aspx

Here is one of the differences that is not known by newbies When you use UNION, you cant include any column of type TEXT and NTEXT. But it is possible if you use UNION ALL

 

Declare @test table(i int, text_col text)
insert into @test 
select 1,'some test string'union all
select 2,'some other test string' 

--Error For UNION
select i,text_col from @test where i=1
union 
select i,text_col from @test where i=1

--No Error for UNION ALL

select i,text_col from @test where i=1
union all
select i,text_col from @test where i=1