Please visit SQLVERSITY.COM for more details.
- Both Track DML changes
- Both Track whether column data has changes or not
- Required to enable them on table as well as on database
S.No. |
Change Tracking |
Change Data Capture |
| 1 | It tracks only whether the data has been changed or not. But never captures the modified values | It captures values also |
| 2 | It follows synchronous tracking mechanism to track the data | It follows asynchronous tracking mechanism which reads data changes from ldf file |
| 3 | It works in all the editions of SQL Server like Express, Workgroup, Web, Standard, Enterprise, DataCenter | It works only in Enterprise and DataCenter editions |
| 4 | Does not hold historical data | Holds historical data |
| 5 | No need to access ldf file of that particular database | Needs to access ldf file of that particular database |
| 6 | Uses temp db | Uses Transaction log (ldf file) |
| 7 | Returns less information regarding the changes in the data | Returns more information compared to CT |
| 8 | Little bit difficult to get the full data of the table for which we have to join CHANGETABLE with the table based on primary key | We can query directly from tables |
| 9 | SQL Server agent is not required to track the data | SQL Server Agent should be enabled to capture the data |
| 10 | We can enable it on a table when the table has primary key on it | No restriction as it is in the CT |
| 11 | User can truncate the table when it is enabled on the table | User can not truncate when it is enabled on the table |
| 12 | We can turn off auto clean up | We can’t turn off |
| 13 | Only SYSADMIN can enable CT | Only DBOwner can enable CDC |
I am very happy to say we are completely done with CT and CDC. I have not decided about the next article yet 🙂
Very soon I will be back with a nice & interesting article.
ALL THE BEST
sqlversity 