Sqlserver change tracking case and sqlserver tracking case

Sqlserver change tracking case and sqlserver tracking case

Create database testgouse testgocreate table t1 (sid int identity (200) not null primary key, sno int not null, sname varchar () goinsert into t1 (sno, sname) values (101, 'wind ') goALTER DATABASE [test] set recovery simple with NO_WAITGOALTER DATABASE [test] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) goALTER TABLE dbo. t1ENABLE CHANGE_TRACKINGWITH (TRACK_COLUMNS_UPDATED = ON) go -- create table veridrecord (lvid bigint not null, nvid bigint not null, isover bigint) goinsert into veridrecord (lvid, nvid) values (1) go --- capture the business data table create table [dbo]. [tempt1] ([sid] [int] not null, [sno] [int] not null, [sname] [varchar] (200) NULL, [addtime] [datetime] NULL) goALTER TABLE [dbo]. [tempt1] add constraint [DF_tempt1_addtime] DEFAULT (getdate () FOR [addtime] GO ---- test data insert into t1 (sno, sname) values (101, 'A ') insert into t1 (sno, sname) values (102, 'B') insert into t1 (sno, sname) values (103, 'C') insert into t1 (sno, sname) values (104, 'd ') goset nocount onupdate t1 set sno = '000000' where sno = 102go --- view change capture DECLARE @ synchronization_version bigint DECLARE @ this_version bigintDECLARE @ pid int = COLUMNPROPERTY (OBJECT_ID ('dbo. t1 '), 'sno', 'columnid') declare @ a bigintdeclare @ B bigintdeclare @ c bigintSET @ synchronization_version = CHANGE_TRACKING_CURRENT_VERSION () select @ a = lvid from Region @ B = nvid from Region @ c = isover from veridrecordif @ synchronization_version> @ bbeginif @ c = 1 beginupdate veridrecord set lvid = nvidupdate veridrecord set nvid = @ Region @ this_version = lvid from veridrecordselect @ this_versioninsert into tempt1 (sid, sno, sname) select k. sid, k. sno, k. sname from (select p. sid, P. sno, P. sname, CT. SYS_CHANGE_OPERATION, ct. SYS_CHANGE_VERSION, case when then (@ pid, SYS_CHANGE_COLUMNS) = 0 then 'no' when then (@ pid, SYS_CHANGE_COLUMNS) = 1 then 'yes' other 'end ischangeFROM dbo. t1 AS Pinner joinCHANGETABLE (CHANGES dbo. t1, @ this_version) as ctonp. sid = CT. sid where ct. SYS_CHANGE_VERSION> @ this_version) k where k. ischange = 'yes' endendelsebeginselect 'no changes' endupdate veridrecord set isover = 1 goselect * from tempt1