Help: SQL Server

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:

CREATE DATABASE SQLSeverHelp_IMO ON PRIMARY (
    NAME = [SQLSeverHelp_IMO_data]
    ,FILENAME = 'C:\IMO_Database\SQLSeverHelp_IMO.mdf'
    )
    ,FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA (
    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'
    )
GO

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.

CREATE DATABASE SQLSeverHelp_IMO ON PRIMARY (
    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'
    )
GO

-- Adding MEMORY_OPTIMIZED_DATA filegroup 
USE [master]
GO

ALTER DATABASE [SQLSeverHelp_IMO] ADD FILEGROUP [SQLSeverHelp_IMO_FG] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [SQLSeverHelp_IMO] ADD FILE (
    NAME = N'SQLSeverHelp_IMO_dir'
    ,FILENAME = N'C:\IMO_Database\SQLSeverHelp_IMO_dir'
    ) TO FILEGROUP [SQLSeverHelp_IMO_FG]
GO

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)

ALTER DATABASE [SQLSeverHelp_IMO] ADD FILE (
    NAME = N'SQLSeverHelp_IMO_dir_1'
    ,FILENAME = N'C:\IMO_Database\SQLSeverHelp_IMO_dir_1'
    ) TO FILEGROUP [SQLSeverHelp_IMO_FG]
GO

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 (
    iID INT NOT NULL PRIMARY KEY NONCLUSTERED
    ,vFName VARCHAR(20) NOT NULL
    ,vLName VARCHAR(20) NOT NULL
    )
GO

-- In-Memory table, New Syntax highlighted 
CREATE TABLE MyFirstMemporyOptimizedTable (
    iID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000)
    ,vFName VARCHAR(20) NOT NULL
    ,vLName VARCHAR(20) NOT NULL
    )
    WITH (
            MEMORY_OPTIMIZED = ON
            ,DURABILITY = SCHEMA_AND_DATA
            )
GO

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 NATIVE_COMPILATION
        ,SCHEMABINDING
        ,EXECUTE AS OWNER
AS
BEGIN ATOMIC
    WITH (
            TRANSACTION ISOLATION LEVEL = SNAPSHOT ,LANGUAGE = 'us_english'
            )

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

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.

• TRANSACTION ISOLATION LEVEL and LANGUAGE are also mandatory.

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.

Cheers,
Balmukund Lakhani
Twitter @blakhani
Author: SQL Server 2012 AlwaysOn – Paperback, Kindle