试图了解一些SQL Server更改跟踪功能 [英] Trying to understand some SQL Server change tracking features

问题描述

我正在使用具有更改跟踪功能的SQL Server 2016 SP1，并且有一个问题要问您.

I'm working on SQL Server 2016 SP1 with the Change Tracking feature and I have a question for you.

我有一个启用了更改跟踪的数据库.该数据库包含一个表Table，该表已激活更改跟踪"，但未激活更新跟踪列"选项.

I have a database which has Change Tracking enabled. That database contains a table Table which has "change tracking" activated, but not "track columns updated" option.

例如，在Table上，我只有一个名为Id的列，类型为"uniqueidentifier"，这是我的PK.

For the example, on Table, I only have one column called Id of type is "uniqueidentifier", which is my PK.

开始时，我的更改跟踪当前版本为0.

On start, my change tracking current version is 0.

我得到了:

SELECT CHANGE_TRACKING_CURRENT_VERSION();

我向Table添加了新行:

INSERT INTO dbo.[Table] (Id) 
VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');

现在，我的更改跟踪当前版本为1.

Now, my change tracking current version is now 1.

通过此请求，我可以在变更跟踪系统中看到我的元素:

With this request, I can see my element in the change tracking system :

SELECT * 
FROM CHANGETABLE (CHANGES dbo.[Table], 0) CT;

结果是:

现在，我用以下命令删除行:

Now, I delete my row with this :

DELETE FROM dbo.[Table] 
WHERE Id = 'C99F9E2A-1974-47CE-A406-481076F53BBD';

更改跟踪当前版本为2.

Change tracking current version is now 2.

我以与先前相同的请求再次插入它.

I insert it again with the same request than previous.

更改跟踪当前版本为3.

Change tracking current version is now 3.

有了这个请求，我得到了这个结果:

With this request, I got this result :

SELECT * 
FROM CHANGETABLE (CHANGES dbo.[Table], 1) CT;

现在是我的问题，为什么我在SYS_CHANGE_OPERATION中得到"U"?

Now is my question, why I got "U" in SYS_CHANGE_OPERATION ?

"I"为什么不引起1< SYS_CHANGE_CREATION_VERSION是3吗?

Why not "I" cause 1 < SYS_CHANGE_CREATION_VERSION which is 3 ?

感谢您的帮助！

推荐答案

添加一些动机后，更改跟踪功能的目的是使您能够查看自上次检查和使您可以收获这些更改并将其应用到其他表或外部系统.

Adding some motivation, the intent of the change tracking functions is to enable you to see what has changed since the last time you checked, and enable you to harvest and apply those changes to some other table or external system.

如果您查看删除之后 之后的更改，您将看到该操作为I.但是，如果您查看删除之前的更改，则可以跳过"删除.但是更改跟踪不记得删除之前的非键值.因此该行被报告为已更新.

If you look at the changes since just after the delete you will see the operation as an I. But if you look at the change since before the delete, you "skip over" the delete. But change tracking doesn't remember what the non-key values were before the delete. So the row is reported as having been updated.

EG

ALTER DATABASE current  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
go
drop table if exists ct

create table ct(id uniqueidentifier primary key)

ALTER TABLE ct
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

declare @beforeInsert bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
INSERT INTO dbo.ct (Id) VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');
declare @afterInsert bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
DELETE FROM dbo.ct WHERE Id = 'C99F9E2A-1974-47CE-A406-481076F53BBD';
declare @afterDelete bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
INSERT INTO dbo.ct (Id) VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');


SELECT 'from before insert to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @beforeInsert) CT
union all
SELECT 'from after insert to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @afterInsert) CT
union all
SELECT 'from after delete to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @afterDelete) CT

输出

                              id                                   sys_change_operation
----------------------------- ------------------------------------ --------------------
from before insert to current C99F9E2A-1974-47CE-A406-481076F53BBD I
from after insert to current  C99F9E2A-1974-47CE-A406-481076F53BBD U
from after delete to current  C99F9E2A-1974-47CE-A406-481076F53BBD I

这篇关于试图了解一些SQL Server更改跟踪功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！