SQL Server工具TableDiff使用场景

1. 两个比较的表数据差异非常大：

       这种情况效率差是肯定的，两个几百万的表，差异非常大，TableDiff光生产差异脚本都要很久时间，效率能

       高得起来吗？

 

       解决方法：如果是差异比较大的大表，最好的方法就是用BCP将目的端的表数据重建，TableDiff搞不定；

 

   2. 两个比较的表中，存在不适宜比较的字段类型：

       xml\timestamp\binary\varbinary\ntext\text\image 这些类型要不就是本身很复杂（如：text类型），

      要不就是两字段根本不可能相等（如：timestamp类型），第一种比较起来肯定效率差，第二种TableDiff

      几乎需要把整个表中的这个字段全部生成相关的差异脚本（几百万呀），能快起来就怪了，基本上比不出来结果；

 

      解决方法：在做TableDiff的时候，需要把这些特殊字段去掉，我们可以在这个表的基础上，创建一个不包含

      这些字段的视图，然后再用TableDiff比较两个视图，这样速度能快很多。

 

   3. 两个表中有比较多的NULL值：

      我们知道，数据库中NULL值是不能比较的（只能用IS NULL来判断），如果两个比较的表中有很多字段可以为

      NULL，而这些NULL值很多的话，TableDiff会认为这些值都不相等，这样生成的差异脚本也会暴多，效率就可

      想而知了；

 

      解决方法：做TableDiff的时候，想将NULL值替换成固定的值，这样比较起来就要快很多了，同样是通过视图来

      比较。

 

     因为2，3两种情况比较多见，所以写了个通用的将表生成视图的过程，如下：

     1. 过滤掉xml\timestamp\binary\varbinary\ntext\text\image的字段；
     2. 替换掉NULL值
     3. tablediff 生成脚本后，注意将默认值替换成NULL

 

 

 

--select * from sys.types



declare @table_name nvarchar(100)
set @table_name='t_user_money'
if exists (select 1 from sys.tables where name=@table_name)
begin
declare @table_info table(id int identity(1,1) not null,info nvarchar(300))
insert into @table_info select 'create view V_'+@table_name+'_Diff'
insert into @table_info select 'as' 
insert into @table_info
select 'select '
insert into @table_info
select case when isnullable=0 then tablename+','       else tablename+','+default_value+') as '+name+','  end 
from (select a.isnullable,a.name,b.xtype,case when a.isnullable =0 then '  ['+a.name+']' else '  ISNULL('+a.name end as tablename,case when b.xtype in (48,52,56,127) then '100' 
--tinyint\smallint\int\bigint     when b.xtype in (59,60,62,106,108) then '1.01'
--real\money\float\decimal\numeric     

when b.xtype=40 then '''1900-01-01''' --date     
when b.xtype=41 then '''01:00:01''' --time     
when b.xtype=58 then '''1900-01-01 01:01:01''' --smalldatetime     
when b.xtype=61 then '''1900-01-01 01:01:01.001''' --datetime   
when b.xtype in(60,62) then '0.0' --money 
when b.xtype in(104,108) then '1' --money     
else '''@#''' end as default_value
from syscolumns a,systypes b   where a.id = OBJECT_ID(@table_name) and a.xtype = b.xusertype    )a where xtype not in(34,35,99,165,173,189,241)
--xml\timestamp\binary\varbinary\ntext\text\image
update @table_info set info=SUBSTRING(info,1,len(info)-1) where id=(select MAX(id) from @table_info)
insert into @table_info values('from dbo.['+@table_name+'] with(nolock)')
select info from @table_info
end
else  
print('Can not find the table '+@table_name)

 然后运行tablediff命令：

tablediff -sourceserver "localhost" -sourcedatabase "kk" -sourceschema "dbo" -sourcetable "Tmp" -sourceuser "sa" -sourcepassword "1" -destinationserver "localhost" -destinationdatabase "kk" -destinationschema "dbo" -destinationtable "Tmp_Check" -destinationuser "sa" -destinationpassword "1" -f "c:\Test.sql"