SqlServer中的UNION操作符在合併數據時去重的原理以及UNION運算符查詢結果預設排序的問題

本文出處：http://www.cnblogs.com/wy123/p/7884986.html 

周圍又有人在討論UNION和UNION ALL，對於UNION和UNION ALL，網上說的最多的就是性能問題（實在不想說出來這句話：UNION ALL比UNION快）
其實根本不想炒UNION和UNION ALL這碗剩飯了，
每次看到網上說用這個不用那個，列舉的一條一條的那種文章，只要看到說UNION ALL比UNION性能好的就……

對於合併的結果集，UNION是去重的，UNION ALL是不去重的，去重與不去重是兩個目的，分別由UNION和UNION ALL實現
兩個作用（功能）不同的東西，放一起比性能有什麼意義？
這種問題真的是無聊至極，就好比“足球場上的某個中後衛和某個前腰哪個能力更強”一樣沒有可比性，
他們的作用本身就是不同的，難道說中後衛能力不行，把他撤下來，用一個牛逼的前腰球員替代中後衛，或者是前腰能力不行，撤下他用牛逼的中後衛替代？
這是在功能上的區別，至於性能，我個人認為對比起來沒有任何意義。
如果非要放一起比的話，做同樣的數據合併，
UNION因為要去重，相對UNION ALL來說，（相對）當然會耗費更多的資源（耗費的資源多少跟性能無關，做的事情多，當然需要更多的資源）
但是一定要弄清楚，合併數據的時候，到底要不要去掉重覆數據，這是最終結果對與錯的問題，不是性能問題！

這裡不討論UNION和UNION ALL的性能了，
從另外一個點入手來發起問題
UNION與UNION ALL最大的區別就是UNION會去重，那麼問題就來了，這個去重是怎麼實現的？去重會對查詢的預設順序集產生什麼影響？

UNION去重的實現

測試一下UNION運算符去重的實現原理

create table TestUnion1
(    
    
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100)
);
create table TestUnion2
(
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100)
);

insert into TestUnion1 values (500,9,'aaa')
insert into TestUnion1 values (700,3,'ccc')
insert into TestUnion1 values (200,7,'eee')


insert into TestUnion2 values (300,2,'bbb')
insert into TestUnion2 values (800,8,'ddd')
insert into TestUnion2 values (100,5,'fff')

--TestUnionALL1和TestUnionALL2中相同的數據
insert into TestUnion1 values (600,6,'xxx')
insert into TestUnion2 values (600,6,'xxx')

UNION在去重的過程中，使用的執行計劃是Merge Join，UNION ALL是不去重的，同樣步驟對應的執行計劃是Concatenation

這裡UNION的去重動作是通過merge實現，這裡的merge join並不是表與表之間的merge join
這裡可以看出來，UNION產生的merge與 inner join產生的Merge的作用是有差異的

對於UNION的去重的這一動作，去當然不是說只有merge join一種，這裡只不過是兩個結果的數據都剛好有序才採用merge join來去重罷了

如果查詢欄位的順序的第一個欄位是聚集索引（或者主鍵）,，正如上文提到的，UNION的雙方就會以merge的方式區中
如果查詢欄位的順序非聚集索引，UNION的過程是現將兩個結果集合併起來（上文提到的Concatenation），然後再做sort排序去重

UNION之後結果集的最終排序結果

UNION之後結果集的最終排序結果跟查詢欄位的順序有關，
如果查詢欄位的順序的第一個欄位是聚集索引（或者主鍵），正如上文提到的，UNION的雙方就會以merge的方式區中
如果查詢欄位的順序的第一個欄位是非聚集索引欄位，UNION的過程是現將兩個結果集合併起來（上文提到的Concatenation），然後再做sort排序去重
如下的實例能說說明這個問題，當查詢欄位的順序發生變化之後，兩者的執行計劃完全不一致。

或者再看一個case，當Name在最前面的時候，最終的結果就是按照name排序。

 　　可能有人會懷疑是不是數據量太小了，是不是巧合，這裡可以加大測試資料庫，在查詢條件中，讓非聚集索引參與到運算之中

create table TestUnion1
(    
    
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100),
    CreateDate datetime
);


create table TestUnion2
(
    Id1 INT PRIMARY KEY,
    Id2 tinyint,
    Name varchar(100),
    CreateDate datetime
);


begin tran
    declare @i int = 0
    while @i<1000000
    begin
        insert into TestUnion1 values (@i,rand()*200,newid(),getdate()-rand()*1000)
        insert into TestUnion2 values (@i,rand()*200,newid(),getdate()-rand()*1000)
        set @i=@i+1
    end
commit


create index idx_CreateDate on TestUnion1(CreateDate)
create index idx_CreateDate on TestUnion2(CreateDate)

參考下圖，一旦查詢結果集不是按照查詢欄位聚集索引排序的話，
比如這裡走的是createDate時間欄位的索引，執行計劃都是先按照普通的方式合併結果集，也即Concatenation
然後在利用Sort(Distinct)的方式排序去重，對於去重的結果的最終的排序，跟查詢結果的第一個欄位有關，且結果總是按照查詢的第一個欄位排序的。

　　換一種查詢欄位的順序方式，看一下結果，仍舊是按照查序列的第一個欄位排序的

　　UNION運算符在去重的時候，
　　如果查詢欄位的第一個欄位是聚集索引，那麼會用merge join的方式合併+去重。
　　如果查詢欄位的第一個欄位不是非聚集索引，那麼首先會將兩個（或者多個）結果集進行普通的合併，最後通過Sort Distinct的方式去重。 　
　　且UNION運算之後的預設排序方式，受查詢欄位前後的方式影響。　

總結：

　　UNION和UNION ALL的作用是不一樣的，放在一起比性能沒有任何意義，真不想趟這趟渾水。
　　合併結果集，需要去重就用UNION，不需要去重就用UNION ALL，如果兩個結果集中沒有重覆的結果集，就用UNION ALL，
　　這純粹是需求驅動的，而不是UNION和UNION ALL的性能問題。

多撤一句：
曾經大晚上接到一個面試電話，沒有任何開場白，第一句話是“我們電話面試一下可以嗎”，答曰可以，第二句話就是“UNION和UNION ALL的區別是什麼，有沒有性能差異”。
真的不希望再去對UNION和UNION ALL的性能上做討論。