6

I have SQL Server change tracking switched on a table with about 3mil rows.

Switch on change tracking:

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)

If I edit rows individually, all fine, a change is stored for each insert/update/delete.

If I run an update query that updates more than one row, I only get a change record for the SECOND row in the update.

So:

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

followed by (to ensure that an actual update occurs for each row)

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

results in a Change record for record 101 only.

SQL Express 2014

3

I have worked out what is going on.

The bad assumption I have made is that if a change causes 4 rows to be updated at once, that would result in the SYS_CHANGE_VERSION of the table going up by 4. It doesn't. Although there are four changes in the changetable, the version only goes up by 1 (it's a single change that changed multiple records.)

Anyone interested in testing it for themselves, can use the code below.

-- 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.
  • I have seen somewhat of the opposite effect, where 50 records have the same sys_change_version, but could not have possibly been updated at the same time (to the best of my knowledge of our systems). Dont assume anything. – Jeremy Giaco Sep 21 '18 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.