SQL Server : change tracking can retrieve previous version records

sqxo8psd  于 2023-05-05  发布在  SQL Server
关注(0)|答案(1)|浏览(181)

I have configured my SQL Server database with change tracking feature. Currently I can retrieve table records information for latest version only.

How can I query the tables to get the previous version of the records? Hence that I can compare what data has been modified.

Please find my current query that am using to fetch the information.

DECLARE @last_synchronization_version bigint;

SELECT  
    CT.product_id, p.CLM_STAT_CD, p.DLR_CODE, p.DLR_NAME,  
    p.comments, p.INVOICE_NO, p.INVOICE_LINE_NO, p.INVOICE_ITEM_SUFFIX, 
    tc.commit_time, ct.sys_change_version, c.updated_by,
    CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,  
    CT.SYS_CHANGE_CONTEXT,
    CT.SYS_CHANGE_VERSION as TrackingVersion
FROM  
    dbo.product AS p  
RIGHT OUTER JOIN  
    CHANGETABLE(CHANGES dbo.product, @last_synchronization_version) AS CT  
        ON p.product_id = CT.product_id
JOIN
    sys.dm_tran_commit_table tc ON CT.sys_change_version = tc.commit_ts

The above query is giving me the latest version changes information only. But I just want to check for the previous version changes also.

2ul0zpep

2ul0zpep1#

AS Damien_The_Unbeliever and eshirvana have pointed out in the comments, Change Tracking does not support version tracking. Since Change Data Capture is not supported in Azure SQL, Temporal tables seem to be a viable alternative.

相关问题