Partitioned, view, In-Memory, SQL Server

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:

Repro script:

/*
	Repro for UPDATEs on a partitioned view
*/

SET NOCOUNT ON 

DROP VIEW IF EXISTS dbo.vwPartitioned;
GO
DROP TABLE IF EXISTS dbo.Table1;
GO
DROP TABLE IF EXISTS dbo.Table2;
GO

CREATE TABLE dbo.Table1
(
    TxDate DATE
   ,OrderID INT 
   CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)
)
--WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);
GO
CREATE TABLE dbo.Table2
(
    TxDate DATE
   ,OrderID INT
   CONSTRAINT PK_Table2 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)
)
WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);
GO

INSERT dbo.Table1
(
    TxDate
   ,OrderID
)
VALUES
 ('2014-01-01', 1)
,('2014-01-10', 2)
,('2014-03-15', 3)
,('2014-07-20', 4);

INSERT dbo.Table2
(
    TxDate
   ,OrderID
)
VALUES
 ('2015-01-07', 5)
,('2015-02-10', 6)
,('2015-03-18', 7)
,('2015-10-28', 8);
GO

CREATE VIEW dbo.vwPartitioned
WITH SCHEMABINDING AS
(
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table1
	UNION ALL
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table2
);
GO
SET STATISTICS IO ON;
GO

ALTER TABLE dbo.Table2 
ADD CONSTRAINT CC_Table2_TxDate CHECK (TxDate &gt;= '2015-01-01');
GO
ALTER TABLE dbo.Table1 
ADD CONSTRAINT CC_Table1_TxDate CHECK (TxDate &lt; '2015-01-01');
GO

ALTER VIEW dbo.vwPartitioned
WITH SCHEMABINDING AS
(
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table1
	UNION ALL
	SELECT
	    TxDate
	   ,OrderID
	FROM dbo.Table2
);
GO

INSERT dbo.vwPartitioned
(
    TxDate
   ,OrderID
)
VALUES
 ('2014-01-13', 91)
,('2015-01-08', 85);
GO
UPDATE dbo.vwPartitioned 
SET OrderID = 2
WHERE TxDate = '2014-01-13';
GO
DELETE dbo.vwPartitioned
WHERE TxDate = '2014-01-13';
GO
-- check location of rows
SELECT *
FROM dbo.Table2
ORDER BY OrderID
GO
SELECT *
FROM dbo.Table1
ORDER BY OrderID
GO

-- This UPDATE succeeds, because it does NOT attempt to "move" the row to another table
UPDATE dbo.vwPartitioned
SET OrderID = 9999
WHERE TxDate = '2015-01-07';
GO

-- This UPDATE fails with a stack dump, due to row "movement"
UPDATE dbo.vwPartitioned
SET TxDate = '2015-01-01'
WHERE TxDate = '2014-01-01';
GO

-- This UPDATE fails with a stack dump, due to row "movement"
UPDATE dbo.vwPartitioned
SET TxDate = '2014-01-01'
WHERE OrderID = 9999;
GO

-- check location of rows
SELECT *
FROM dbo.Table2
ORDER BY OrderID
GO
SELECT *
FROM dbo.Table1
ORDER BY OrderID

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

Repro for UPDATEs on a partitioned view

SET NOCOUNT ON

DROP VIEW IF EXISTS dbo.vwPartitioned;

DROP TABLE IF EXISTS dbo.Table1;

DROP TABLE IF EXISTS dbo.Table2;

CREATE TABLE dbo.Table1

(

TxDate DATE

,OrderID INT

CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)

)

--WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);

CREATE TABLE dbo.Table2

(

TxDate DATE

,OrderID INT

CONSTRAINT PK_Table2 PRIMARY KEY NONCLUSTERED (TxDate, OrderID)

)

WITH (DURABILITY = SCHEMA_AND_DATA, MEMORY_OPTIMIZED = ON);

INSERT dbo.Table1

(

TxDate

,OrderID

)

VALUES

('2014-01-01', 1)

,('2014-01-10', 2)

,('2014-03-15', 3)

,('2014-07-20', 4);

INSERT dbo.Table2

(

TxDate

,OrderID

)

VALUES

('2015-01-07', 5)

,('2015-02-10', 6)

,('2015-03-18', 7)

,('2015-10-28', 8);

CREATE VIEW dbo.vwPartitioned

WITH SCHEMABINDING AS

(

SELECT

TxDate

,OrderID

FROM dbo.Table1

UNION ALL

SELECT

TxDate

,OrderID

FROM dbo.Table2

);

SET STATISTICS IO ON;

ALTER TABLE dbo.Table2

ADD CONSTRAINT CC_Table2_TxDate CHECK (TxDate >= '2015-01-01');

ALTER TABLE dbo.Table1

ADD CONSTRAINT CC_Table1_TxDate CHECK (TxDate < '2015-01-01');

ALTER VIEW dbo.vwPartitioned

WITH SCHEMABINDING AS

(

SELECT

TxDate

,OrderID

FROM dbo.Table1

UNION ALL

SELECT

TxDate

,OrderID

FROM dbo.Table2

);

INSERT dbo.vwPartitioned

(

TxDate

,OrderID

)

VALUES

('2014-01-13', 91)

,('2015-01-08', 85);

UPDATE dbo.vwPartitioned

SET OrderID = 2

WHERE TxDate = '2014-01-13';

DELETE dbo.vwPartitioned

WHERE TxDate = '2014-01-13';

-- check location of rows

SELECT *

FROM dbo.Table2

ORDER BY OrderID

SELECT *

FROM dbo.Table1

ORDER BY OrderID

-- This UPDATE succeeds, because it does NOT attempt to "move" the row to another table

UPDATE dbo.vwPartitioned

SET OrderID = 9999

WHERE TxDate = '2015-01-07';

-- This UPDATE fails with a stack dump, due to row "movement"

UPDATE dbo.vwPartitioned

SET TxDate = '2015-01-01'

WHERE TxDate = '2014-01-01';

-- This UPDATE fails with a stack dump, due to row "movement"

UPDATE dbo.vwPartitioned

SET TxDate = '2014-01-01'

WHERE OrderID = 9999;

-- check location of rows

SELECT *

FROM dbo.Table2

ORDER BY OrderID

SELECT *

FROM dbo.Table1

ORDER BY OrderID

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

Pingback: Partitioned Views With Memory-Optimized Tables – Curated SQL
Pittsburgh DBA April 1, 2018 at 3:33 am

I was just hit by this. Do you know of any updates to the case? Thanks!

Reply ↓
Ned Otter Post authorApril 7, 2018 at 3:19 am

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 ↓
Ned Otter Post authorSeptember 25, 2018 at 12:07 am

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 ↓

Ned Otter Blog

SQL Server DBA and Musician

DML for memory-optimized tables in partitioned views

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

Leave a Reply Cancel reply