SQL Server 2014 - InMemory Tables, Indexes and Stored Procedures

In this article, we will explore how to create memory optimized Tables, Indexes and Stored Procedures introduced in Microsoft SQL Server 2014. SQL Server 2014 as of this writing is in CTP1

To enhance the performance of OLTP, SQL Server 2014 has introduced a number of new features. One of them is In-Memory Tables, Indexes and Stored Procedures. The in-memory tables and indexes are memory optimized structures which allows the rows to be accessed using memory pointers. Simply put, using memory pointers, we can now access the rows more efficiently.

When you create indexes on in-memory tables, they do not occupy  disk space. The index existence is now in-memory. The in-memory data structures in SQL Server 2014 are lock-free data structures. So when you implement a transaction and two user’s try to modify the same row, the second transaction is always failed.
The in-memory structures does not have separate tools and a query language. This feature is fully integrated into SQL Server and thus you will use the same tools and language [T-SQL] for querying the data.

For demonstrations in this article, I am using SQL Server 2014 CPT 1 and Windows 8 OS. I have already created a database with the name “PurchaseOrderDB”. Let’s take a look at the following script –

CREATE TABLE Employees
(
    EmployeeID INT
        NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 100000),
    EmployeeName NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxEmpName] HASH WITH (BUCKET_COUNT = 100000),
    City NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxCity] HASH WITH(BUCKET_COUNT = 100000),
    Country NVARCHAR(200) NOT NULL,
    Region NVARCHAR(10) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

In the above CREATE TABLE script, you might have noticed some new keywords. Let’s first understand these new keywords before we proceed –

1. MEMORY_OPTIMIZED = ON – by using this keyword, you are creating memory optimized tables. Also note that this keyword can be used only with the CREATE TABLE statement.

2. DURABILITY = SCHEMA_AND_DATA – this option tells that the schema and data are durable/persisted. In case for some reason if the server crashes, you will still get the schema and data back. This is an optional setting and the default value is the same which we are using during our CREATE TABLE statement – SCHEMA_AND_DATA. The other option is Schema only.

3. NONCLUSTERED HASH WITH – HASHED INDEX is a new index which you can use only with in-memory tables. HASH INDEXES are used for point lookups. With HASH INDEX the BUCKET_COUNT is a must which is used for specifying how many buckets you want to have in the HASH table.

4. Other indexes which are known as secondary indexes excluding primary key index, now can be specified with the CREATE TABLE. As you can see that in the above script, we have indexes specified on EmployeeName and City column.

Note – The HASH INDEX can be specified only with CREATE TABLE.

When you write the above CREATE TABLE statement, internally SQL Server will generate the code for the table and then compile this code into a DLL. Once the DLL is ready, it will load it into the memory.

To access the tables and indexes from our PurchaseOrderDB database, we can create natively compiled stored procedures(SP’s). In these SP’s, we can write our business logic and the Data Manipulation Language (DML) statements as per our domain requirements. Let’s take a look at the syntax for creating a natively compiled stored procedure –



The create procedure script contains some new keywords. Let’s first understand these new keywords –

1. NATIVE_COMPILATION – This keyword is used for making Stored Procedures as native stored procedures.

2. SCHEMABINDING – This keyword is a must-use-keyword so that the table which is getting accessed inside the SP, should not get dropped accidently.

3. EXECUTE AS OWNER – which context the SP should get executed. That means under which user this stored procedure will run. The possible values are owner, self or a specific user.

4. Begin ATOMIC – a required block in native stored procedures. The ATOMIC block automatically COMMIT’s or ROLLBACK’s the transaction. If there is already a transaction, then it will create a SAVEPOINT or if there is no transaction, it will start with a new transaction.

5. The WITH block – This block decides the various session settings which will be always be fixed at SP creation time. The two required parameters are TRANSACTION ISOLATION LEVEL and LANGUAGE. You can set multiple options as well.

Now let’s start SQL Server 2014 if you haven’t already and create a database with the name “PurchaseOrderDB”. Once the database is ready, write the following code in your query window –

Step 1 – Write the USE database command first as shown below –

USE PurchaseOrderDB
GO

Step 2 – Now alter the database using the following commands –

ALTER DATABASE PurchaseOrderDB ADD  FILEGROUP [PODB_fg]
CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE PurchaseOrderDB ADD FILE
( NAME = N'PODB_FG', FILENAME = N'C:\PODBDATA')
TO FILEGROUP [PODB_FG]

The question is why did we alter the database as shown above? The reason is when you directly execute the script shown for CREATE TABLE in the beginning of this article, you will receive the following error –

The Memory_Optimized_DATA filegroup does not exist or is empty. Memory optimized tables cannot be created for database until it has one Memory_Optimized_DATA filegroup that is not empty

The error clearly tells you that the memory optimized table needs separate file group. So we are configuring our database with the new file group and giving the path of a directory which will be used by SQL Server to store the DLLs, which are created for Data Definition Language (DDL) statements as shown below –



Step 3 – Now let’s write the CREATE TABLE script as shown below –

CREATE TABLE Employees
(
    EmployeeID INT
        NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT = 100000),
    EmployeeName NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxEmpName] HASH WITH (BUCKET_COUNT = 100000),
    City NVARCHAR(200)
        COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IdxCity] HASH WITH(BUCKET_COUNT = 100000),
    Country NVARCHAR(200) NOT NULL,
    Region NVARCHAR(10) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

If you observe the code, we have also included collate with Latin1_General_100_Bin2. This is because string columns in a memory optimized columns must use BIN2 collation.



For more information, please refer the following MSDN link which lists the limitations, restrictions and workaround [if available] – http://msdn.microsoft.com/en-us/library/dn169381(v=sql.15).aspx

Once your table is created, you can query the table using our everyday T-SQL statements.

Step 4 – Write the following SELECT statement to query Employees table –

SELECT * FROM Employees

Step 5 – Let’s insert some records into our Employees table by using the following script –

BEGIN
    DECLARE @EmpID INT=1
    WHILE(@EmpID<100000)
        BEGIN
            INSERT INTO Employees VALUES(@EmpID,'DNCEmp','New York','USA','EAST')
            SET @EmpID+=1
        END
END

Now execute the query given in Step 4 and you will see the following result –



You can also use order by clause to get all the rows in order as shown below –

SELECT * FROM Employees ORDER BY EmployeeID

Step 6 – Write the memory optimized stored procedure as shown below –

CREATE PROCEDURE InsertEmployee
(
    @EmployeeID INT,
    @EmployeeName NVARCHAR(200),
    @City NVARCHAR(200),
    @Country NVARCHAR(200)
) WITH NATIVE_COMPILATION,
       SCHEMABINDING,
       EXECUTE AS OWNER
AS
BEGIN ATOMIC
    WITH
    (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = 'us_english'
    )
    SET @EmployeeID = 1
    WHILE(@EmployeeID<100000)
        BEGIN
            INSERT INTO dbo.Employees VALUES(@EmployeeID,@EmployeeName,@City,@Country,'EAST')
            SET @EmployeeID+=1
        END
END

Execute the SP as shown below –

exec InsertEmployee 1,'DNC','London','UK'

Now see the result in your table by repeating Step 4

Abstract – In this article we have seen how to create an in-memory or memory optimized table, indexes and Stored Procedures in SQL Server 2014. To access the rows, we have use T-SQL statements and to perform DML [Insert] operation, we have used Natively Complied Stored Procedure.