Monday, 6 July 2015

MSSQL - CHANGE TRACKING - How can I get DateTime of changes?

Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications.
You can get more information on the Microsoft Developer Network web site.

But on these pages you will not find an answer on this question:
How can I get DateTime of changes. You can find this information in the sys.dm_tran_commit_table DMV (Dinamic Management View).

There the complete T-SQL script:

-- Recreate test table
IF OBJECT_ID('dbo.TestCT') IS NOT NULL
    DROP TABLE dbo.TestCT;
GO
CREATE TABLE dbo.TestCT
(
    ID INT IDENTITY(1,1) NOT NULL,
        CONSTRAINT [dbo.TestCT.PK] PRIMARY KEY CLUSTERED (ID),
    Code VARCHAR(10) NOT NULL,
    Description NVARCHAR(100) NULL
);

GO
-- Enable CHANGE TRACKING for test table
ALTER TABLE dbo.TestCT ENABLE CHANGE_TRACKING 

GO
-- Fill test table with initial rows
INSERT INTO dbo.TestCT(Code, Description)
VALUES
    ('AAA', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'),
    ('BBB', 'Cras sed tincidunt dolor, in interdum lorem.'),
    ('CCC', 'Vestibulum eget condimentum orci.');

GO
-- Wait for 30 seconds
WAITFOR DELAY '00:00:30'

GO
-- Change second row in the test table
UPDATE dbo.TestCT
SET Description = '!!! This line was changed !!!'
WHERE Code = 'BBB';

GO
-- Show CHANGE TRACKING information
-- directly after initial filling (VersionNumber=0)
SELECT
    CT.ID,
    CT.SYS_CHANGE_VERSION,
    CT.SYS_CHANGE_CREATION_VERSION,
    CT.SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, 0) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;

-- Get initialisation version number
DECLARE @MaxCreationVersion INT = (
    SELECT TOP(1) SYS_CHANGE_CREATION_VERSION
    FROM CHANGETABLE(CHANGES dbo.TestCT, 0) CT
);

-- Show changes directly after initialisation
SELECT
    CT.ID, CT.SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
    COMM.commit_time
FROM
    CHANGETABLE(CHANGES dbo.TestCT, @MaxCreationVersion) CT
    INNER JOIN sys.dm_tran_commit_table COMM ON
        CT.sys_change_version = COMM.commit_ts
ORDER BY
    CT.ID, COMM.commit_time DESC;

Execution result: