How to drop an in-memory temporal columnstore

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

up vote
4
down vote

favorite

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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLDATABar_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.)

asked Aug 30 at 13:54

Jeroen Mostert

1336

add a commentÂ |Â

up vote
4
down vote

favorite

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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLDATABar_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)
)

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.

(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.)

asked Aug 30 at 13:54

Jeroen Mostert

1336

add a commentÂ |Â

up vote
4
down vote

favorite

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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLDATABar_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)
)

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.

(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.)

asked Aug 30 at 13:54

Jeroen Mostert

1336

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 FilesMicrosoft SQL ServerMSSQL14.MSSQLSERVERMSSQLDATABar_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)
)

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.

(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.)

asked Aug 30 at 13:54

Jeroen Mostert

1336

asked Aug 30 at 13:54

Jeroen Mostert

1336

asked Aug 30 at 13:54

Jeroen Mostert

1336

asked Aug 30 at 13:54

Jeroen Mostert

1336

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

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).

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f216316%2fhow-to-drop-an-in-memory-temporal-columnstore%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
5
down vote

accepted

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).

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

add a commentÂ |Â

up vote
5
down vote

accepted

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).

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

add a commentÂ |Â

up vote
5
down vote

accepted

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).

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

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).

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

edited Aug 30 at 14:10

answered Aug 30 at 14:07

jadarnel27

1,6111125

answered Aug 30 at 14:07

jadarnel27

1,6111125

answered Aug 30 at 14:07

jadarnel27

1,6111125

add a commentÂ |Â

draft saved

draft discarded

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky