Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting?

Question

SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below:

1. When CDC is enabled on a table, SQL Server creates another table (so-called cdc table here) that contains all columns of the original table plus some extra meta-data column.
2. When insert happened, the cdc table kept a copy of record.
3. When update happened, the cdc table kept before changed and after changed records.
4. When delete happened, the cdc table kept before changed record.
5. CDC use SQL Server Agent to monitor the log file and write the changes to the cdc table
6. Typical size of the meta-data of each record in cdc table is about 51-bytes.

Eg. After 1 insert and 1 update actions The original table kept 1 record The cdc table kept 3 records (1 insert, 2 records per each update)

Assume the image size is 300KB, the total size is about 1,200 KB. It impacts the disk storage and disk IO.

Is it possible NOT to keep cdc record for a type of action (ie. insert)?

Is there any alternative for auditing Image that keeps less copy of records?

Answer 1

Well, do you want to keep track of all the versions of the image data, or not? If not, can't you specify only certain columns for CDC instead of just enabling all columns? That will let you keep track of the changes to other columns but ignore the image data changes.

In other words, if all you care about is the current state of the image data, you can get that from the base table. If you care about the previous states of the image data, there isn't any way to store that data without storing it somewhere.

Answer 2

Old records in the capture tables are purged automatically after 2 or 3 days by default, you can configure that time to be shorter if need be; you shouldn't be using the system tables for long-term storage of the data or for reporting, typically an SSIS package would be copying the records out to permanent locations, you could easily skip the records you don't want to persist in the copy process.

Answer 3

You could just use trigger to track changes in whatever way you wish, you don't have to use the built in CDC. In a past job, we used CodeSmith to generate all our change checking triggers.