SQL Change Tracking not tracking multi-record updates

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

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.

StackExchange Database Administrators Q#125966, answer score: 3