How do you change the DURABILITY option on an existing memory-optimized table in SQL Server 2016?

Question

I want to change the DURABILITY of a memory-optimized table in SQL Server 2016 from SCHEMA_AND_DATA to SCHEMA_ONLY.

The Microsoft documentation suggests that the following ALTER TABLE statement should work:

ALTER TABLE mem_opt_table
DURABILITY = SCHEMA_ONLY

But it gives the following error:

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'DURABILITY'.

What is the correct syntax for changing the durability setting on a table? Are there any additional steps that I am missing?

Answer 1

The documentation linked in the original question is faulty as was suggested by many of the comments. That is confirmed in the github issue response here: https://github.com/MicrosoftDocs/sql-docs/issues/3523#issuecomment-554511264.

Therefore, the only way to do this is to drop the table and re-create it with the desired DURABILITY setting.