Partitioned Views With Memory-Optimized Tables

Ned Otter ran into an issue building partitioned views from a combination of disk-based and memory-optimized tables:

Let’s assume that we have two tables that belong to a partitioned view. Both tables can be memory-optimized, or one table can be memory-optimized, and the other on-disk.

Success condition

an UPDATE occurs to a row in a table, and the UPDATE does not change where the row would reside, i.e. does not cause it to “move” to another table, based on defined CONSTRAINTS

Failure conditions:

   a. UPDATE occurs to a row in the memory-optimized table that causes it to move to either another memory-optimized table, or a on-disk table

   b. UPDATE occurs to a row in the on-disk table that causes it to move to the memory-optimized table

Read the whole thing.  The good news is that if you’re splitting in a way that doesn’t require updating from one type of table to the other, partitioned views work fine.

Related Posts

Memory-Optimized Table Maintenance

Ned Otter has a great post looking at what you can and cannot do with memory-optimized tables containing certain types of indexes: Now, let’s attempt to create a NONCLUSTERED COLUMNSTORE INDEX: ALTER TABLE dbo.InMemADD ADD INDEX NCCI_InMem NONCLUSTERED COLUMNSTORE (col1); Msg 10794, Level 16, State 76, Line 76 The feature ‘NONCLUSTERED COLUMNSTORE’ is not supported […]

Read More

Bugs With Backup Compression And TDE

Parikshit Savjani provides recommendations on combining backup compression with Transparent Data Encryption: In past months, we discovered some edge scenarios related to backup compression for TDE databases causing backups or restores to fail, hence our recommendations have been Avoid using striped backups with TDE and backup compression. If your database has virtual log files (VLFs) […]

Read More


July 2017
« Jun Aug »