ALTER TABLE DROP INDEX failed on a table that isn't memory optimized

Question

I'm trying to drop an index created on a table, but I get this error -

The operation 'ALTER TABLE DROP INDEX' is supported only with memory optimized tables.

I need to remove this index in order to drop a field in my table. Is there any way of doing this without duplicating the table, and migrating all the data across?

Twink

Answer 1

For regular tables you should use DROP INDEX syntax:

DROP INDEX index_name ON tab_name;

ALTER TABLE

The syntax ALTER TABLE ... ADD/DROP/ALTER INDEX is supported only for memory-optimized tables.

Riva

Answer 2

To Drop an Index

DROP INDEX index_name ON table_name

To Add an Index

CREATE INDEX index_name ON table_name(column1, column2, ...);

Fisherman

Answer 3

Drop index on memory optimized table can be done only using alter table statement

Alter table table name DROP INDEX index name

or non memory optimized tables

DROP INDEX index name ON table name

Memory optimized tables are being supported from sql server 2016.

Beguin

Answer 4

Look here: if it is NOT a memory optimized table then just use the "drop index" statement.

Trubow

Answer 5

You need use Drop Index <IndexName> On <TableName>

Ferdy

Hi Sanjoy, thanks for your answer. Can you update your question to explain why this solves the asker's issue? A good start is linking to the docs and quoting an important bit of context. – Brigidbrigida

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Terr

@Terr How is this a comment. It is definitely answer. It might not be a very good answer, but it is an answer nonetheless. – Grindle

You are most definitely correct. I was a bit hasty on my review queue, and this looked mostly like something one should be putting in the comment. – Terr