Sql-docs: Enabled CDC on CCI

[Avyncentia]

Is Change Data Capture not supported for CCIs? I get a failure when I try to turn it on for a CCI, but I can't find any reference to this limitation in the documentation for CDC or CCIs.

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 3bd2adf6-0543-7ca3-3bd5-dd51d0499d7c
• Version Independent ID: 7fdb189c-e1f2-5a99-e2f8-2f683467e016
• Content: Enable and Disable Change Data Capture - SQL Server
• Content Source: docs/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server.md
• Product: sql
• Technology: **
• GitHub Login: @rothja
• Microsoft Alias: jroth

[WilliamAntonRohm]

@Avyncentia -- thank you for your question. Please consider these resources:

• SQL Server on Microsoft Q&A
• DBA Stack Exchange
• Stack Overflow

@rothja -- please look into this issue.

[rothja]

@Avyncentia Thank you for the question. I assume by CCI, you are referring to Clustered Columnstore Indexes? If so, I see in the documentation the following for SQL Server 2014+:

• Change tracking. You cannot use change tracking with columnstore indexes.
• Change data capture. You cannot use change data capture for nonclustered columnstore index (NCCI) because they are read-only. It does work for clustered columnstore indexes (CCI).

It looks like for SQL Server 2014+, it should wok for CCI but not for NCCI. Does this not match what you are seeing? What version of SQL Server are you on? Thank you!

[Avyncentia]

Thanks for the quick response. Yes, I meant clustered columnstore indexes. We're using SQL Server Standard 2019.

So far what I'm seeing doesn't match that documentation. I have a table with a clustered columnstore index, a unique non-clustered index, and a non-unique non-clustered index. When we try to enable CDC on the table, we get 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...

This is the code we've been using to enable CDC on the table:

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo'
,@source_name = 'METRIC_DATA_MAIN'
,@role_name = NULL
,@filegroup_name = [TBD] --separate filegroup per Microsoft recommendations 
,@supports_net_changes = 0
,@index_name = NULL
,@captured_column_list = NULL

I can send you the DDL scripts for the table if that would be helpful.

[rothja]

@Avyncentia Thanks for the additional details. That would be helpful. Can you paste the DDL to create the table into this git issue? That would speed up the repo, and then I can reach out to others internally to verify whether the documentation is correct or needs to be clarified.

[Avyncentia]

/****** Object:  Table [dbo].[METRIC_DATA_MAIN]    Script Date: 10/5/2020 12:18:10 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[METRIC_DATA_MAIN](
    [CUSTOMER_ID] [int] NOT NULL,
    [RPT_PERIOD_TYPE_C] [tinyint] NOT NULL,
    [RPT_PERIOD_DATE] [date] NOT NULL,
    [METRIC_ID] [int] NOT NULL,
    [SUM_LEVEL] [nvarchar](50) NOT NULL,
    [TARGET_ID] [nvarchar](300) NOT NULL,
    [PROC_VALUE] [numeric](19, 4) NULL,
    [VALUE_A] [numeric](19, 4) NULL,
    [VALUE_B] [numeric](19, 4) NULL,
    [EXCLUDED] [numeric](19, 4) NULL,
    [QUESTIONABLE] [tinyint] NULL,
    [VALIDATED] [bit] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[METRIC_DATA_MAIN] ADD  CONSTRAINT [DFT_MDM_QUESTIONABLE]  DEFAULT ((0)) FOR [QUESTIONABLE]
GO


/****** Object:  Index [CC_METRIC_DATA_MAIN]    Script Date: 10/5/2020 12:18:51 PM ******/
CREATE CLUSTERED COLUMNSTORE INDEX [CC_METRIC_DATA_MAIN] ON [dbo].[METRIC_DATA_MAIN] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]
GO

/****** Object:  Index [AK_METRIC_DATA_MAIN]    Script Date: 10/5/2020 12:18:31 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [AK_METRIC_DATA_MAIN] ON [dbo].[METRIC_DATA_MAIN]
(
    [RPT_PERIOD_TYPE_C] ASC,
    [RPT_PERIOD_DATE] ASC,
    [METRIC_ID] ASC,
    [SUM_LEVEL] ASC,
    [CUSTOMER_ID] ASC,
    [TARGET_ID] ASC
)
INCLUDE([PROC_VALUE],[VALUE_A],[VALUE_B],[EXCLUDED],[QUESTIONABLE],[VALIDATED]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

/****** Object:  Index [AK_METRIC_DATA_MAIN2]    Script Date: 10/5/2020 12:18:43 PM ******/
CREATE NONCLUSTERED INDEX [AK_METRIC_DATA_MAIN2] ON [dbo].[METRIC_DATA_MAIN]
(
    [RPT_PERIOD_TYPE_C] ASC,
    [RPT_PERIOD_DATE] ASC,
    [METRIC_ID] ASC,
    [SUM_LEVEL] ASC,
    [PROC_VALUE] ASC
)
INCLUDE([VALUE_A],[VALUE_B],[EXCLUDED],[QUESTIONABLE],[VALIDATED]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

[rothja]

@Avyncentia Really appreciate it. I'll be looking into this and get back to you. Thanks!

[Avyncentia]

@Avyncentia -- thank you for your question. Please consider these resources:

* [SQL Server on Microsoft Q&A](https://docs.microsoft.com/en-us/answers/products/sql-server)

* [DBA Stack Exchange](https://dba.stackexchange.com/questions/tagged/sql-server)

* [Stack Overflow](https://stackoverflow.com/questions/tagged/sql-server)

Closing the loop on this comment: I've posted the issue to SQL Server on Microsoft Q&A and DBA Stack Exchange.

[rothja]

@Avyncentia I simplified this to make sure it wasn't anything else, but I get the same error. I'll talk to the product team to see if we can/should change the documentation here. Thanks for calling this out.

PRINT @@VERSION 
GO
CREATE DATABASE dbTest
GO
USE dbTest
GO
EXEC sys.sp_cdc_enable_db  
GO
CREATE TABLE [dbo].[Test]([ID] [int] NOT NULL)
GO
CREATE CLUSTERED COLUMNSTORE INDEX [CC_Test] ON [dbo].[Test] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0)
GO
EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo'
,@source_name = 'Test'
,@role_name = NULL
GO
USE master
GO
DROP DATABASE dbTest
GO

[rothja]

@Sait-MSFT Abdul, could you help us look at this issue. In the documentation for column store indexes, it states:

"Change data capture. You cannot use change data capture for nonclustered columnstore index (NCCI) because they are read-only. It does work for clustered columnstore indexes (CCI)."

This was from article CREATE COLUMNSTORE INDEX (Transact-SQL)

This implies that CDC does work for CLUSTERED columnstore indexes after SQL Server 2014. However, from the tests you can see in this git issue, we get the following error:

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

Can you verify that clustered columnstore indexes are NOT supported for CDC. If so, I will change the line in the reference article to indicate this. Thank you! #assign:Sait-MSFT

[Sait-MSFT]

This is incorrect for SQL 2014 and should be changed to

"Change data capture cannot be enabled on tables with a clustered or non-clustered columnstore index."

For SQL 2016, this is a little different. We should also call that out in the appropriate location. That should read. "In SQL 2016 and above, change data capture cannot be enabled on tables with a clustered columnstore index, but it can be enabled on tables with non-clustered columnstore index"

[rothja]

@Sait-MSFT Thank you. I will make the change to the article. @Avyncentia I know this does not help you use CDC on your table, but I hope it clears up the confusion. Thanks for raising this issue with us!

[Avyncentia]

Thank you both. @rothja , it sounds like you're updating the CREATE CLUSTERED COLUMNSTORE INDEX article. Would you also be able to add something to this effect to the main CDC article or somewhere else that seems appropriate, since this is a notable limitation of the feature?

[rothja]

@Avyncentia Yes, that is a great idea. I'll look for a good spot to surface this in more than one place and consider the main article you're pointing to.