CDC and deferred updates

Category: sql server dw

Question

aj131tech on Fri, 15 Mar 2013 11:14:13


I am using CDC to generate events based on the entries in the CDC tables. I need to distingush between insert, update and delete events but when SQL chooses to do a deferred update, it changes an update into a delete and insert - is there a way I can affect this behaviour so that any update command results in an update record in the cdc tables?

I am aware of the DBCC TRACEON (8207,-1) but this seems to only apply to update commands affecting 1 record and which don't affect fields used by unique constraints - my update commands will affect multiple records and fields with unique constraints applied to them.

Replies

Shehap on Mon, 18 Mar 2013 01:42:38


You can use instead of Trigger for any update on the related tables to transform update transactions to the needed behavior

Please let me know if any further help is needed 

aj131tech on Mon, 18 Mar 2013 10:00:26


Thanks for the trigger suggestion Shehap (and to Kalman for moving this thread to a more suitable forum -I'm still learning the ropes here).

How would the trigger work - SQL Server is internally translating an update statement into a delete/insert so even if I did an instead of trigger to do my own update command logic - wouldn't my update also be intercepted and changed to a deferred delete/insert anyway?

I would also have problems implementing the "instead of" trigger as it is a 3rd party system although we do have responsibility for the DBA work on it ourselves (and so are allowed to configure it for CDC).

Also seems a shame to have to complicate CDC implementation with triggers when our main aim in introducing CDC was to remove the reliance on triggers. 

Piotr Palka on Mon, 25 Mar 2013 06:00:37


As I know DBCC TRACEON (8207,-1) is the only solution proposed by Microsoft - command is really executed as DELETE and then INSERT and replication reflects it.

I'm afraid that you will have to add logic at the subscriber to find out, if INSERT is a real INSERT or a part of UPDATE operation.

rrozema on Tue, 24 Mar 2015 10:46:08


This problem is caused by a bug in the fn_cdc_get_net_changes_<capture_instance> functions. The bug works in 2 ways, the first is indentified in this thread: an additional row is exported with __$operation = 1. A 2nd problem, resulting from the same bug, is that some rows with __$operation = 1 are incorrectly suppressed. These missing rows are however less easily spotted and thus this incarnation of the problem is nowhere reported.

The bug is reported on Connect as ID 690476 back in 2011 already. Below is a copy of the corrected cdc.fn_get_net_changes_dbo_NETTEST function in my test database. The fix can easily be extracted from this sample code. I would suggest you do not adapt the functions yourself in a production environment. Instead we should put all our combined powers in to get Microsoft to fix this issue. Please Vote and if possible have the case reopened as soon as possible. 

	create function [cdc].[fn_cdc_get_net_changes_dbo_NETTEST]
	(	@from_lsn binary(10),
		@to_lsn binary(10),
		@row_filter_option nvarchar(30)
	)
	returns table
	return

	select NULL as __$start_lsn,
		NULL as __$operation,
		NULL as __$update_mask, NULL as [ID], NULL as [A]
	where ( [sys].[fn_cdc_check_parameters]( N'dbo_NETTEST', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 0)

	union all
	
	select __$start_lsn,
	    case __$count_23BAE034
	    when 1 then __$operation
	    else
			case __$min_op_23BAE034 
				when 2 then 2
				when 4 then
				case __$operation
					when 1 then 1
					else 4
					end
				else
				case __$operation
					when 2 then 4
					when 4 then 4
					else 1
					end
			end
		end as __$operation,
		null as __$update_mask , [ID], [A]
	from
	(
		select t.__$start_lsn as __$start_lsn, __$operation,
		case __$count_23BAE034 
		when 1 then __$operation 
		else
		(	select top 1 c.__$operation
			from [cdc].[dbo_NETTEST_CT] c with (nolock)   
			where  ( (c.[ID] = t.[ID]) )  
			and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
			and (c.__$start_lsn <= @to_lsn)
			and (c.__$start_lsn >= @from_lsn)
			order by c.__$seqval) end __$min_op_23BAE034, __$count_23BAE034, t.[ID], t.[A] 
		from [cdc].[dbo_NETTEST_CT] t with (nolock) inner join 
		(	select  r.[ID], max(r.__$seqval) as __$max_seqval_23BAE034,
		    count(*) as __$count_23BAE034 
			from [cdc].[dbo_NETTEST_CT] r with (nolock)   
			where  (r.__$start_lsn <= @to_lsn)
			and (r.__$start_lsn >= @from_lsn)
			group by   r.[ID]) m
		on t.__$seqval = m.__$max_seqval_23BAE034 and
		    ( (t.[ID] = m.[ID]) ) 	
		where lower(rtrim(ltrim(@row_filter_option))) = N'all'
			and ( [sys].[fn_cdc_check_parameters]( N'dbo_NETTEST', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
			and (t.__$start_lsn <= @to_lsn)
			and (t.__$start_lsn >= @from_lsn)
            and ((t.__$operation = 2) or (t.__$operation = 4) or 
                 ((t.__$operation = 1) and not exists (
                        select top(1) *
                        from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                        where  ( (c.[ID] = t.[ID]) )
                           and c.__$operation = 2
                           and c.__$start_lsn = t.__$start_lsn
                           and c.__$seqval = t.__$seqval
                     )
                  --(2 not in 
                  --      (   select top 1 c.__$operation
                  --          from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                  --          where  ( (c.[ID] = t.[ID]) )  
                  --          and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
                  --          and (c.__$start_lsn <= @to_lsn)
                  --          and (c.__$start_lsn >= @from_lsn)
                  --          order by c.__$operation desc
                  --       ) 
                  --)
                 )
            )
            and t.__$operation = (
                select
                    max(mo.__$operation)
                from
                    [cdc].[dbo_NETTEST_CT] as mo with (nolock)
                where
                    mo.__$seqval = t.__$seqval
                    and 
                     ( (t.[ID] = mo.[ID]) ) 
                group by
                    mo.__$seqval
            )
	) Q
	
	union all
	
	select __$start_lsn,
	    case __$count_23BAE034
	    when 1 then __$operation
	    else
			case __$min_op_23BAE034 
				when 2 then 2
				when 4 then
				case __$operation
					when 1 then 1
					else 4
					end
				else
				case __$operation
					when 2 then 4
					when 4 then 4
					else 1
					end
			end
		end as __$operation,
		case __$count_23BAE034
		when 1 then
			case __$operation
			when 4 then __$update_mask
			else null
			end
		else	
			case __$min_op_23BAE034 
			when 2 then null
			else
				case __$operation
				when 1 then null
				else __$update_mask 
				end
			end	
		end as __$update_mask , [ID], [A]
	from
	(
		select t.__$start_lsn as __$start_lsn, __$operation,
		case __$count_23BAE034 
		when 1 then __$operation 
		else
		(	select top 1 c.__$operation
			from [cdc].[dbo_NETTEST_CT] c with (nolock)
			where  ( (c.[ID] = t.[ID]) )  
			and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
			and (c.__$start_lsn <= @to_lsn)
			and (c.__$start_lsn >= @from_lsn)
			order by c.__$seqval) end __$min_op_23BAE034, __$count_23BAE034, 
		m.__$update_mask , t.[ID], t.[A]
		from [cdc].[dbo_NETTEST_CT] t with (nolock) inner join 
		(	select  r.[ID], max(r.__$seqval) as __$max_seqval_23BAE034,
		    count(*) as __$count_23BAE034, 
		    [sys].[ORMask](r.__$update_mask) as __$update_mask
			from [cdc].[dbo_NETTEST_CT] r with (nolock)
			where  (r.__$start_lsn <= @to_lsn)
			and (r.__$start_lsn >= @from_lsn)
			group by   r.[ID]) m
		on t.__$seqval = m.__$max_seqval_23BAE034 and
		    ( (t.[ID] = m.[ID]) ) 	
		where lower(rtrim(ltrim(@row_filter_option))) = N'all with mask'
			and ( [sys].[fn_cdc_check_parameters]( N'dbo_NETTEST', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
			and (t.__$start_lsn <= @to_lsn)
			and (t.__$start_lsn >= @from_lsn)
            and ((t.__$operation = 2) or (t.__$operation = 4) or 
                 ((t.__$operation = 1) and not exists (
                        select top(1) *
                        from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                        where  ( (c.[ID] = t.[ID]) )
                           and c.__$operation = 2
                           and c.__$start_lsn = t.__$start_lsn
                           and c.__$seqval = t.__$seqval
                     )
                  --(2 not in 
                  --      (   select top 1 c.__$operation
                  --          from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                  --          where  ( (c.[ID] = t.[ID]) )  
                  --          and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
                  --          and (c.__$start_lsn <= @to_lsn)
                  --          and (c.__$start_lsn >= @from_lsn)
                  --          order by c.__$operation desc
                  --       ) 
                  -- )
                 )
            )
            and t.__$operation = (
                select
                    max(mo.__$operation)
                from
                    [cdc].[dbo_NETTEST_CT] as mo with (nolock)
                where
                    mo.__$seqval = t.__$seqval
                    and 
                     ( (t.[ID] = mo.[ID]) ) 
                group by
                    mo.__$seqval
            )
	) Q
	
	union all
	
		select t.__$start_lsn as __$start_lsn,
		case t.__$operation
			when 1 then 1
			else 5
		end as __$operation,
		null as __$update_mask , t.[ID], t.[A]
		from [cdc].[dbo_NETTEST_CT] t  with (nolock) inner join 
		(	select  r.[ID], max(r.__$seqval) as __$max_seqval_23BAE034
			from [cdc].[dbo_NETTEST_CT] r with (nolock)
			where  (r.__$start_lsn <= @to_lsn)
			and (r.__$start_lsn >= @from_lsn)
			group by   r.[ID]) m
		on t.__$seqval = m.__$max_seqval_23BAE034 and
		    ( (t.[ID] = m.[ID]) ) 	
		where lower(rtrim(ltrim(@row_filter_option))) = N'all with merge'
			and ( [sys].[fn_cdc_check_parameters]( N'dbo_NETTEST', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)
			and (t.__$start_lsn <= @to_lsn)
			and (t.__$start_lsn >= @from_lsn)
            and ((t.__$operation = 2) or (t.__$operation = 4) or 
                 ((t.__$operation = 1) and not exists (
                        select top(1) *
                        from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                        where  ( (c.[ID] = t.[ID]) )
                           and c.__$operation = 2
                           and c.__$start_lsn = t.__$start_lsn
                           and c.__$seqval = t.__$seqval
                     )
                  --(2 not in 
                  --      (   select top 1 c.__$operation
                  --          from [cdc].[dbo_NETTEST_CT] c with (nolock) 
                  --          where  ( (c.[ID] = t.[ID]) )  
                  --          and ((c.__$operation = 2) or (c.__$operation = 4) or (c.__$operation = 1))
                  --          and (c.__$start_lsn <= @to_lsn)
                  --          and (c.__$start_lsn >= @from_lsn)
                  --          order by c.__$operation desc
                  --       ) 
                  -- )
                 )
            )
            and t.__$operation = (
                select
                    max(mo.__$operation)
                from
                    [cdc].[dbo_NETTEST_CT] as mo with (nolock)
                where
                    mo.__$seqval = t.__$seqval
                    and 
                     ( (t.[ID] = mo.[ID]) ) 
                group by
                    mo.__$seqval
            )