SQL Server 2016: Memory Optimized Tables Made Easier

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.

InfoQ Weekly Newsletter

Join a community of over 250 K senior developers by signing up for our newsletter. If you are based in the EEA, please contact us so we can provide you with the protections afforded to you under EEA protection laws.

Is your profile up-to-date? Please take a moment to review and update.

Email Address

Note: If updating/changing your email, a validation request will be sent

Company name:

Keep current company name

Update Company name to:

Company role:

Keep current company role

Update company role to:

Company size:

Keep current company Size

Update company size to:

Country/Zone:

Keep current country/zone

Update country/zone to:

State/Province/Region:

Keep current state/province/region

Update state/province/region to:

Subscribe to our newsletter?

Subscribe to our architect newsletter?

Subscribe to our industry email notices?

By subscribing to this email, we may send you content based on your previous topic interests. See our privacy notice for details.

You will be sent an email to validate the new email address. This pop-up will close itself in a few moments.

We notice you're using an ad blocker

We understand why you use ad blockers. However to keep InfoQ free we need your support. InfoQ will not provide your data to third parties without individual opt-in consent. We only work with advertisers relevant to our readers. Please consider whitelisting us.