DML for memory-optimized tables in partitioned views

As part of a conversation on the #sqlhelp, I was giving feedback to a member of the Microsoft Tiger team about how In-Memory OLTP might be improved. One of my responses was about implementing a hot/cold data solution, and  Adam Machanic (b|t) responded, suggesting that partitioned views might work. I had already tested partitioned views and memory-optimized tables for queries, and confirmed that it worked as expected, but I had never tested DML for this combination of features.

Based on my new testing, I can state with confidence that partitioned view support for DML with memory-optimized tables will properly handle INSERT and DELETE operations, but some UPDATE operations might fail, and that failure occurs in a most ungraceful way. After verifying that this is an issue on SQL 2016 SP1/CU3 and SQL 2017 CTP2.1, I filed this Connect item. Microsoft has confirmed this issue, but has not yet determined how it will be fixed. For example, they could decide to disallow all UPDATEs when a memory-optimized table  belongs to a partitioned view, or instead decide to support it under limited circumstances. I’ll update this post when I have more detail from Microsoft.

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

The failure looks like this:

PartitionedViewError

Repro script:

4 thoughts on “DML for memory-optimized tables in partitioned views

  1. Pingback: Partitioned Views With Memory-Optimized Tables – Curated SQL

  2. Ned Otter Post author

    Apologies for the delay in replying – I just retested this in SQL 2017 CU5, and it still fails. Have not heard about any updates to the Connect item that I opened.

    Reply
  3. Ned Otter Post author

    Apparently this has been “fixed” in SQL 2019. When I retested in CTP2.0, I received the following error:

    ################################
    Msg 4458, Level 16, State 14, Line 166
    The partitioned view “TEST.dbo.vwPartitioned” is not updatable because one or more of the partioned tables is a Hekaton table and the partitioned column is being updated.
    ################################

    If a memory-optimized table participates in a partitioned view, it can be updated, except for the partitioning column. Microsoft has basically chosen to disallow row movement between memory-optimized and on-disk tables.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *