The change of the monitoring table of SQL Drip 24 _mssql

Source: Internet
Author: User
Tags datetime getdate sql 2008 table definition
Sometimes, we need to monitor the changes of a table in an important time period, including inserts, updates, deletes. For example, when we export data to an external system, we want to export all of the data and it is best to export the data that has changed since the last export.
As a DBA, we can use traditional trigger actions to build a metadata table or a timestamp column to monitor the changes in the data.
The code is as follows: Listing 1
The code is tested in SQL (SP3), SQL 2008 R2 (RTM with Cu5) through
Copy Code code 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 no 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 was reading about the Guan Tian SQL Server online Help (BOL), and I came into contact with some of the functions of SQL CHECKSUM (), Binary_checksum (), and Checksum_agg (), It suddenly occurred to me that these functions can also monitor the data changes in the table, and it turns out that the Checksum_agg () function is not applicable here, although it is described as a change in the detection table.
Using the CheckSum () and Checksum_agg () function
The Checksum_agg () function, described in books OnLine and many related sites, is typically used to detect whether a table's data has changed. This is a better way to replace a trigger, except that it causes table scans to be done. So I'm still using metadata to track changes in data, just new column lastchksum instead of Lastupdate, which is used to save Checksum_agg (binary_checksum (*)), which will produce a unique value in the entire table. To distinguish the change of the data.
The code is as follows: Listing 2.
Copy Code code 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_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

As you can see, for a single change, checksum is better used, but Checksum_agg () does not respond to changes in the data
The code is as follows: Listing 3
Copy Code code 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 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

We will find that the value of the Checksum_agg (binary_checksum (*)) is the same before and after the adjustment and cannot distinguish
Conclusion:
The Checksum_agg () function is not available in the actual test, although it is described as being able to monitor the changes in the table data. Especially when the table is modified with symmetric data, it cannot be monitored
Author: Tyler Ning

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.