将两个表Segment153，Segment141取差后结果同步到Segment101和Segment102中，分别采用了临时表和Merge两种方法。用merge语句一句就完成。用临时表要创建临时表，插入结果到临时表中，对比删除目标表中多出的数据，对比删除临时表中重复的数据，然后再将临时表数据插入到目标表中，删除临时表。多做了多次表扫描和哈希匹配。

结果通过相同数据下的执行计划对比一看，发现反而merge效率要低一点点。

在万条数据级别下，两种方式多次执行均衡下来相当

在十万数据级别下，Merge效率要更多的低于临时表方式。这次测试下,merge大概占总共执行的60%-70%。

代码如下

--非merge方式
create table #result (
   FK_Customer char(20) NOT NULL primary key,
);
	--TRUNCATE TABLE #result;
INSERT INTO #result(FK_Customer) SELECT distinct fk_customer FROM (SELECT a0.FK_Customer FROM Segment153 a0 WITH (NOLOCK) LEFT OUTER JOIN Segment141 a1 WITH (NOLOCK) ON a0.FK_Customer = a1.FK_Customer WHERE a1.FK_Customer IS NULL) a
DELETE t FROM segment101 t LEFT OUTER JOIN #result r ON t.FK_Customer = r.FK_Customer WHERE r.FK_Customer IS NULL;
DELETE r FROM #result r INNER JOIN segment101 t ON t.FK_Customer = r.FK_Customer;
INSERT INTO segment101 (FK_Customer, ModifyTime, GcFlag) SELECT FK_Customer, GETDATE(), 0 FROM #result;
DROP TABLE #result;
--Merge方式
MERGE Segment102 a
USING (SELECT distinct a0.FK_Customer FROM Segment153 a0 WITH (NOLOCK) LEFT OUTER JOIN Segment141 a1 WITH (NOLOCK) ON a0.FK_Customer = a1.FK_Customer WHERE a1.FK_Customer IS NULL) b
ON a.FK_Customer = b.FK_Customer
WHEN NOT matched THEN
	INSERT (FK_Customer, ModifyTime, GcFlag) VALUES (b.FK_Customer, GETDATE(), 0)
WHEN NOT matched BY source THEN
	DELETE;
	

从执行计划上来看merge主要开销在排序上，我不由恶意的猜测是不是merge每插入删除一条记录都会立即执行到目标表的，而不是数据堆放到一个临时集合中最后一次合并的。

表结构代码在下面

CREATE TABLE [dbo].[Segment101](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FK_Customer] [char](20) NULL,
	[ModifyTime] [datetime] NULL,
	[GcFlag] [bit] NULL,
 CONSTRAINT [PK__Segment1__3214EC071AC9DC03] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_Segment101] ON [dbo].[Segment101] 
(
	[FK_Customer] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

运行环境是sqlserver2008r2。