Memory Optimized Tables promise significant performance gains, but tend to be difficult to work with. Much of this difficulty comes from the fact that the table schema and indexes for a Memory Optimized Table couldn’t be altered.
The workaround was to create a temp table, copy the data across, drop the original Memory Optimized Table, and then create and load the new one. That is no longer necessary for the following operations:
- Changing the bucket count. A bucket count that is too high wastes memory while one that is too low hurts performance.
- Adding and removing an index. Note that you cannot create or remove an index outside of an ALTER Table command.
- Changing, adding, and removing a column.
- Adding and removing a constraint.
Memory Optimized Tables are usually subject to schema binding from Natively Compiled Stored Procedures. You can use the Schema-bound Dependency chart to see if a given ALTER statement is allowed.
Note that ALTER TABLE has some additional limitations involving workloads:
Before starting the ALTER TABLE operation, the workload needs to be halted. Any user transaction that started before the ALTER TABLE started, and that accesses the table, can cause the ALTER TABLE to fail with a serializable validation failure (error code 41325).
Natively Compiled Stored Procedures
In 2016, Natively Compiled Stored Procedures can also be altered. While the new version of the stored procedure is being compiled, the original version will continue to be used. Once compilation is complete, and the pending requests executed, the database will switch over to the new version.
As with tables, this previously required the stored procedure to be dropped before the new one was created. During the compilation window, this could result in failed executions.
Note that you cannot use this feature to convert between normal and natively compiled stored procedures.