資料庫整筆ROW比對

在資料庫操作時, 有時會需要比對資料表的內容是否一致, 或是某些資料的某些欄位是否一樣, 若是使用 SQL 指令來逐行比對, 會是個比較麻煩的操作方式, 使用 CHECKSUM 函數, 可以方便地將待比對的資料(多欄亦可), 計算出 hash index (int)後, 再進行比對, 舉例如下:

兩個 table 資料:

table TBL_TMP1
fno fdata fcreatetime
1 ‘data 1’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/3’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’

table TBL_TMP2
fno fdata fcreatetime
1 ‘data 001’ ‘2012/1/1’
2 ‘data 2’ ‘2012/2/12’
3 ‘data 3’ ‘2012/2/4’
4 ‘data 4’ ‘2012/4/1’

若要找出資料不同的資料列, 可以使用如下指令:

SELECT * FROM
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(*) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK

其中用了 CHECKSUM(*) 是將 table 中的各欄位合併起來計算 hash index, 再將兩個 table 含有 hash index (欄位名 CHK)拿來比對, 列出不同的資料, 結果如下:
1 data 1 2012-01-01 00:00:00.000 803471792 1 data 001 2012-01-01 00:00:00.000 1877235137
2 data 2 2012-02-03 00:00:00.000 266601110 2 data 2 2012-02-12 00:00:00.000 266601103

是很方便的工具函數.

附上建立上面 sample code 的 create table 及 insert data 指令:

CREATE TABLE TBL_TMP2 (fno int, fdata varchar(20), fcreatetime datetime)

INSERT INTO TBL_TMP1 values (1, 'data 1', '2012/1/1')
INSERT INTO TBL_TMP1 values (2, 'data 2', '2012/2/3')
INSERT INTO TBL_TMP1 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP1 values (4, 'data 4', '2012/4/1')

INSERT INTO TBL_TMP2 values (1, 'data 001', '2012/1/1')
INSERT INTO TBL_TMP2 values (2, 'data 2', '2012/2/12')
INSERT INTO TBL_TMP2 values (3, 'data 3', '2012/2/4')
INSERT INTO TBL_TMP2 values (4, 'data 4', '2012/4/1')

大家可以試看看, 若是只要比對前兩欄, 則指令如下:

SELECT * FROM
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP1) a INNER JOIN
(SELECT *, CHECKSUM(fno, fdata) AS CHK FROM TBL_TMP2) b
ON a.fno = b.fno AND a.CHK != b.CHK

結果如下, 就是只有第一筆資料的前兩欄不同而已:
1 data 1 2012-01-01 00:00:00.000 -1291957785 1 data 001 2012-01-01 00:00:00.000 -1493283680
如同我們想要的結果一樣. 這樣一來可以方便活用這個 CHECKSUM 函數, 十分方便好用!

相關資料:
http://msdn.microsoft.com/en-us/library/ms189788.aspx

資料上寫到是 SQL Server 2005 以上版本可用, 不過其實在 SQL Server 2000 也有這個指令:
http://msdn.microsoft.com/en-us/library/aa258245%28v=sql.80%29.aspx