August 30, 2016 at 7:56 am
Hi All,
I am experimenting with Change Tracking (Not CDC, yet) to identify rows in a small subset of tables to be loaded into a a data warehouse (Well, I suppose Mart, its a single fact + 5 dimension, nothing spectacular).
I've enabled Change Tracking:
ALTER DATABASE TrackerDB
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
GO
ALTER TABLE dbo.TrackME
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
I then made a couple of changes, inserts and an update. Which I *can* identify using CHANGETABLE function, however, I am struggling to understand the version parameter.
select CHANGE_TRACKING_CURRENT_VERSION();
-- This returns the value 2
So, If I were to perform an INSERT and then an UPDATE, am I correct in thinking the latest version would have lost the fact that a NEW row was added and it would then look like a only a row update has taken place.. Thus making this no good for tracking changes to be loaded into a data warehouse.
Or am I missing a trick? I like the lightweight nature of this change tracking approach but not sure it's going to work in my place.
EDIT: I should all, all I really want to achieve is a list of ID's that have changed since a given a date, be them new rows, updated rows, etc.. Deletes I am not too bothered about as no deletes occur on these tables.
Any thoughts?
Cheers,
Alex
August 31, 2016 at 1:00 am
September 1, 2016 at 12:24 pm
Your change would actually show up as I (an insert).
Let's say that you have a a record and it gets inserted and updated a couple of times, I'll include the changeTrackingId in () for info...
Id Val ct
1 2 (1)
1 3 (2)
1 4 (3)
If you looked for changetable changes passing in 0 you would show I for that row, as that was the primary action. If you passed in 2 then you would get U as you were looking for the primary action starting with that change number.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply