How to drop an in-memory temporal columnstore

Consider the following:

CREATE DATABASE [Foo]
ALTER DATABASE [Foo] ADD FILEGROUP XTP CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE [Foo] ADD FILE (NAME=XTP,FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Bar_XTP') TO FILEGROUP XTP 
GO
USE [Foo]
CREATE TABLE dbo.A(
    ID INT NOT NULL CONSTRAINT PK_A_ID PRIMARY KEY NONCLUSTERED, 
    [Start] DATETIME2 GENERATED ALWAYS AS ROW START,
    [End] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME([Start], [End]),
    INDEX IX_A_CCS CLUSTERED COLUMNSTORE
) WITH (
    MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA,  
    SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.A_History)
)

This wonderful contraption combines all the new features of SQL Server: an in-memory columnstore that's also a temporal table. Admittedly, the use cases for this should be limited: an in-memory columnstore typically supports real-time analytics and would not be expected to require versioning. Even so, if it's possible, someone will no doubt find some use for it.

There's just one slight thing I've got a problem with: I don't know how to get rid of it. A simple DROP TABLE produces:


Msg 13552, Level 16, State 1, Line 27

Drop table operation failed on table 'Foo.dbo.A' because it is not a supported operation on
system-versioned temporal tables.

This is to be expected; you get the same error with disk-based tables. You're supposed to turn off system versioning first. Unfortunately ALTER TABLE A SET (SYSTEM_VERSIONING = OFF) produces:


Msg 10794, Level 16, State 13, Line 1

The operation 'ALTER TABLE' is not supported with memory optimized tables that have a column store index.

And that's also to be expected, but it seems to leave me out of options. Deleting the table from Management Studio produces the same sequence of commands under the covers, and so that also fails.

I've searched around but found neither a solution, nor someone who's attempted the same thing and confirmed

It seems you have to drop the columnstore index first. This works for me:

ALTER TABLE A DROP INDEX IX_A_CCS;
ALTER TABLE A SET (SYSTEM_VERSIONING = OFF);
DROP TABLE A;

Note that this leaves behind the temporal history table, so you might want to run DROP TABLE A_History; for good measure (which is the default naming when a history table name is not specified).

StackExchange Database Administrators Q#216316, answer score: 6