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

rahul G 1 Reputation point
2021-09-19T20:59:05.107+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-09-20T03:20:14.747+00:00

    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.

    0 comments