Temporal, CDC and Change Tracking

History queries differ from change feeds.

UnderstandStudy an examplePractise

Understand the concept

System-versioned temporal tables keep row versions in a history table for point-in-time queries. Period columns use database-managed UTC transaction-start times, which may differ from application event times. Plan history growth and retention.

CDC captures log-based changes and can expose before/after values with LSN ranges. Change Tracking identifies changed keys, not a full archive of old values. Check edition, service and version requirements. These features are not interchangeable. Incremental consumers need checkpoints, retention awareness and recovery for missed windows. These examples only inspect feature status; the lab does not enable them.

WHEN TO USE IT

Distinguish historical reporting from incremental integration.

COMMON PITFALL

Treating Change Tracking as a complete row-history archive.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT name, temporal_type_desc FROM sys.tables WHERE schema_id = SCHEMA_ID(N'lab');

Shows the temporal status of lab tables.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Inspect CDC tracking on lab tables.

Show the solution and explanation

Worked solution

SELECT name, is_tracked_by_cdc FROM sys.tables WHERE schema_id = SCHEMA_ID(N'lab');

The setup enables no change feeds, so zero is expected.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.