Change tracking provides an efficient, light-weight data change tracking mechanism. In other words, this only tracks UPDATE, INSERT, DELETE
statements; it does not track SELECT
statements. It does not track who made the change.
If you truly need to track all DML and DDL statements made against the server, you'll need to implement an Extended Events monitoring solution, but be aware for a server that is heavily used, you will likely affect performance in a negative way.
If you only are interested in DML changes, then change tracking will allow you to understand what changes were made to the data using the CHANGETABLE
T-SQL statement. This sample code shows how that works. The code drops any database named ChangeTrackingTest first, then creates that same database with a single table that has change tracking enabled.
USE master;
GO
DROP DATABASE ChangeTrackingTest
GO
CREATE DATABASE ChangeTrackingTest;
ALTER DATABASE ChangeTrackingTest
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
USE ChangeTrackingTest;
GO
CREATE TABLE dbo.t
(
id int NOT NULL
PRIMARY KEY
CLUSTERED
);
ALTER TABLE dbo.t ENABLE CHANGE_TRACKING;
INSERT INTO dbo.t (id) VALUES (1);
UPDATE dbo.t SET id = 2 WHERE id = 1;
SELECT current_id = t.id
, ctc.*
FROM dbo.t t
CROSS APPLY CHANGETABLE(CHANGES dbo.t, NULL) ctc
GO
The results:
╔════════════╦════════════════════╦═════════════════════════════╦══════════════════════╦════════════════════╦════════════════════╦════╗
║ current_id ║ SYS_CHANGE_VERSION ║ SYS_CHANGE_CREATION_VERSION ║ SYS_CHANGE_OPERATION ║ SYS_CHANGE_COLUMNS ║ SYS_CHANGE_CONTEXT ║ id ║
╠════════════╬════════════════════╬═════════════════════════════╬══════════════════════╬════════════════════╬════════════════════╬════╣
║ 2 ║ 2 ║ 1 ║ D ║ NULL ║ NULL ║ 1 ║
║ 2 ║ 2 ║ 2 ║ I ║ NULL ║ NULL ║ 2 ║
╚════════════╩════════════════════╩═════════════════════════════╩══════════════════════╩════════════════════╩════════════════════╩════╝
Cleanup:
USE master;
GO
DROP DATABASE ChangeTrackingTest