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.
CodePudding user response:
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.