Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Everyday SQL

Patrick Keisler is a Premier Field Engineer for Microsoft with over 15 years of SQL Server experience working in various fields such as financial, healthcare, and government. He currently holds an MCSE Data Platform certification, MCITP certifications in SQL Server 2008 for administration and development, and CompTIA Security+. You can follow him on Twitter or listen to him speak at various SQL Saturdays and user group meetings.

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.

Comments

Leave a comment on the original post [www.patrickkeisler.com, opens in a new window]

Loading comments...