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:

  1. -- Recreate test table
  2. IF OBJECT_ID('dbo.TestCT') IS NOT NULL
  3. DROP TABLE dbo.TestCT;
  4. GO
  5. CREATE TABLE dbo.TestCT
  6. (
  7. ID INT IDENTITY(1,1) NOT NULL,
  8. CONSTRAINT [dbo.TestCT.PK] PRIMARY KEY CLUSTERED (ID),
  9. Code VARCHAR(10) NOT NULL,
  10. Description NVARCHAR(100) NULL
  11. );
  12.  
  13. GO
  14. -- Enable CHANGE TRACKING for test table
  15. ALTER TABLE dbo.TestCT ENABLE CHANGE_TRACKING
  16.  
  17. GO
  18. -- Fill test table with initial rows
  19. INSERT INTO dbo.TestCT(Code, Description)
  20. VALUES
  21. ('AAA', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit.'),
  22. ('BBB', 'Cras sed tincidunt dolor, in interdum lorem.'),
  23. ('CCC', 'Vestibulum eget condimentum orci.');
  24.  
  25. GO
  26. -- Wait for 30 seconds
  27. WAITFOR DELAY '00:00:30'
  28.  
  29. GO
  30. -- Change second row in the test table
  31. UPDATE dbo.TestCT
  32. SET Description = '!!! This line was changed !!!'
  33. WHERE Code = 'BBB';
  34.  
  35. GO
  36. -- Show CHANGE TRACKING information
  37. -- directly after initial filling (VersionNumber=0)
  38. SELECT
  39. CT.ID,
  40. CT.SYS_CHANGE_VERSION,
  41. CT.SYS_CHANGE_CREATION_VERSION,
  42. CT.SYS_CHANGE_OPERATION,
  43. COMM.commit_time
  44. FROM
  45. CHANGETABLE(CHANGES dbo.TestCT, 0) CT
  46. INNER JOIN sys.dm_tran_commit_table COMM ON
  47. CT.sys_change_version = COMM.commit_ts
  48. ORDER BY
  49. CT.ID, COMM.commit_time DESC;
  50.  
  51. -- Get initialisation version number
  52. DECLARE @MaxCreationVersion INT = (
  53. SELECT TOP(1) SYS_CHANGE_CREATION_VERSION
  54. FROM CHANGETABLE(CHANGES dbo.TestCT, 0) CT
  55. );
  56.  
  57. -- Show changes directly after initialisation
  58. SELECT
  59. CT.ID, CT.SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,
  60. COMM.commit_time
  61. FROM
  62. CHANGETABLE(CHANGES dbo.TestCT, @MaxCreationVersion) CT
  63. INNER JOIN sys.dm_tran_commit_table COMM ON
  64. CT.sys_change_version = COMM.commit_ts
  65. ORDER BY
  66. CT.ID, COMM.commit_time DESC;

Execution result:

No comments:

Post a Comment

Note: only a member of this blog may post a comment.