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:

No comments:
Post a Comment
Note: only a member of this blog may post a comment.