Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,018,066 hits
  • Select GETDATE()

    December 2013
    M T W T F S S

A-Z of In-Memory OLTP : TSQL Constructs for In-Memory

Posted by blakhani on December 5, 2013

To build a mega-structure, we need strong foundation. To start such tasks, we need to have knowledge of right tools available. Similarly, in SQL world, you would not be using SQL Server Management Studio UI all the time to do small tasks. All deployments are done by scripts. Once I had chat with my friend Vinod (b|t) and I asked him – Why do you think T-SQL is important. As always, he thought, smiled and said “For a rail to get from one location to another one needs to have a strong rail. These rails not only link the two destination end points but guide the trail with all its carts to this destination. In a similar way, to get started with any programming inside SQL Server, one needs to know TSQL code.”

In-Memory OLTP has a number of changes/improvements that get introduced with TSQL. This blog post of “A-Z of In-Memory OLTP” series, will bring some of these changes that you need to know when working with In-memory OLTP. What would happen under the hood, when these T-SQL codes are executed, would be explained in later posts of this series. There are T-SQL extensions added in existing CREATE DATABASE, CREATE TABLE and CREATE PROCEDURE commands. Let’s begin…

Create In-Memory OLTP database

CREATE DATABASE command has new extension called MEMORY_OPTIMIZED_DATA. It is also available in ALTER DATABASE as well. Below is the quick sample:

    NAME = [SQLSeverHelp_IMO_data]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
    NAME = [SQLSeverHelp_IMO_dir]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO_dir'
    ) LOG ON (
    NAME = [SQLSeverHelp_IMO_log]
    ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'

I have highlighted the syntax used to create storage for In-Memory Optimized objects, a filegroup which is container for MEMORY_OPTIMIZED_DATA. Once we run this command, SQL Server is going to create folder given in FILENAME parameter.  Let’s say we already have a “regular” database and we want to add a new filegroup, here is the syntax.

    NAME = [SQLSeverHelp_IMO_data]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
    ) LOG ON (
    NAME = [SQLSeverHelp_IMO_log]
    ,Filename = 'C:\IMO_Database\SQLSeverHelp_IMO_log.ldf'

-- Adding MEMORY_OPTIMIZED_DATA filegroup 
USE [master]


    NAME = N'SQLSeverHelp_IMO_dir'
    ,FILENAME = N'C:\IMO_Database\SQLSeverHelp_IMO_dir'

Behind the scene, SQL Server uses FIleStream technology. Note that only one filegroup is allowed. Here is the error in case we try to add more filegroup contains MEMORY_OPTIMIZED_DATA

Msg 10797, Level 15, State 2, Line 3
Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database.

Above does not mean that we can’t have multiple files/folders. That’s just a limitation to number of filegroups having MEMORY_OPTIMIZED_DATA. If we want to add more files to the filegroup, we can do that as below. (I am adding new folder SQLSeverHelp_IMO_dir_1)

    NAME = N'SQLSeverHelp_IMO_dir_1'
    ,FILENAME = N'C:\IMO_Database\SQLSeverHelp_IMO_dir_1'

Create In-Memory Table

Once we have created database, next step is to create objects in the database. As I mentioned in first post this is a “hybrid” database which can contain In-Memory table(s) as well as disk-based tables. Let me go ahead and create a regular table and in-memory table.

-- Normal table, Old Syntax 
CREATE TABLE DiskBasedTable (
    ,vFName VARCHAR(20) NOT NULL
    ,vLName VARCHAR(20) NOT NULL

-- In-Memory table, New Syntax highlighted 
CREATE TABLE MyFirstMemporyOptimizedTable (
    ,vFName VARCHAR(20) NOT NULL
    ,vLName VARCHAR(20) NOT NULL
    WITH (

There are three important extension in earlier syntax of CREATE TABLE

  1. MEMORY_OPTIMIZED: This is to tell SQL Server that, we need to put this table into memory and optimization should be ON. In case we try to create the table without filegroup then we would be welcomed with below message

    Msg 41337, Level 16, State 0, Line 12
    The MEMORY_OPTIMIZED_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for a database until it has one MEMORY_OPTIMIZED_DATA filegroup that is not empty.

  2. DURABILITY: This indicates whether we want data in the table to be persisted when database is restarted (via offline/online, SQL Service restart or SQL crash). If we just need schema then SCHEMA_ONLY can be specified. Default option is SCHEMA_AND_DATA which means data would not be lost during database restart.
  3. Inline Index Creation: Since In-Memory tables doesn’t allow creating or dropping the new index separately, we have option to define the index during table creation itself. They are two kind of Index supported – Hash and Range Index. We would be covering more about those in future post. It’s worth noting that we can’t create clustered index on In-Memory tables.If we attempt any ALTER TABLE on such tables, here is the error message.

Msg 10794, Level 16, State 15, Line 11
The operation ‘ALTER TABLE’ is not supported with memory optimized tables.

Create Natively Compiled Stored Procedure

Don’t get scared of new term – natively compiled. This is essentially a stored procedure which can also be used to access in-memory table other than regular ad-hoc queries. Here is the basic syntax of creating natively compiled stored procedure. What happens behind the scene? We would uncover that in one of the later blog.

 CREATE PROCEDURE [dbo].[InsertName] @iID INT
    ,@vFName VARCHAR(20)
    ,@vLName VARCHAR(20)
    WITH (

    INSERT INTO dbo.MyFirstMemporyOptimizedTable
    VALUES (@iID,@vFName,@vLName)

I have highlighted the new terms which you are seeing as compared to regular stored procedure also known as Interpreted T-SQL. It is important to note that with all those option specified, query plan is generated during creation time itself. Permission check are also performed during creation time and that’s one of the reason they are faster. It is important to note that we can’t access disk-based table in a natively compiled stored procedure. Here is the error when I changed MyFirstMemporyOptimizedTable to DiskBasedTable.

Msg 10775, Level 16, State 1, Procedure InsertName, Line 13
Object ‘DiskBasedTable’ is not a memory optimized table and cannot be accessed from a natively compiled stored procedure.

Let me quickly comment upon the highlighted pieces for clarity.

  • NATIVE_COMPILATION:  This tells SQL Engine to compile this store procedure during creation time and indicates that we want the procedure optimized for in-memory tables access.
  • SCHEMABINDING: This is not a new parameter. It tells SQL Server to disallow the drop of tables referred inside the procedure. If I try to drop table MyFirstMemporyOptimizedTable, here is the error message. Alter is anyways not permitted on

Msg 3729, Level 16, State 1, Line 24
Cannot DROP TABLE ‘MyFirstMemporyOptimizedTable’ because it is being referenced by object ‘InsertName’.

If we don’t specify SCHEMABINDING option, here is the error

Msg 10796, Level 15, State 1, Procedure InsertName, Line 16
The SCHEMABINDING option is supported only for natively compiled stored procedures, and is required for those stored procedures.

  • EXECUTE AS OWNER: This is also a old option which specifies the context under which the procedure should be executed. Here is the error message if I don’t specify that

Msg 41320, Level 16, State 1, Procedure InsertName, Line 6
EXECUTE AS clause is required, and EXECUTE AS CALLER is not supported, with natively compiled stored procedures.

  • BEGIN ATOMIC: If we don’t specify, here is the error message. This means that all operations in the procedure succeed or fail as a unit.

Msg 10783, Level 15, State 1, Procedure InsertName, Line 16
The body of a natively compiled stored procedure must be an ATOMIC block.


Msg 10784, Level 15, State 1, Procedure InsertName, Line 9
The WITH clause of BEGIN ATOMIC statement must specify a value for the option ‘transaction isolation level’.

Msg 10784, Level 15, State 1, Procedure InsertName, Line 9
The WITH clause of BEGIN ATOMIC statement must specify a value for the option ‘language’.

What I have demonstrated as sample in create procedure block is all “minimal” options needed to create a natively complied stored procedure. There are certain limitation of natively compiled stored procedure which are documented here. I truly hope that to go along with me you have already downloaded CTP2 and installed it.

Stay Tuned for next part of the series.

Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOnPaperback, Kindle

9 Responses to “A-Z of In-Memory OLTP : TSQL Constructs for In-Memory”

  1. […] other programming paradigm – Microsoft makes it easy for developers and DBA alike. In my previous post, you would have read about new T-SQL constructs added to support In-Memory OLTP objects. This blog […]

  2. […] logical step is to create a In-Memory table in the database. In our last post, we have created table called MyFirstMemporyOptimizedTable. As soon as table is created, SQL Server […]

  3. […] earlier “T-SQL Constructs” post of this series, we have seen syntax of creation of natively compiled stored procedure. […]

  4. […] Here is the script which can be used. This is the same script which you might have seen earlier in this […]

  5. […] my previous blog about T-SQL constructs, we have discussed that index on in-memory need to be created during table […]

  6. […] tables in SQL Server in-memory OLTP. By default, tables are durable. We have briefly discussed here in earlier blog of this series. This is defined by DURABILITY parameter during created table […]

  7. […] this a database with no in-memory optimized filegroup then we would get option to create them (Read here). The next option is a checkbox that says “Also copy table data to the new memory optimize […]

  8. sateesh said

    Thanks for the series

  9. Sunil pal said

    I want to move data from my disk table to Memory table. My disk table is in different database (e.g DB1 which is not In memory Database) & my Memory table is in Different database (e.g Db2 which is a Memory Database). I have lakhs of record which need to be moved from my disk table to the In Memory table.

    Please help, Its urgent.
    Thanks in advance

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: