Sample Header Ad - 728x90

Weird behaviour in CDC SQL Server - could be a bug?

-2 votes
1 answer
63 views
Something I can't understand this behavior and it seems to me bug in SQL Server: I have enabled CDC for table Editorial_schedule_index_cards, now when I update a record from this table with this way:
update esic set target_name = 'test8'
from Editorial_Schedule_Index_Cards esic
inner join editorial_schedule es on es.Index_Card_ID = esic.Index_Card_ID 
where es.last_updated_by = 'IMG92'
and then query the CDC table as below so the output is no record, which is normal, specially target_name is not part of captured columns
select *
from cdc.dbo_editorial_schedule_index_cards_ct
where index_card_id =709339
but when I do the update as the following by using variable in the where clause and point to the same value:
declare @LastUpdatedBy varchar (50)
set @LastUpdatedBy= 'IMG92'
update esic set target_name = 'test8'
from Editorial_Schedule_Index_Cards esic
inner join editorial_schedule es on es.Index_Card_ID = esic.Index_Card_ID 
where es.last_updated_by = @LastUpdatedBy
which is basically I put 'IMG92' into variable, and then query as below
select *
from cdc.dbo_editorial_schedule_index_cards_ct
where index_card_id = 709339
so I got two records, one as Delete and the other as Add - why? It seems the record is deleted and recreated?
Asked by JPNN
Jan 15, 2025, 04:24 AM
Last activity: Jan 15, 2025, 12:56 PM