Many DBAs turn on SQL Server Change Tracking expecting to see what changed on a row. The feature records that a row changed and, optionally,...
Many DBAs turn on SQL Server Change Tracking expecting to see what changed on a row. The feature records that a row changed and, optionally, which columns were affected. It discards the before-and-after values entirely. That single detail decides whether the feature fits a synchronization workload, a lightweight tracking need, or a regulated audit requirement.
This guide covers the mechanics, the setup at both database and table level, the real limits, and where SQL Compliance Manager extends the picture for compliance.
SQL Server Change Tracking is a synchronous, row-level feature that records whether a row in a tracked table was inserted, updated, or deleted, and optionally which columns were affected.
The tracking work happens inline with the DML statement itself, which is why the overhead stays modest compared with log-based capture. Microsoft’s feature comparison draws a clear line.
The feature records the DML type, the changed row key, and an optional flag showing which columns were touched. That is the entire record.
The before-and-after values are discarded, permanently. A DBA can confirm a salary column was updated and still have no way to recover the previous figure from the tracking metadata.
Change Data Capture is the asynchronous counterpart that reads the transaction log and stores the actual before-and-after values.
Because CDC reads the log after the fact, it builds change tables with the full row image for each modification, making point-in-time reconstruction possible. That architectural difference is why people routinely conflate the two features. Change Data Capture carries more storage and processing weight in exchange for detail Change Tracking never keeps. They answer separate questions.
| Aspect | Change Tracking | Change Data Capture |
| Capture mode | Synchronous, inline with the DML statement | Asynchronous, read from the transaction log |
| What is stored | Changed row key, DML type, optional column-changed flag | Full before-and-after row images |
| Historical values | Discarded | Retained in change tables |
| Question answered | Has this row changed since version X? | What did this data look like before and after? |
| Typical workload | Synchronization clients, caching, lightweight tracking | Point-in-time reconstruction, downstream ETL |
Change Data Capture deserves its own technical treatment. Here it serves as the contrast that clarifies why the two features answer different questions.
SQL Server Change Tracking was designed for synchronization. Microsoft documents two patterns it supports.
Enabling the feature takes two steps, described in Microsoft’s enable and disable documentation.
Underneath both steps is the version mechanism, which explains how the feature answers a sync query without scanning the base table.
Each committed transaction touching a tracked table receives a version marker, and a synchronizing client stores the last version it processed. That stored number is the only state the application has to keep between passes.
On the next pass, the client asks for everything after that version, and SQL Server returns the changed row keys since that point. That exchange answers the “has this row changed since version X” question without scanning the base table.
Five constraints define the ceiling of native SQL Server Change Tracking, and each one emerges at a predictable stage in a deployment.
Limitation 1: No historical or intermediate values are captured. This is the same boundary established above, and it becomes an operational problem the first time someone asks what a record used to contain. If a row was updated four times inside your retention window, the feature tells you the row changed. The four prior states are unavailable, and no configuration option adds them.
Limitation 2: Every tracked table needs a primary key. Vendor-supplied schemas, legacy staging tables, and heaps inherited from an older application often lack one, which means those tables cannot be enrolled until someone adds a key. DBAs typically discover this partway through a rollout, when a subset of in-scope tables simply refuses to be enabled.
Limitation 3: No user or application identity is recorded. Change Tracking registers that a row changed without capturing the login, the application name, or the host. This limitation stands apart from the missing historical values, and it matters most to anyone with an audit obligation, because “the row changed” leaves the question unanswered: which account modified a patient record on a specific afternoon?
Limitation 4: There is no built-in alerting. A change to a sensitive column produces tracking metadata and nothing else. Any real-time awareness has to be built by hand, which means a polling job, a custom comparison against a reference set, a notification path, and the maintenance that comes with all three.
Limitation 5: Retention and autocleanup are ongoing responsibilities long after initial configuration. Tracking metadata grows with transaction volume, and Brent Ozar Unlimited documents the internal tracking tables as objects worth sizing and watching, along with the autocleanup warnings that appear when cleanup cannot keep pace with the configured retention period on busy systems. Monitor cleanup health on any high-volume instance.
When that overhead becomes a standing chore, IDERA SQL Compliance Manager gives DBAs centralized retention management for audit data instead of per-instance cleanup tuning.
SQL Server 2025 rebuilt the cleanup mechanism, and Microsoft’s Change Tracking documentation documents the shift.
The old cleanup woke on a fixed interval and performed a full deep pass across all tracked tables, which struggled on instances with large internal tracking metadata. The new default, adaptive shallow cleanup, runs automatically in SQL Server 2025 and handles large tracked tables without the full pass. Trace flag 8273 restores the prior deep-cleanup behavior for teams that need it.
For a DBA, the practical takeaway is that Microsoft rebuilt this mechanism because the old one hit real limits at real scale. SQL Server Change Tracking is an actively managed feature in the newest release, which reinforces the retention point above. Budget attention for it after go-live, and revalidate cleanup behavior when you upgrade.
Compliance frameworks including HIPAA, SOX, PCI DSS, and GDPR ask for evidence of who accessed or modified specific data and when, retained for a defined period and producible on demand. A row-level change signal leaves all of that unproven.
An auditor asking which account altered a financial record needs an identity, a timestamp, and a report that holds up under review. The native feature supplies none of the three.
IDERA SQL Compliance Manager closes the identity gap directly, auditing DDL and DML changes with the user, application, host, and timestamp detail that SQL Server Change Tracking never records.
The same UPDATE that native tracking reduces to a changed row key becomes an audit entry naming the login and the application that issued it.
Access and permission-change auditing sit alongside it. A reviewer can see who read a sensitive table and who was granted the rights to do so, not just who modified it.
Retention and archiving are managed centrally as well. Retention becomes a configured policy inside the auditing product, replacing per-database CHANGE_RETENTION tuning and autocleanup health checks spread across instances.
One note on scope. IDERA positions SQL Compliance Manager as a change-auditing product for regulated SQL Server environments. Performance and deadlock monitoring belong to a different product entirely, outside SQL Compliance Manager.
Consider a healthcare organization running SQL Server Change Tracking on its patient billing tables for a downstream reporting synchronization. During an audit, the reviewer asks who modified a specific claim record and when.
Native tracking can confirm the row changed at some point inside the retention window. It cannot name the account, the application, or the moment, and once the retention period has passed, it cannot confirm even that much.
The gap becomes concrete the second someone has to write “unable to determine” in a response to an auditor.
SQL Compliance Manager records each DML operation against the audited table with the originating login, application, and timestamp, answering that question directly. It also alerts on changes to sensitive tables in real time, so the change surfaces when it happens rather than during the audit that follows. IDERA’s sensitive data discovery narrows that scope first, identifying and classifying the columns that warrant this level of auditing so the whole schema does not sit under a single policy.
SQL Server Change Tracking remains the right tool for what Microsoft built it to do, telling a synchronizing client which rows moved, cheaply and synchronously, without the weight of transaction-log capture. The moment an auditor asks who changed a regulated record, the feature has nothing to say. That answer has to come from somewhere else.
IDERA SQL Compliance Manager turns that gap into a closed question. It extends native change tracking into a compliance-grade audit trail, and everything native tracking discards becomes evidence you can produce on demand.
Your next audit is already on someone’s calendar, and the difference between “unable to determine” and a definitive answer is the trail you start building now.
Review the SQL Compliance Manager product page and download the datasheet to measure its coverage against the framework you report under, before an auditor measures it for you.
What is change tracking in SQL Server?
Change Tracking records row-level changes synchronously, capturing which rows in a tracked table were inserted, updated, or deleted along with an optional column-changed flag. It stores the changed row’s primary key and version information, leaving the data values themselves out of scope.
What is the difference between CDC and change tracking in SQL Server?
Change Tracking works synchronously with the DML statement and reports only that a row changed. Change Data Capture works asynchronously from the transaction log and retains the actual before-and-after values, which is why it costs more in storage and processing.
How do I turn on change tracking in SQL Server?
Enable it at the database level first, setting CHANGE_RETENTION for how long tracking information is kept and AUTO_CLEANUP for automatic purging. Then enable it on each table you want tracked, and remember that every such table must have a primary key.
How do you query changes in SQL Server Change Tracking?
Applications read changes through the CHANGETABLE(CHANGES …) function, passing the table name and the version number from their last synchronization. CHANGE_TRACKING_CURRENT_VERSION() returns the version to store for the next pass, and CHANGE_TRACKING_MIN_VALID_VERSION() confirms the stored version is still inside the retention window before the client trusts the results.
Why does SQL Server Change Tracking require a primary key?
The primary key identifies each changed row, so a synchronizing client can requery the source table for exactly the rows that moved. Tables without one, including heaps and many vendor-supplied staging tables, cannot be enrolled until a key is added.
What are the performance implications of using change tracking at scale?
The main cost is internal tracking metadata that grows with transaction volume and depends on cleanup keeping pace with your retention setting. Monitor tracking table size and autocleanup health, and note that SQL Server 2025 replaced the old deep-cleanup pass with adaptive shallow cleanup to better handle large tracked tables.
SQL Compliance Manager
Contact our sales team to get a personalized demo of our database management software for SQL Server!
