Cannot drop a Columnstore Index

Question

Cannot drop a Columnstore Index

Fredrik M 246

I have a clustred Columnstore Index on a big table (5 billion rows), that I want to drop.

So I understand that dropping a clustered columnstore index will convert the columnstore table to a heap.

When I do this, DROP INDEX, I get an error message saying that disk is full.

I am kind of stuck here. Do I really have to extend the disk size to drop it?

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Olaf Helper 47,586

will convert the columnstore table to a heap.

A column store table is highly compressed and if you want to convert it, you need additional space.

Do I really have to extend the disk size to drop it?

So, yes.

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-10-15T21:28:40.3866667+00:00

In addition, the data must be copied to the heap, and the columnstore is not dropped until all data is in the heap. So you need twice the space of the table to fit both. Or the three or four times the size, given that the heap will take up more space as Olaf points out.

If you have more disk space elsewhere you could create a new table on that disk and copy data over, and then drop the entire columnstore table when you are done.
Fredrik M 246 Reputation points

2024-10-16T06:54:25.8166667+00:00

Well I don't want to convert it, I just want to drop it. But I guess there is no way to drop it without extend disk size then, which is kind of ironic.

"The statement has been terminated.
Msg 1101, Level 17, State 12, Line 122

Could not allocate a new page for database '"DatabaseName"' because the 'PRIMARY' filegroup is full due to lack of storage space or database files reaching the maximum allowed size. Note that UNLIMITED files are still limited to 16TB. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
博雄胡 725 Reputation points

2024-10-16T09:36:16.0366667+00:00

Because your "drop index" is actually executing "create clustered index" and "drop columnstore index" in order. It will first take up more space than it currently does to execute "create clustered index" and then "drop columnstore index". So you can see why it says we're out of space? If you want to drop, you should drop the table, and then of course the data is gone.

You think of them as compressed packages（columnstore index） and compressed files（clustered index）, and now you have a 1 gigabyte hard drive with a 1 gigabyte compressed package stuffed into it, and you want to extract it, what should it do? Only increase the hard disk space.

Answer 2

Fredrik M 246

I found the option WITH (DROP_EXISTING = ON), I could try that and see if there is any difference.

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-10-16T20:55:55.7033333+00:00
That option would possibly help if you wanted to replace the columnstore index with a clustered row index in the sense that you would not have take the step over a heap. (But I don't really expect that to be supported.) In any case, you still need the space for both.

Think of it this way: You have some data on disk, and it has be organised in some way. SQL Server offers three options:

Heap.

Clustered row index.

Clustered columnstore.

You can change the organisation from one to the other, but that always includes copying the data to the new organisation, while the old is still there, so such a reorganisation does require space.

If all you want to do is drop, then as 博雄胡 said, just use DROP TABLE.
Fredrik M 246 Reputation points

2024-10-18T12:48:26.45+00:00

Thanks @Erland Sommarskog !!!
I read this with the help of google;

"You can use CREATE CLUSTERED INDEX...WITH (DROP_EXISTING=ON) to change the existing columnstore clustered index to a b-tree and then CREATE CLUSTERED COLUMNSTORE INDEX...WITH (DROP_EXISTING=ON) to change back to columnstore. This will eliminate the drop index step that changes the columnstore to a heap."

Would this help performance in recreating the columnstore index you think?
Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2024-10-18T14:48:50.8866667+00:00

Recreate? Prevoiusly, you only talked about drop? Maybe it is time that you tell us the full story, and not only a piece in middle.

If you only want to rebuild the colunmstore index there is always ALTER INDEX REBUILD. Unless this is different rebuilding rowstore indxes, this will also require extra space, but since the compression does not change, you don't need as much space than if you aim for a heap or a clustered index.

Whether going from columnstore to clustered index and back to columnstore is faster than going to heap and back I have no idea. But since the clustered index requires sorting and the heap does not, I would place my bets on the heap.

Share via

Cannot drop a Columnstore Index

1 additional answer

Your answer