USE [Test]
GO
DROP TABLE [ChangeTrack]
GO
CREATE TABLE [dbo].[ChangeTrack](
[LineID] [int] IDENTITY(1000,1) NOT NULL,
[Fname] [nvarchar](100) NULL,
[Lname] [nvarchar](100) NULL,
[InsertDate] [datetime] NULL,
CONSTRAINT [PK_ChangeTrack] PRIMARY KEY CLUSTERED
(
[LineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeTrack] ADD CONSTRAINT [DF_ChangeTrack_InsertDate] DEFAULT (getdate()) FOR [InsertDate]
GO
ALTER TABLE [dbo].[ChangeTrack] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
GO
declare @synchronization_version bigint , @last_synchronization_version bigint
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
waitfor delay '00:00:05'
--load table
insert into [ChangeTrack] (Fname, Lname ) values ('Pat','Rick'), ('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick'),('Pat','Rick')
waitfor delay '00:00:05'
--Obtain initial data set.
--SELECT * FROM [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1302
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some updates
update [ChangeTrack] set Fname = Fname + '_CHANGED' where LineID % 10 = 4
waitfor delay '00:00:05'
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1312
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION();
waitfor delay '00:00:05'
--some insert/update/delete
insert into [ChangeTrack] (Fname, Lname ) values ('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111'),('Pat111','Rick111')
update [ChangeTrack] set Fname = '123' where LineID % 10 = 5
delete from [ChangeTrack] where LineID % 10 = 6
waitfor delay '00:00:05'
--select * from [ChangeTrack]
--Using the Change Tracking Functions to Obtain Changes
--declare @last_synchronization_version bigint
--set @last_synchronization_version = 1313
set transaction isolation level snapshot
begin transaction
SELECT
Getdate(), CT.[LineID], CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
set @last_synchronization_version = CHANGE_TRACKING_CURRENT_VERSION()
commit
/*
SELECT
CT.[LineID], P.*,
CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_COLUMNS,
CT.SYS_CHANGE_CONTEXT
FROM
[ChangeTrack] AS P
RIGHT OUTER JOIN
CHANGETABLE(CHANGES [ChangeTrack], @last_synchronization_version) AS CT
ON
P.[LineID] = CT.[LineID]
*/
--select CHANGE_TRACKING_CURRENT_VERSION()