Hello All,

I hope you read my previous blog In-Memory OLTP . This is my second blog in In-Memory OLTP Series. As of now, you should have known what Sql Server In-Memory OLTP is. Giving you a quick recap, this new feature of Sql Server allows you to store your data entirely in memory giving you up to 100 times performance benefit. In real time, you can expect up to 40 times performance improvement. The coolest of all is your application doesn’t require any changes if you have your table in memory.

In this blog, I will specially focus on how do we create memory-optimized table. You will use same T-SQL code that you are using till date to create table except few addition. To fit your table in memory, you should have memory-optimized database. You can create new database as memory-optimized or you can change existing database to contain memory-optimized file group. You can store both memory-optimized and disk-based tables in memory-optimized database.

The process below is same for existing database and for new databases as well.

1. T-SQL:
   After you have executed Create Database statement, issue the following command :

   ALTER DATABASE IMOLTP ADD filegroup IMOLTP_MOD2 CONTAINS MEMORY_OPTIMIZED_DATA
   
   ALTER DATABASE IMOLTP ADD FILE (
       NAME = 'imoltp_mod2'
     ,filename = 'D:\Data\imoltp_mod2'
     ) TO filegroup imoltp_mod2
   GO

    

   Don’t forget to replace ‘IMOLTP’ by your database name. Also, make sure that the path provided in file name exists. In this case, D:\Data should already exist. What we are doing here is simply adding Memory Optimized File group to our database. This file group is needed for Memory Optimized tables.

2. SSMS:

Another way to add Memory-Optimized File group to your table is to go through Sql Server Management Studio. Here are the steps:

1. If you are migrating existing database, Select database and click on properties and go to Filegroups tab. If you are creating new database then also go to Filegroups tab.
2. There is section called Memory Optimized Data there. Add your memory optimized file group name and it’s done.
   

   Memory Optimized FileGroup

Now, we have database with memory optimized Filegroup so we can add memory-optimized table in this database.

Below is the query to create memory-optimized table in your database.

CREATE TABLE [ShoppingCart] (
    [ShoppingCartId] BIGINT NOT NULL
    PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 2000000)		
  ,[UserId] INT NOT NULL 
  ,[CreatedDate] DATETIME2 NOT NULL
    INDEX Ix_CreatedDate NONCLUSTERED
  ,[TotalPrice] MONEY
  )
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA)
GO

This query should look familiar to you but, there are lots of things to note on this create statement.

Option MEMORY_OPTIMIZED=ON is compulsory for the table to be Memory Optimized Table. DURABILITY=SCHEMA_AND_DATA is optional. If you don’t provide, durability will be SCHEMA_AND_DATA. This means, your data is not lost with power loss or Serer shutdown. I will discuss in detail about this on upcoming blog. Another option is SCHEMA_ONLY. In this option, your data is in memory only till your server is up. Server goes off, you lose data.

Another thing you need to understand is HASH WITH (BUCKET_COUNT = 2000000). This is hash index we are creating in in-memory OLTP. What is that number 2 with few zeros? This is the way you create hash index in in-memory tables. The Number BUCKET_COUNT should always be 1-2 times of number of unique rows a column can have. Over sizing is okay.

Now, you have created memory optimized database and table. Now we will create natively-compiled stored procedure. I won’t explain lot about natively compiled stored procedure in this blog as this piece of text is getting longer and i am afraid you won’t read anything of this.

Execute the following code to create natively compiled stored procedure :

IF OBJECT_ID('usp_insertSampleCarts') IS NOT NULL
    DROP PROCEDURE usp_insertSampleCarts
GO

CREATE PROCEDURE dbo.Usp_InsertSampleCarts @startId INT ,@insertCount INT
  WITH 
    NATIVE_COMPILATION
    ,SCHEMABINDING
    ,EXECUTE AS OWNER
AS
BEGIN ATOMIC
  WITH 
  (TRANSACTION ISOLATION LEVEL = SNAPSHOT
    ,LANGUAGE = N'us_english'
  )

  DECLARE @shoppingcartId BIGINT = @startid

  WHILE @shoppingcartId < @startId + @insertCount
  BEGIN
    INSERT INTO dbo.ShoppingCart
    VALUES (
      @shoppingcartId
      ,10000 + @shoppingcartId
      ,dateadd(day, @shoppingcartId + 10000 * RAND(), '2014-07-01T00:00:00')
      ,100000.0 * RAND()
      )

    SET @shoppingcartId += 1
  END
END
GO

Now call the stored procedure as below to insert rows in in-memory oltp table.

SET STATISTICS TIME OFF
SET NOCOUNT ON
EXEC usp_insertSampleCarts 1,400000
GO

Observe the time taken to insert 400,000 rows in in-memory table. It should be completed within 1-2 seconds while in disk-based table it would take ~2-3 Minutes.