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.