CDC changes data capture technology issues and limitations, cdc captures

CDC changes data capture technology issues and limitations, cdc captures
The current cdc function will still disappoint you.
The advantage is not mentioned, that is, those written in online books.
Disadvantages:
1. So far, cdc cannot be associated with log files. For more useful information, we still need to compare and obtain the data before and after. (The trigger will still be used for replacement record)
2. So far, cdc can record DDl-related processing, but more information, such as recording IP addresses and other user information, still requires additional code support.


Select * into PT_CUSTOMER_INFO_BAK from PT_CUSTOMER_INFO

Similar to oracle's create table as select * from t;

The constraints are not included.


Alt + q
Check the SQL server execution plan.


Clustered index table: similar to oracle's iot table, index table is the same concept. Tables and indexes are the same, that is, tables are sorted.

Non-clustered index table: a heap table

Execution Plan:

1. [Table Scan]: traverses the entire Table and searches for matched record rows. This operation will check one row at a time. Of course, the efficiency is also the worst.

2. [Index Scan]: according to the Index, some records are filtered out from the table, and then the matched record rows are searched. The display range is smaller than that of the first method, therefore, it is faster than [Table Scan.

3. [Index Seek]: locate (obtain) the location where the record is stored Based on the Index and then obtain the record. Therefore, it is faster than the first two methods.

4. Clustered Index Scan: Same as Table Scan. Note: Do not think that there is an Index here, so it is different.

In fact, it means that each row of records is scanned row by clustered index, because records are stored in the order of clustered index.

[Table Scan] Only says: the Table to be scanned has no clustered index, so these two operations are essentially the same.

5. [Clustered Index Seek]: Obtain records directly based on the Clustered Index, the fastest!


There is also a small problem. The method for deleting an index is different from that for deleting a primary key because the primary key is a constraint rather than an index.

Delete index: drop index IDX_PROVINCE_ID on PT_CUSTOMER_INFO_BAK

Delete primary key: alter table PT_CUSTOMER_INFO_BAK drop constraint PK_PT_CUSTOMER_INFO_BAK

Add primary key:

Alter table PT_CUSTOMER_INFO_BAK
Add constraint PK_PT_CUSTOMER_INFO_BAK primary key nonclustered (ID );
Go