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 as a problem yet to be fixed. Workarounds readily suggest themselves (like renaming the table to something obscure) but is there a way to properly drop it?
(This was tested with the most recent non-Azure version of SQL Server as of writing, which is 14.0.3037.1, 2017 RTM CU10.)