代码如下： Listing 2.
|
--------------------------------------------- --Method 2 : using CheckSum (not reliable) --------------------------------------------- --Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Create Audit Summary Table to hold Meta-Data IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, TableName sysname NOT NULL, LastChkSum INT NOT NULL ) GO INSERT dbo.TableAuditSummary (TableName, LastChkSum) SELECT 'dbo.CheckSumTest', CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest GO --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALL ... --Make a Simple (Single row) Update UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=1 --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALL ... --Reset MetaData UPDATE dbo.TableAuditSummary SET LastChkSum=(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) WHERE TableName='dbo.CheckSumTest' --Make a Symmetric change UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=2 --Tables that need exporting (no rows returned as CHECKSUM_AGG() has not changed!!) SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALLCode Listing 2 |
正如你所看到的那样，对于单个的变化的情况，CHECKSUM是使用比较好的，但是CHECKSUM_AGG()却不能反应数据的变化
代码如下：Code Listing 3
|
--Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL, chksum1 AS (CHECKSUM(id, vc1, vc2)), chksum2 AS (BINARY_CHECKSUM(id, vc1, vc2)) ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Show Computed Columns and CheckSum_Agg() value = 199555 SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest --Make a Simple (Single row) Update UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=1 --Show Computed Columns and CheckSum_Agg() value = 204816 (Ok) SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest --Make a Symmetric change UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=2 --Show Computed Columns and CheckSum_Agg() value = 204816 (Not Ok!) SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest |
