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.
