SQL 更改跟踪不跟踪多記錄更新

我在一個大約有 300 萬行的表上打開了 SQL Server 更改跟踪。

打開更改跟踪：

ALTER DATABASE FooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

ALTER TABLE [dbo].[fooTable]
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

如果我單獨編輯行，一切都很好，每次插入/更新/刪除都會儲存一個更改。

如果我執行更新多行的更新查詢，我只會獲得更新中第二行的更改記錄。

所以：

UPDATE fooTable SET name = UPPER(name) where id between 100 and 200

緊隨其後（以確保每一行都發生實際更新）

UPDATE fooTable SET name = LOWER(name) where id between 100 and 200

僅導致記錄 101 的更改記錄。

SQL Express 2014

我已經弄清楚發生了什麼。

我所做的錯誤假設是，如果更改導致一次更新 4 行，那將導致表的 SYS_CHANGE_VERSION 上升 4。它不會。雖然 changetable 有 4 次更改，但版本只上升了 1（它是一次更改更改了多條記錄。）

任何有興趣自己測試的人都可以使用下面的程式碼。

-- 1. Create Table
CREATE TABLE dbo.fooTable(
   [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
   [Title] [nvarchar](20) NULL,
   [Surname] [nvarchar](128) NULL,
   [MiddleNames] [nvarchar](128) NULL,
   [Firstname] [nvarchar](128) NULL,
   [CreateDate] [datetime2](7) NOT NULL CONSTRAINT         [DF_CustomerProfile_CreateDate_I]  DEFAULT (getdate()),
   [LastModifiedDate] [datetime2](7) NOT NULL CONSTRAINT     [DF_CustomerProfile_LastModifiedDate_I]  DEFAULT (getdate()),
CONSTRAINT [PK_fooTable_I] PRIMARY KEY CLUSTERED 
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

-- 2. Insert dummy data
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mr','John',null,'Smith',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Mrs','Mary','Beth','Jones',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Ms','Thanh',null,'Nguyen',GetDate()-5, getdate())
insert into fooTable (Title, Firstname, MiddleNames, Surname, CreateDate,     lastModifiedDate)
values ('Dr','Lee','Evan','Oscars',GetDate()-5, getdate())

-- 3. Turn on Change Tracking
-- Database
ALTER DATABASE fooDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 10 DAYS, AUTO_CLEANUP = OFF)

-- Tables
ALTER TABLE dbo.fooTable
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

-- 4. Check current version
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be Zero if you have never     turned change tracking on before.

-- 5. Make a single change
Update fooTable set MiddleNames = 'Arthur' where Surname = 'Smith'

-- 6. Check current version, should have gone up by 1
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 1 if you have never turned change tracking on before.

-- 7. See the change, should be Type U on record with ID 1 
DECLARE @version bigint
SET @version = CHANGE_TRACKING_CURRENT_VERSION()-1
Select CT.*
   FROM CHANGETABLE(CHANGES dbo.fooTable, @version) CT

-- 8. If everything is OK up to here, that's great. Now we have a problem.
-- Update all the rows in the table
Update fooTable set lastModifiedDate = Getdate() + 5

-- 9. Check current version, I expected it would go up by 4, one update for each record
Select CHANGE_TRACKING_CURRENT_VERSION() -- Should be 5 if you have never turned change tracking on before.

-- 10. View all changes for the table. There should be 4 (the first one will be gone now.)
Select CT.*
   FROM CHANGETABLE(CHANGES dbo.fooTable, 0) CT

-- Observe that each row has the same SYS_CHANGE_VERSION which means I need to deal with it and move on.

引用自：https://dba.stackexchange.com/questions/125966