When you are defining a policy for data capture, the level you set on your Table Policy and the fields you subsequently select to capture with Field Policies, can impact the data captured.
Consider the following when setting the level of your Table Policy. By specifying the Level in the Table Policy, the policy allows flexible capture:
If you only need to capture the fact the data changed, the Level field can be set to only capture the Change Tracking record.
If you need to know which fields have changed, but not their values, the change fieldmap in the Change Tracking record can be consulted to see which fields changed. This type of tracking implies that no field policies are created.
If you only need to track an update operation by recording the current value, the Level field in the Policy table can be set to do this. As a result, the Operation field for an operation type of update indicates “After Update” values are recorded for a change in the Change Tracking and Change Tables.
If you need to track both the “before update” and “after update” values, the Level field in the Policy table can be set to do this. The Change Tracking Table adds one record to record the operation and the Change Table adds two records, one record for the “before update” field values, and the other for the “after update” field values.
If a field is deleted from the source table and the field has an active policy, the corresponding field in the Change Table is not deleted. After the deletion of the source fields, the corresponding field in the Change Table stores a null value in the field.
If all Source fields that have active CDC policies are deleted, there is nothing to be captured, and neither the Tracking nor Change record is recorded.
Consider the following when designing field policies:
Each selected field generates a field policy.
The selected fields can be of any basic data type including raw, and array, however LOB columns are not supported.
The field order (field position) in the Change Table does not have to match the position in the Source Data Table.
The field order in the Change Table is controlled by the pre-defined meta-data columns as leading fields, followed by identifying fields in their assigned component order, followed by non-identifying fields in policy defined order.
Identifying fields cannot be of the data types: lob, raw, arrays.
Selected fields in the Source Data table do not have to be contiguous.
The fields names in the Source Data table are the source of the field names for the Change Table fields.
If you only need to capture the fact the data changed, the Level field can be set to only capture the Change Tracking record.