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.