In-Memory OLTP and the Identity Column

Over the past month I've been playing around with the new In-Memory OLTP (code name: "Hekaton") features within SQL Server 2014 CTP2. My organization is all about low latency applications, and this is one feature of SQL Server that I need to get familiar with ASAP.

To do this, I started my own little project that takes an existing database and converts parts of it into in-memory tables.  Once that step is complete, I could work on rewriting the TSQL code.

It might seem fairly simple, but with every new feature of SQL Server there are usually limitations. And one of the first ones I noticed was the use of an IDENTITY column. They are prohibited in Hekaton tables which means I had to find an alternative. This is where the new SEQUENCE object comes into play.

The CREATE SEQUENCE command allows you to create a user-defined numerical value that can be ascending or descending. This gives it much more flexibility than an IDENTITY column, and it's fully supported for use within an in-memory table.

Looking at the example below, we have a table with an IDENTITY value used for the OrderID column.

CREATE TABLE dbo.Orders (
     OrderID INT IDENTITY(1,1) NOT NULL
    ,OrderDate DATETIME NOT NULL
    ,CustomerID INT NOT NULL
    ,NetAmount MONEY NOT NULL
    ,Tax MONEY NOT NULL
    ,TotalAmount MONEY NOT NULL
);
GO

And we have a typical insert statement to insert a new order. Notice the IDENTITY column is not specified because it's value is automatically generated during at runtime.

INSERT INTO dbo.Orders (OrderDate,CustomerID,NetAmount,Tax,TotalAmount)
VALUES (GETDATE(),16,9.99,0.80,10.79);
GO

So how would this need to be rewritten to be turned into an in-memory table?  First we just need to create the table without the IDENTITY value.

CREATE TABLE dbo.Orders (
     OrderID INT NOT NULL
    ,OrderDate DATETIME NOT NULL
    ,CustomerID INT NOT NULL
    ,NetAmount MONEY NOT NULL
    ,Tax MONEY NOT NULL
    ,TotalAmount MONEY NOT NULL
);
GO

Then we'll need to create a SEQUENCE that produces the same order of values as the IDENTITY. In our example, it starts and 1 and increments by one.

CREATE SEQUENCE dbo.CountBy1 AS INT
    START WITH 1
    INCREMENT BY 1;
GO

The insert statement will look a little different, because we'll need to call the NEXT VALUE FOR function for the SEQUENCE we just created.

INSERT INTO dbo.Orders (OrderID,OrderDate,CustomerID,NetAmount,Tax,TotalAmount)
VALUES (NEXT VALUE FOR dbo.CountBy1,GETDATE(),16,9.99,0.80,10.79);
GO

You could also generate the next sequence number ahead of time and then insert the value in a later statement.

DECLARE @NextValue INT = NEXT VALUE FOR dbo.CountBy1;

-- Do some other stuff here then insert --

INSERT INTO dbo.Orders (OrderID,OrderDate,CustomerID,NetAmount,Tax,TotalAmount)
VALUES (@NextValue,GETDATE(),16,9.99,0.80,10.79);
GO

So far, I think Microsoft has done a great job with the new Hekaton feature. They are definitely marketing it as a feature to implement with little to no changes in code, but I think that really depends on the existing code.  This is very basic rewrite, but one that only took a few minutes to implement.

Check out Books Online for more detailed information about both Hekaton and Sequence Numbers.