question

rahulG-7170 avatar image
0 Votes"
rahulG-7170 asked AmeliaGu-msft answered

Is there any recommendation from microsoft to create index on CDC system tables(cdc.dbo_mytable_CT)

Hi, I would like to know is it ok to create indexes on CDC system tables ?
Is there any recommendation from microsoft regarding the same ?
I would like to know will there any performance issues post creating indexes on CDC system tables?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi rahulG-7170,
Welcome to Microsoft Q&A.
According to my test, we can create non-clustered index on the cdc.<capture_instance>_CT table.

133385-image.png


133431-image.png

But it is not recommended to query system tables directly. Instead, execute the cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance> functions. Please refer to this doc. So I think it is better not to create index on the CDC system index.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



image.png (26.5 KiB)
image.png (11.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.