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