0

I'm trying to enable Change Data Capture on a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index in SQL Server Standard 2019. The command fails with this error:

Msg 35353, Level 16, State 1, Procedure sys.sp_cdc_enable_table_internal, 
Line 243 [Batch Start Line 16]
Change Data Capture cannot be enabled on a table with a clustered columnstore index. 
Consider dropping clustered columnstore index

I don't understand why I'm getting this error since, as far as I can tell from Microsoft documentation, CDC is supported for CCIs.

Has anyone else run into this error or know why it's occurring?

Improve this question
3
  • try creating a primary key/unique non-clustered index to give rows formal identity so that CDC will function. – Conor Cunningham MSFT Oct 5 '20 at 17:48
  • The table already has a unique non-clustered index. – Avyncentia Oct 6 '20 at 19:10
  • Apologies - I checked the code and indeed it is supposed to fail. Can you point out where you say the docs says it should work? – Conor Cunningham MSFT Oct 6 '20 at 20:52
0

I just heard back from Microsoft. The documentation on CCIs and CDC is incorrect/misleading. Change Data Capture is not supported for clustered columnstore indexes. It is supported for nonclustered columnstore indexes starting in SQL Server 2016. They are correcting the documentation.

Improve this answer
0

The workaround to this limitation is posted on SO:

  1. Drop a clustered columnstore index if exists.
  2. Enable CDC.
  3. Create a clustered columnstore index.

Voila! It works!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.