0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
John Stud
  • 1,506
  • 23
  • 46
  • What's the name of the columnstore index? – J.D. Dec 26 '22 at 15:12
  • There is no name, apparently. That is the create table statement generated by SQL. – John Stud Dec 26 '22 at 15:22
  • 1
    When you don't explicitly name the constraint / index, a name is auto-generated. You need the name to be able to drop the index. See if you can find the index name either in whatever UI you use for Synapse or by querying the [`sys.indexes`](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql?view=sql-server-ver16) system view, filtering the `object_id` column on the `OBJECT_ID('dim.Ticket')`. – J.D. Dec 26 '22 at 15:53

1 Answers1

2

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67