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?

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.

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

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.

Answer 4

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