Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Merge not supported with memory tables as target

Hi friends, in this post we will discussed another limitation of In-Memory OLTP that in-memory tables as a Target in Merge statement is not supported.

Let’s first create two table: In-memory and standard table.

CREATE TABLE S_Merge

(

[Name] varchar(32) not null PRIMARY KEY ,

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

)

CREATE TABLE T_Merge

(

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) null,

[State_Province] varchar(32) null,

[LastModified] datetime not null,

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

GO

Now, I will insert some dummy data in S_Merge table –

INSERT INTO S_Merge (Name, City, State_Province, LastModified)

SELECT ‘A1’, ‘Jaipur’, ‘Rajasthan’, Getdate()

UNION ALL

SELECT ‘A2’, ‘GGN’,’Haryana’, Getdate()

Lim4.jpg

So, now we are ready to execute merge statement scenario.

MERGE T_Merge AS T

USING S_Merge AS S ON (T.Name = S.Name)

WHEN NOT MATCHED THEN

INSERT (Name, City, State_Province, LastModified)

VALUES (S.Name, S.City, S.State_Province, S.LastModified);

Here in this script I used my standard table S_Merge as Source and T_Merge (in-Memory table) as my target table. But when I execute this script it comes out with an error –

The operation ‘MERGE’ is not supported with memory optimized tables.’

Lim4_1

Now when I used it vice versa i.e. using In-memory table as Source and standard table as target using below script –

MERGE S_Merge AS T

USING T_Merge AS S ON (T.Name = S.Name)

WHEN NOT MATCHED THEN

INSERT (Name, City, State_Province, LastModified)

VALUES (S.Name, S.City, S.State_Province, S.LastModified);

It will get executed successfully without any error.

So friends, we came to know one more limitation of In-memory OLTP tables that we cannot use In-memory table as target in MERGE statement.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s