24-bit SQL monitoring table changes

Sometimes, we need to monitor the changes of a table in an important period, including insertion, update, and deletion. For example, when we export data to an external system, we want to export all the data, and it is best to export the changed data after the last export.
As a DBA, we can use traditional trigger operations to build a metadata table or a timestamp column to monitor data changes.
The Code is as follows: Code Listing 1
The Code passed the SQL 2005 (SP3) and SQL 2008 R2 (RTM with cu5) tests.
Copy codeThe Code is as follows:
-------------------
-- Method 1: TRIGGER
-------------------
-- 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,
LastUpdate datetime not null,
LastExport DATETIME NOT NULL
)
GO
INSERT dbo. TableAuditSummary (TableName, LastUpdate, LastExport) VALUES ('dbo. checksumtest', GETDATE (), GETDATE ())
GO
-- Tables that need exporting
SELECT * FROM dbo. TableAuditSummary WHERE LastUpdate> LastExport
-- Create Trigger on all Base Tables
-- This fires on any insert/update/delete and writes new LastUpdate column for the table set to Current Date and Time
IF OBJECT_ID ('dbo. trg_CheckSumTest_MaintainAuditSummary ', 'tr') is not null drop trigger dbo. trg_CheckSumTest_MaintainAuditSummary
GO
Create trigger dbo. trg_CheckSumTest_MaintainAuditSummary
ON dbo. CheckSumTest
After insert, UPDATE, DELETE
AS
BEGIN
IF (object_id ('dbo. checksumtest') is not null)
UPDATE dbo. TableAuditSummary SET LastUpdate = GETDATE () WHERE TableName = 'dbo. checksumtest'
END
GO
-- Make an Update
UPDATE dbo. CheckSumTest SET vc1 = 'B', vc2 = 'A' WHERE id = 1
UPDATE dbo. CheckSumTest SET vc1 = 'A', vc2 = 'B' WHERE id = 2
-- Check Meta-Data
SELECT * FROM dbo. TableAuditSummary WHERE LastUpdate> LastExport
-- When we have Exported the data, we run the following to reset MetaData
UPDATE dbo. TableAuditSummary SET LastExport = GETDATE () WHERE LastUpdate> LastExport

Recently, I am reading the online help (BOL) related knowledge of. I have access to the SQL Server CHECKSUM (), BINARY_CHECKSUM (), and checksum_sums () functions, then I suddenly thought about whether these functions can also monitor the changes in table data. It turns out that although the CHECKSUM_AGG () function is described as a detection table change, it is not applicable here.
Use the CheckSum () and checksum_sums () Functions
The checksum_online () function is described in this way on Books OnLine and many related sites. It is usually used to check whether the data of a table is changed. this is a better way to replace the trigger, but this operation will cause the table scan operation. So this time I still use metadata to track data changes. I just created a new column named LastChkSum instead of LastUpdate. This column is used to save checksum_sums (BINARY_CHECKSUM (*)), it will generate a unique value in the whole table to differentiate data changes.
The Code is as follows: Listing 2.
Copy codeThe Code is as follows:
---------------------------------------------
-- 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_sums (BINARY_CHECKSUM (*) FROM dbo. CheckSumTest
GO
-- Tables that need exporting
SELECT * FROM dbo. TableAuditSummary WHERE TableName = 'dbo. checksumtest'
AND LastChkSum <> (SELECT checksum_sums (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_sums (BINARY_CHECKSUM (*) FROM dbo. CheckSumTest)
UNION ALL
...
-- Reset MetaData
UPDATE dbo. TableAuditSummary SET LastChkSum = (SELECT checksum_sums (BINARY_CHECKSUM (*) FROM dbo. CheckSumTest)
WHERE TableName = 'dbo. checksumtest'
-- Make a random Ric 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_sums (BINARY_CHECKSUM (*) FROM dbo. CheckSumTest)
UNION ALLCode Listing 2

As you can see, for a single change, CHECKSUM is better, but CHECKSUM_AGG () cannot reflect data changes.
The Code is as follows: Code Listing 3
Copy codeThe Code is as follows:
-- 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 random Ric 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

We will find that the values of checksum_sums (BINARY_CHECKSUM (*) before and after adjustment are the same and cannot be distinguished.
Conclusion:
Although the checksum_ () function is described to be able to monitor changes in table data, it cannot be used in actual tests. In particular, it cannot be monitored when performing symmetric data modification on the table.
By: Taylor Ning