sys.fn_cdc_has_column_changed (Transact-SQL)

 

Applies To: SQL Server (starting with 2008)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Identifies whether the specified update mask indicates that the specified column has been updated in the associated change row.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

  
sys.fn_cdc_has_column_changed ( 'capture_instance','column_name' , update_mask )  

' capture_instance '
Is the name of the capture instance. capture_instance is sysname.

' column_name '
Is the captured column of the specified capture instance to report on. column_name is sysname.

update_mask
Is the mask identifying updated columns in any associated change row. update_mask is varbinary(128).

bit

You can use this function to extract information from an update mask returned in a query for change data. It is most useful in post-processing the update mask when you need to know whether a particular column in the associated change row has been modified. For more information, see About Change Data Capture (SQL Server).

When this information will be returned as part of a change data query, we recommend that you use the functions sys.fn_cdc_get_column_ordinal and sys.fn_cdc_is_bit_set instead of this function. Use the function fn_cdc_get_column_ordinal before querying for change data so that the desired column ordinal is only computed once. Use fn_cdc_is_bit_set within the query to extract the information from the update mask for each returned row.

Requires membership in the sysadmin fixed server role or db_owner fixed database role. For all other users, requires SELECT permission on all captured columns in the source table and, if a gating role for the capture instance was defined, membership in that database role.

cdc.<capture_instance>_CT (Transact-SQL)
cdc.captured_columns (Transact-SQL)

Community Additions

ADD
Show: