Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

sys.fn_cdc_get_column_ordinal (Transact-SQL)

Returns the column ordinal of the specified column as it appears in the change table associated with the specified capture instance.

Topic link iconTransact-SQL Syntax Conventions


sys.fn_cdc_get_column_ordinal ('capture_instance','column_name')
'capture_instance'

Is the name of the capture instance in which the specified column is identified as a captured column. capture_instance is sysname.

'column_name'

Is the column to report on. column_name is sysname.

This function is used to identify the ordinal position of a captured column within the change data capture update mask. It is principally used in conjunction with the function sys.fn_cdc_is_bit_set to extract information from the update mask when querying for change data.

Requires SELECT permission on on all captured columns of the source table. If a database role for the change data capture component is specified for the capture instance, membership in that role is also required.

The following example obtains the ordinal position of the VacationHours column in the update mask for the HumanResources_Employee capture instance. That value is then used in the call to sys.fn_cdc_is_bit_set to extract information from the returned update mask.

USE AdventureWorks;
GO
DECLARE @VacationHoursOrdinal int;
SET @VacationHoursOrdinal = 
sys.fn_cdc_get_column_ordinal ( 'HumanResources_Employee','VacationHours');
SELECT sys.fn_cdc_is_bit_set(@VacationHoursOrdinal, __$update_mask)
  as 'VacationHoursChanged', *
FROM cdc.fn_get_net_changes_HumanResources_Employee
    (@from_lsn, @to_lsn, 'all with mask');
GO
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.