949
Chapter 41: Data Change Tracking and Capture
41
Enabling Change Tracking for a table can affect other tasks:■ The primary key constraint/index cannot be dropped while Change Tracking is
enabled.
■ If the table is dropped, then Change Tracking is fi rst removed from the table.■ (^) The table can’t use a partitioned table’s alter table...switch partition
command.
■ (^) Change Tracking does not track changes made by the truncate table command.
In this case, the synch target table should also be truncated.
To view the current tables with Change Tracking enabled, query the sys.change_
tracking_tables DMV:
SELECT s.name + '.' + t.name as [table],
ct.is_track_columns_updated_on,ct.min_valid_version,
ct.begin_version, ct.cleanup_version
FROM sys.change_tracking_tables ct
JOIN sys.tables t
ON ct.object_id = t.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
ORDER BY [table];
Internal Tables
Change Tracking stores its data in internal tables. There’s no reason to directly query these
tables to use Change Tracking. However, it is useful to look at the space used by these
tables when considering the cost to use Change Tracking and to estimate disk usage.
Query sys.internal_tables to fi nd the internal tables. Of course, your Change Tracking
table(s) will have a different name:
SELECT s.name + '.' + o.name as [table],
i.name as [ChangeTracking],
ct.is_track_columns_updated_on,
ct.min_valid_version,
ct.begin_version, ct.cleanup_version
FROM sys.internal_tables i
JOIN sys.objects o
ON i.parent_id = o.object_id
JOIN sys.schemas s
ON o.schema_id = s.schema_id
JOIN sys.change_tracking_tables ct
ON o.object_id = ct.object_id
WHERE i.name LIKE 'change_tracking%'
ORDER BY [table]
c41.indd 949c41.indd 949 7/31/2012 10:17:20 AM7/31/2012 10:17:20 AM
http://www.it-ebooks.info