Let's talk a little bit about CDC usage today.

Starting with 08, SQL Server provides a feature called change data capture CDC, which enables you to view the changes in the data of the table objects in the database by enabling this feature. (I feel like a bit of a data change function that SQL Server provides directly to the user.)

According to the official statement. Using the CDC feature has the following benefits

1, this is the database comes with the function, no additional development costs.

2, do not need to change the structure of the table, do not need to use the trigger, stored procedures and other means to record

3, the performance overhead is minimized. (This I have not confirmed ╮ (╯_╰) ╭)

It's easy to use CDC.

1. Turn the database display on CDC tracking

EXEC sys.sp_cdc_enable_db

2. Then I create a table and open CDC on it. The simplest parameters are listed here, and the trace can also be set to the specific capture of which columns have changed. Specific reference

sys.sp_cdc_enable_table:https://msdn.microsoft.com/zh-cn/library/bb522475 (v=sql.120). aspx

CREATE TABLEAAA2 (IDINT PRIMARY KEY, Col1NVARCHAR( -), Col2INT)EXECSys.sp_cdc_enable_table@source_schema = 'dbo',     @source_name = 'AAA2'

3. Start the proxy server. (You can make the trace create successfully without booting, but it doesn't work.)

4, then we can see in the system table more than one CDC's architecture and then the box is the one that opened the AAA2 tracking.

5, then you can go to AAA2 inside to do some operation, see if it is effective ~

INSERT  intodbo. AAA2 (ID, col1,col2)VALUES(1,'pp', the),(2,'BB',234),(3,'cc', A )UPDATEDbo. AAA2SETCol1= 'DD'    WHEREId= 3DELETE  fromDbo. AAA2WHEREId= 2SELECT *  fromcdc.dbo_aaa2_ct__$start_lsn __$end_lsn __$seqval __$operation __$update_mask ID Col 1 Col2---------------------- ------------- ---------------------- ------------ -------------------- ---- ------ ---------- -0x0000015b0001378f0019 NULL          0x0000015b0001378f0016 2            0x07                 1Pp the0x0000015b0001378f0019 NULL          0x0000015b0001378f0017 2            0x07                 2Bb2340x0000015b0001378f0019 NULL          0x0000015b0001378f0018 2            0x07                 3Cc A0x0000015b000137a50003 NULL          0x0000015b000137a50002 3            0x02                 3Cc A0x0000015b000137a50003 NULL          0x0000015b000137a50002 4            0x02                 3Dd A0x0000015b000137ab0005 NULL          0x0000015b000137ab0002 1            0x07                 2Bb234

There is an east, the CDC, which means that can not be used truncate ~ will be prompted not to use Yo ~, as for the results of the basic understanding of it?

A few instructions,

__$operation  : 1 Delete 2 new 3 changes (value before modification) 4 modify (modified value)

__$update_mask: That  column did the operation, 02 is 0010 for the second column, that 07 is 0111 means that 123 columns have been modified Luo ~ on the sauce ~

6 See here still feel this function good is not AH ~ but I think there is still a little inconvenient. For example, when you need to modify the table structure ~

Chestnut 1, delete a column Col2 and then insert 2 data, and then there will be Col2 column (because there is no need to change the structure of ╮ (╯_╰) ╭), and then trace the value set to empty

ALTER TABLEDbo. AAA2DROP COLUMNCol2INSERT  intodbo. AAA2 (ID, Col1)VALUES(4N'DD'),(5N'EE') __$start_lsn __$seqval __$operation __$update_mask ID Col1 Col20x0000015b000138f40004    0x0000015b000138f40002    2    0x07    4DdNULL0x0000015b000138f40004    0x0000015b000138f40003    2    0x07    5EeNULL

Chestnut 2, and then my brain pumping Col2 add in ~ but I changed to a string type, and then insert data from the new, Col2 no value Ah!!! That is normal, because it is the structure, although the name is the same, but the ColumnID is not the same AH! So it's normal to track down.

ALTER TABLEDbo. AAA2ADDCol2NVARCHAR( -)INSERT  intodbo. AAA2 (ID, Col1, Col2)VALUES(6,--Id-intN'JJ',--Col1-nvarchar (+)          'JJJ'  --Col2-int) __$start_lsn __$seqval __$operation __$update_mask ID Col1 Col20x0000015b000138f40004    0x0000015b000138f40002    2    0x07    4DdNULL0x0000015b000138f40004    0x0000015b000138f40003    2    0x07    5EeNULL0x0000015b000139640003    0x0000015b000139640002    2    0x07    6JjNULL

Chestnut 3, then I can update the total! When the update is the meta-structure of the column, it can be updated successfully, but if the new column has been changed, it will not catch, normal (think that since you can configure a traceable column, then there is no change in the list of columns not captured, this is the reason Luo ~)

UPDATEDbo. AAA2SETCol2= 'ee' WHEREId= 4UPDATEDbo. AAA2SETCol1= 'III' WHEREId= 6__$start_lsn __$seqval __$operation __$update_mask ID Col1 Col20x0000015b000139c00003    0x0000015b000139c00002    4    0x02    6IiiNULL

So how do you deal with this problem?

Make a capture instance from the heart ~ Make a capture instance from the heart-make a capture instance from the heart important thing to say 3 ╮ (╯_╰) ╭. It's the only way.

In summary, the CDC I described here ~ what I found, I will add ~

or wish you a Happy New Year ~

Let's talk a little bit about CDC usage today.