Overload
Sometimes documentation is overwhelming, and it’s easy to miss important distinctions between features.
One thing I’ve seen people run into is that these two technologies have very different relationships with Partitioning.
Now, I know this isn’t going to be the most common scenario, but often when you find people doing rocket surgeon stuff like tracking data changes, there are lots of other features creeping around.
Change Data Capture Lets You Choose
When you run sys.sp_cdc_enable_table to enable Change Data Capture for a table, there’s a parameter to allow for Partition Switching.
It’s fittingly named @allow_partition_switch — and it’s true by default.
That means if you’re using CDC, and you get an error while trying to switch partitions, it’s your fault. You can fix it by disabling and re-enabling CDC with the correct parameter here.
EXEC sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 'ct_part',
@capture_instance = 'dbo_ct_part';
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name = 'ct_part',
@role_name = NULL, --or whatever you used before
@allow_partition_switch = 1;
Change Tracking Just Says No
To demo things a little:
CREATE PARTITION FUNCTION ct_func (int)
AS RANGE LEFT FOR VALUES
(1, 2, 3, 4, 5);
GO
CREATE PARTITION SCHEME ct_scheme
AS PARTITION ct_func
ALL TO([PRIMARY]);
GO
We don’t need anything fancy for the partitioning function/scheme, or the tables:
CREATE TABLE dbo.ct_part
(
id int PRIMARY KEY CLUSTERED ON ct_scheme(id),
dt datetime
);
CREATE TABLE dbo.ct_stage
(
id int PRIMARY KEY,
dt datetime
);
Now we flip on Change Tracking:
ALTER TABLE dbo.ct_part
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
We can get a little bit fancy with how we stick data into the table (ha ha ha), and how we figure out what the “highest” partition number is so we can try to switch it out.
INSERT
dbo.ct_part(id, dt)
SELECT
x.c,
x.c
FROM
(
VALUES (1),(2),(3),(4),(5)
) AS x(c);
SELECT
c.*
FROM dbo.ct_part AS c
CROSS APPLY
(
VALUES($PARTITION.ct_func(c.id))
) AS cs (p_id);
Since we (probably against most best practices) have five partitions, all with one row in them, our highest partition number will be five.
If we try to switch data out into our staging table, we’ll get an error message:
ALTER TABLE dbo.ct_part
SWITCH PARTITION 5 TO dbo.ct_stage;
Msg 4900, Level 16, State 2, Line 62
The ALTER TABLE SWITCH statement failed for table 'Crap.dbo.ct_part'.
It is not possible to switch the partition of a table that has change tracking enabled.
Disable change tracking before using ALTER TABLE SWITCH.
We could disable Change Tracking here, do the switch, and flip it back on, but then we’d lose all the tracking data.
SELECT
ct.*
FROM CHANGETABLE(CHANGES dbo.ct_part, 0) AS ct;
ALTER TABLE dbo.ct_part
DISABLE CHANGE_TRACKING;
ALTER TABLE dbo.ct_part
SWITCH PARTITION 5 TO dbo.ct_stage;
ALTER TABLE dbo.ct_part
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
SELECT
ct.*
FROM CHANGETABLE(CHANGES dbo.ct_part, 0) AS ct;
Is This A Dealbreaker?
This depends a lot on how you’re using the data in Change Tracking tables, and whether or not you can lose all the data in there every time you switch data out.
If processes that rely on the data in there have all completed, and you’re allowed to start from scratch, you’re fine. Otherwise, Change Tracking might not be the solution for you. Similarly, Temporal Tables don’t do so well with Partition Switching either.
Change Data Capture handles partition switching just fine, though. It’s a nice perk, and for some people it narrows the choices down to one immediately.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
One thought on “An Important Difference Between Change Tracking And Change Data Capture In SQL Server”
Comments are closed.