Posted in Development

Limitation of In-Memory OLTP in SQL Server 2016 – Sp_rename not allowed

Hi friends, one more limitation of In-Memory OLTP is SQL Server 2016 is that sp_rename is not allowed to change the name of in-memory OLTP objects.

Sp_rename is a system stored procedure which allows to change the name of the user-defined objects like table name, column name, datatype of a column etc. in the current database.

Let’s see an example of this –

There is already an In-memory table created with name ‘Test_OLTP’ and now I like to rename it with a new name ‘MemOLTP’.

I have executed the below query to rename the table name.

sp_rename ‘dbo.Test_OLTP’,’MemOLTP’

When I executed the query it comes up with an error stating –

‘Operations that require a change to the schema version,

for example renaming, are not supported with memory optimized tables.’

Lim_Sp_rename

That’s all for the day folks 🙂

 

Advertisements
Posted in Development

Getting started with In Memory OLTP in SQL Server 2016 – Part 1

In Memory OLTP (Hekaton) introduced in SQL Server 2014 is a new database engine component which is optimized for OLTP workloads to achieve the performance by storing data in memory and accessing data from memory. Memory optimized tables can be accessed using Transact –SQL. When memory optimized tables are accessed all data is stored into the memory.

Today, I will tell you how to create a memory optimized tables.

Let’s first create a database to stored memory optimized tables. Database that contain memory optimized tables must have MEMORY_OPTIMIZED_DATA filegroup. Syntax to create filegroup is same as creating regular filegroup only difference is that here we need to specify option CONTAINS MEMORY_OPTIMIZED_DATA.

–Create a database

IF EXISTS (SELECT * FROM sys.databases WHERE name=’InMemOLTP’)

DROP DATABASE InMemOLTP;

GO

CREATE DATABASE InMemOLTP

ON

PRIMARY(NAME = [InMemOLTP_data],

FILENAME = ‘I:\453491\InMemOLTP\Data\HKDB_data.mdf’, size=500MB),

FILEGROUP [InMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA

(NAME = [InMemOLTP_mod_dir],

FILENAME = ‘I:\453491\InMemOLTP\InMemOLTP_mod_dir’)

LOG ON (name = [InMemOLTP_log],

Filename=’I:\453491\InMemOLTP\Log\InMemOLTP_log.ldf’, size=500MB)

COLLATE Latin1_General_100_BIN2;

Here, I have created a filegroup ‘InMemOLTP_fg’ with filecontainer ‘InMemOLTP_mod_dir’. I have also specifies BIN2 collation as currently indexes on character column can only be defined on columns that uses BIN2 collation.

Below is the script to create a memory optimized table with name ‘Test_OLTP’.

CREATE TABLE Test_OLTP

(

       Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),

       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)

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

Syntax to create memory optimized table is similar to the Transact SQL tables but to specify a memory optimized table we need to define clause MEMORY_OPTIMIZED = ON. Memory optimized table can be defined using two durability – SCHEMA_AND_DATA and SCHEMA_ONLY.

With SCHEMA_ONLY durability, memory optimized table will store only table structure and SQL server will not store log changes to the disk. With SCHEMA_AND_DATA, memory optimized table store table data to the checkpoint files on disk and table structure too.

Nonclustered Hash index is created on Name column as clustered indexes are not allowed on memory optimized tables. If you don’t specify Nonclustered with Primary Key column you will get error –

Msg 12317, Level 16, State 78, Line 21

Clustered indexes, which are the default for primary keys, are not supported with hash indexes. Specify a NONCLUSTERED index instead.

When we create a HASH index we need to specify the number of buckets in the hash index. More on Bucket_count I will discuss later in further posts.

Now, the table has been created, let’s try to insert some data into memory optimized table.

INSERT INTO Test_OLTP (Name, Place, CompanyName, Skill)

VALUES (‘Kapil’, ‘Gurgaon’,’CTS’,’SQL Server’),

              (‘Aditya’,’Pune’,’ABC’,’Weblogic’)

Just check the data that you has inserted by running below query.

SELECT * FROM Test_OLTP

OLPT1

So friends, in this way we can create memory optimized database and table. We will discuss further on memory optimized tables in my next blogs.

Have a happy learning 🙂

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.

 

 

 

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.