--前提：表中不能有text、ntext、image、cursor 数据类型的字段。

用CheckSum()最简单：

select * from A where checksum(*) not in (select checksum(*) from B)

------解决方案--------------------
SQL code
-->差集
select * from 表A
except
select * from 表B

--交集
select * from 表A
intersect
select * from 表B

------解决方案--------------------

SQL code
 
/******************************************************************************/
/*回复：20080519007总：00027                                                   */
/*主题：数据比对                                                                         */
/*作者：二等草                                                                           */
/******************************************************************************/

set nocount on

--数据--------------------------------------
 
create table [a] ([xh] int,[ksh] varchar(4),[xm] varchar(3))
 insert into [a] select 1,'0102','aac'
 insert into [a] select 2,'0203','bbf'
 insert into [a] select 3,'0103','dse'
 insert into [a] select 4,'0104','sds'
 
create table [b] ([xh] int,[ksh] varchar(4),[xm] varchar(3))
 insert into [b] select 1,'0102','aac'
 insert into [b] select 2,'0204','bbf'
 insert into [b] select 3,'0105','dsd'
 insert into [b] select 5,'0106','rtr'
go

--代码--------------------------------------
select *,diff='a-xh,ksh,xm' from a t where not exists(select 1 from b where xh=t.xh or ksh = t.ksh or xm = t.xm)
union 
select t.*,'b-xh,ksh,xm' from b t where not exists(select 1 from a where xh=t.xh or ksh = t.ksh or xm = t.xm)
union 
select t.*,'ksh,xm' from a t,b where t.xh=b.xh and t.xm<>b.xm and t.ksh <> b.ksh
union
select t.*,'xh,xm' from a t,b where t.ksh=b.ksh and t.xm<>b.xm and t.xh <> b.xh
union
select t.*,'xh,ksh' from a t,b where t.xm=b.xm and t.xh<>b.xh and t.ksh <> b.ksh
union
select t.*,'xh' from a t,b where t.ksh=b.ksh and t.xm = b.xm and t.xh <> b.xh
union
select t.*,'ksh' from a t,b where t.ksh<>b.ksh and t.xm = b.xm and t.xh = b.xh
union
select t.*,'xm' from a t,b where t.ksh=b.ksh and t.xm <> b.xm and t.xh = b.xh

go

/*结果--------------------------------------
xh          ksh  xm   diff        
----------- ---- ---- ----------- 
2           0203 bbf  ksh
3           0103 dse  ksh,xm
4           0104 sds  a-xh,ksh,xm
5           0106 rtr  b-xh,ksh,xm
--清除------------------------------------*/
drop table a,b