SQL Server 2008 Capture DML Changes Using Change Data Capture


7. CDC captures any DML changes that happen to the Currency Table by logging these changes into the cdc.dbo_Currency_CT table.

Use ChangeDataCapture
Go
Select * from cdc.dbo_Currency_CT 
Go 



8. Now let’s update and delete some rows from the Currency table by executing the following TSQL Query:

Use ChangeDataCapture
Go

Update Currency
Set CurrencyName = ‘AFGHANI’
Where CurrencyKey = 1
Go

Delete From Currency
Where CurrencyKey = 3
Go

9. Now if you run the select statement against the cdc.dbo_Currency_CT table you should see that all the changes to the Currency table are captured. You could also see the values between 1…4 being assigned against _$operations column. The translation for each of these values is outlined below:

_$operations = 1 Means row was Deleted
_$operations = 2 Means row was Inserted
_$operations = 3 Means value of row before update
_$operations = 4 Means value of row after update



Conclusion
The Change Data Capture feature helps DBA’s to keep track of all the Data Manuplation Language changes on a specific user table.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |