Posted in 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
Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Seed and Increment value other than 1 not supported

Hi friends, in this blog we will discussed another limitation of In-Memory OLTP that seed and increment value other than 1 while defining identity property to a column is not supported.

Let’s quickly see a demo on this –

First we will create a table defining identity property with seed 1 and incremental value 1.

CREATE TABLE T4

(

ID INT IDENTITY(1,1),

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

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

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

When I execute this query, table will created successfully.

Now, let’s change the identity value (1,1) to (1,4) and re run the query.
We will drop the table T4 and re execute the query modifying identity property.

DROP TABLE T4

Recreate the table again –

CREATE TABLE T4

(

ID INT IDENTITY(1,4),

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

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

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

Oops we got an error ‘The use of seed and increment values other than 1 is not supported with memory optimized tables.’

Lim3

So, finally we got to know one more limitation of In-memory OLTP tables that no seed and increment value other than 1 is supported with it.

That’s all for the day folks.

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – TRUNCATE table not supported

Hi friends, another limitation that we will discussed is TRUNCATE table command is not supported with memory optimized tables.

Let’s see a demo on this.

First we will create a memory optimized table.

CREATE TABLE T2

(

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

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) not null,

[State_Province] varchar(32) not null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])

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

 

Once the table gets created, we will try to add one more column with LOB datatype.

TRUNCATE TABLE T2

But as we run this statement it comes up with an error –

‘The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.’

Lim2

So, we got to know limitation of In-memory OLTP is that TRUNCATE table command is not supported in In-memory table. Might be this limitation also gets over with future CTP versions of SQL Server 2016.

Have a happy learning.

Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – LOB datatypes not supported

Hi friends, Wish you all a very Happy New Year. This is my first blog of 2016 and starting it with SQL 2016 J . Now a days I am exploring In-Memory features and changes done in SQL 2016 CTP versions. I have installed CTP 3.0 currently and comparing changes done in SQL 2016 from SQL 2014 In-memory features.

For next few blogs I will try to cover the limitation of In-Memory OLTP feature.

First limitation that we will discussed is support of LOB datatypes. LOB datatypes like varchar(max), nvarchar(max), XML etc. are not supported in memory optimized tables.

Let’s see a demo on this.

First we will create a memory optimized table.

CREATE TABLE T2

(

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

WITH (BUCKET_COUNT = 100000),

[City] varchar(32) not null,

[State_Province] varchar(32) not null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])

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

 

Once the table gets created, we will try to add one more column with LOB datatype.

ALTER TABLE T2

ADD Description nvarchar(max) NULL

Alter table T2

ADD Description XML

But the column does not gets added and comes up with an error –

‘The type ‘nvarchar(max)’ is not supported with memory optimized tables.’

Lim1

So, we got to know limitation of In-memory OLTP is that LOB datatypes are not supported in In-memory table. Might be this limitation gets over with future CTP versions of SQL Server 2016.

Have a happy learning.