如何通过SQL Server的CHANGETABLE获取变更记录的前后版本？

Got it, let's tackle this problem of getting both the old and new versions of changed records in your SQL Server change log. Your current query uses CHANGETABLE, which only pulls in the current state of modified rows (since it joins back to the original table, where old values are overwritten). To get the before-and-after snapshots, we need to leverage SQL Server's Change Data Capture (CDC) underlying tables and functions.

Understanding CDC's Capture Table #

When CDC is enabled on your table, it creates a dedicated capture table (usually named cdc.[SchemaName_TableName]_CT—for your dbo.A_Table, that's cdc.dbo_A_Table_CT). This table stores every change event, including:

• __$start_lsn: Unique log sequence number for the change
• __$operation: Code indicating the type of change:
  • 1 = Delete
  • 2 = Insert
  • 3 = Update (old values of the row)
  • 4 = Update (new values of the row)
• __$update_mask: Bitmask showing which columns were modified
• All columns from your original table, storing either the old or new value depending on the operation

Method 1: Join Capture Table Rows for Side-by-Side Comparison #

This method joins the old and new value records for updates, and includes inserts/deletes with nulls for missing states, giving you a clean side-by-side view:

SELECT
    -- Core change metadata
    change_metadata.__$start_lsn,
    change_metadata.change_type,
    change_metadata.__$update_mask,
    -- Old version values (null for inserts)
    old_values.A_Primary_KEY1 AS Old_KEY1,
    old_values.A_Primary_KEY2 AS Old_KEY2,
    old_values.A_Primary_KEY3 AS Old_KEY3,
    old_values.Column1 AS Old_Column1,
    old_values.Column2 AS Old_Column2,
    -- New version values (null for deletes)
    new_values.Column1 AS New_Column1,
    new_values.Column2 AS New_Column2
FROM (
    -- Combine all change types and label them
    SELECT
        __$start_lsn,
        __$update_mask,
        CASE __$operation
            WHEN 1 THEN 'DELETE'
            WHEN 2 THEN 'INSERT'
            WHEN 3 THEN 'UPDATE_OLD'
            WHEN 4 THEN 'UPDATE_NEW'
        END AS change_type,
        *
    FROM cdc.dbo_A_Table_CT
) AS change_metadata
LEFT JOIN cdc.dbo_A_Table_CT AS old_values
    ON change_metadata.__$start_lsn = old_values.__$start_lsn
    AND change_metadata.A_Primary_KEY1 = old_values.A_Primary_KEY1
    AND change_metadata.A_Primary_KEY2 = old_values.A_Primary_KEY2
    AND change_metadata.A_Primary_KEY3 = old_values.A_Primary_KEY3
    AND old_values.__$operation IN (1, 3) -- Old values for delete/update
LEFT JOIN cdc.dbo_A_Table_CT AS new_values
    ON change_metadata.__$start_lsn = new_values.__$start_lsn
    AND change_metadata.A_Primary_KEY1 = new_values.A_Primary_KEY1
    AND change_metadata.A_Primary_KEY2 = new_values.A_Primary_KEY2
    AND change_metadata.A_Primary_KEY3 = new_values.A_Primary_KEY3
    AND new_values.__$operation IN (2, 4) -- New values for insert/update
-- Filter to avoid duplicate rows for update operations
WHERE NOT (change_metadata.change_type = 'UPDATE_OLD' AND new_values.__$operation IS NOT NULL)
ORDER BY change_metadata.__$start_lsn;

How This Works: #

• We first label all change operations for clarity
• For each change event, we join to get the old value (if it exists) and new value (if it exists)
• The final filter removes duplicate rows that would show up for update operations (since we're combining both old and new records into one row)

Method 2: Use CDC's fn_cdc_get_all_changes Function #

If you prefer a more concise approach, use CDC's built-in function to retrieve all changes, then use CASE statements to split old and new values:

SELECT
    __$start_lsn,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE_OLD'
        WHEN 4 THEN 'UPDATE_NEW'
    END AS change_type,
    __$update_mask,
    -- Old values (only for delete/update-old operations)
    CASE WHEN __$operation IN (1, 3) THEN A_Primary_KEY1 END AS Old_KEY1,
    CASE WHEN __$operation IN (1, 3) THEN Column1 END AS Old_Column1,
    -- New values (only for insert/update-new operations)
    CASE WHEN __$operation IN (2, 4) THEN A_Primary_KEY1 END AS New_KEY1,
    CASE WHEN __$operation IN (2, 4) THEN Column1 END AS New_Column1
FROM
    cdc.fn_cdc_get_all_changes_dbo_A_Table(
        sys.fn_cdc_get_min_lsn('dbo_A_Table'), -- Start at earliest captured LSN
        sys.fn_cdc_get_max_lsn(), -- End at latest captured LSN
        'all update old' -- Include both old and new rows for updates
    )
ORDER BY __$start_lsn;

Key Function Parameter: #

• The third argument 'all update old' ensures that update operations return two rows: one with the old values (__$operation=3) and one with the new values (__$operation=4). The CASE statements let you pivot these into side-by-side columns if needed.

Important Tips #

• Verify CDC Configuration: Make sure CDC is enabled on your database and table. You can check this with:

  EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'A_Table';
  

• Dynamic SQL for Large Tables: If your table has dozens of columns, manually writing CASE statements or joins is tedious. You can generate the SQL dynamically by querying sys.columns for your table.
• Add Timestamps: Convert __$start_lsn to a human-readable time using sys.fn_cdc_map_lsn_to_time(__$start_lsn) to include the exact change time in your report.

内容的提问来源于stack exchange，提问作者samhith gardas