How to drop a clustered columnstore index?

Question

How can I drop a clustered columnstore index on a table?

I am trying to alter the length of a column, but getting this error:

The statement failed because a secondary dictionary reached the maximum size limit.
Consider dropping the columnstore index, altering the column, then creating a new columnstore index.

I have a table that looks like this, roughly:

CREATE TABLE [dim].[Ticket]
( 
    [ID] [bigint]  NULL,
    [Rev] [int]  NULL,
    [Timestamp] [datetime2](7)  NULL,
    [Title] [varchar](260)  NULL,
    [Description] [varchar](4005)  NULL
)
WITH
(
    DISTRIBUTION = HASH ( [ID] ),
    CLUSTERED COLUMNSTORE INDEX
)

When I try variations of this recommendation:

https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-index-transact-sql?view=sql-server-ver16

I just get errors.

Answer 1

I checked that this works on Synapse too. Discover the CCI's name with

select * from sys.indexes where object_id = object_id('dim.Ticket')

then drop it

drop index ClusteredIndex_fdddc3c574214a2096190cbc54f58cc4 on dim. Ticket

You'll then have a heap. When you're ready re-compress the table with

create clustered columnstore index cci_dim_ticket on dim.Ticket

But it would be more efficient to create a new table with a CTAS, and then rename and drop the old one. Dropping the CCI actually requires rewriting the table as an uncompressed heap, which you can skip with CTAS.