Adding a derived column

You can create a derived column to move the processing of an expression from the target instance to the source instance.

About this task

Procedure

  1. Click Configuration > Subscriptions.
  2. Select the subscription.
  3. Click the Table Mappings view and select the table mapping from the Source Table column.
  4. Right-click and select Open Details....
  5. Click the Column Mappings tab.
  6. Expand the Source Columns list and double-click New Derived Column.
  7. If you already have a source column in your RDBMS on which you want to base the properties of your derived column, click Copy Column and select the column from the list of tables.

    The properties of this column (the data type, length, and any precision) are used as the properties of your derived column.

  8. Enter a name for the derived column in the Name box.

    This name must be unique.

  9. Enter a brief description of the derived column in the Description box.
  10. Select a data type of the result from the Data Type list.
  11. Enter the maximum length for the returned value in the Length box.
  12. Select an evaluation frequency:
    • After Image Only—Select this option when you want CDC Replication to evaluate the expression in the derived column for the after image of the source table.
    • Before and After Images—Select this option when you want CDC Replication to evaluate the expression in the derived column for both the before and after images of the source table.
  13. If you selected the After Image Only value, evaluate it for performance reasons.

    Derived columns and the expressions you build for them are evaluated on source tables.

  14. If you selected the Before and After Images (*BTH) value, an evaluation is only necessary when you are performing conflict detection and resolution, which requires the before image to recognize conflicts, or when you are auditing so that you can audit the full before image. You also need to select this evaluation frequency if the target column to which you mapped the derived column is a primary key column. This maintains database integrity.

    Derived columns and the expressions you build for them are evaluated on source tables.

  15. Click Editor to build the expression for the derived column.
  16. Click Verify to verify the syntax of the expression.
  17. Click OK to return to the Define Derived Column dialog box.
  18. Click OK.
  19. Click Save.