I have strange problem, I turned on CDC on my SQL Server table.
When I use cdc.fn_cdc_get_net_changes_<capture_instance> function I get latest state of row with performed operation but with no understandable operation value for me. Issue is that cdc.fn_cdc_get_all_changes_<capture_instance> returns the following result:
| __$start_lsn | __$seqval | __$operation | id | value |
|---|---|---|---|---|
| 0x00007772412111 | 0x000077724AAA1244 | 1 | 123 | 5 |
| 0x00007772412111 | 0x000077724AAA1244 | 2 | 123 | 6 |
there are only 1 and 2 operations (delete and insert) for specific row, but
cdc.fn_cdc_get_net_changes_<capture_instance> returns the following result:
| __$start_lsn | __$operation | __$update_mask | id | value |
|---|---|---|---|---|
| 0x00007772412111 | 4 | NULL | 123 | 6 |
I have completely no idea why cdc.fn_cdc_get_net_changes_<capture_instance> returns 4 as value, instead of 2 because it was the last operation returned by cdc.fn_cdc_get_all_changes_<capture_instance>.
I was expecting such result:
| __$start_lsn | __$operation | __$update_mask | id | value |
|---|---|---|---|---|
| 0x00007772412111 | 2 | NULL | 123 | 6 |
Do you have any idea why is there such result?