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

SQL Server and Recent Security Patches

Allan Hirt takes us through recent security updates and how they pertain to SQL Server: After Spectre and Meltdown a few months back (which I cover in this blog post from January 4), another round of processor issues has hit the chipmaker. This one is for MDS (also known as a ZombieLoad) This one comprises […]

Read More

String or Binary Data and Associated Bug

Brent Ozar looks at the “String or binary data would be truncated” improvement: Don’t leave this trace flag enabled.There’s at least one bug with it as of today on SQL Server 2017 CU13: table variables will throw errors saying their contents are being truncated even when no data is going into them. Andrew Pruski reports: […]

Read More

Categories

July 2017
MTWTFSS
« Jun Aug »
 12
3456789
10111213141516
17181920212223
24252627282930
31