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?

score 21 · Answer 1 · answered May 01 '19 at 09:59

21

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.

answered May 01 '19 at 09:59

Lukasz Szozda

162,964
23
234
275

score 6 · Answer 2 · edited Aug 06 '19 at 07:51

6

To Drop an Index

DROP INDEX index_name ON table_name

To Add an Index

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

edited Aug 06 '19 at 07:51

Suraj Rao

29,388
11
94
103

answered Aug 06 '19 at 07:39

Suraj Tiwari

91
1
4

score 4 · Answer 3 · answered Oct 28 '20 at 15:52

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.

score 1 · Answer 4 · answered May 01 '19 at 10:02

1

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

answered May 01 '19 at 10:02

wosi

373
4
15

score -1 · Answer 5 · answered Feb 15 '21 at 16:56

-1

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

answered Feb 15 '21 at 16:56

Sanjoy

1

2

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. – Connor Low Feb 15 '21 at 17:11
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/28329818) – Oliver Feb 15 '21 at 18:01
@Oliver How is this a comment. It is definitely answer. It might not be a very good answer, but it is an answer nonetheless. – Mark Rotteveel Feb 15 '21 at 19:12
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. – Oliver Feb 15 '21 at 19:55

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

5 Answers5