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.