Implement IDENTITY Column in SQL Server Memory Optimized Table

Introduction

Memory Optimized tables were introduced in SQL Server 2014 (Hekaton). As we know, memory optimized tables do not support an IDENTITY column. So how can we create an INDENTITY (auto incremented) column with a memory optimized table?

Example

CREATE TABLE IdentityTest
(
        ID INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
        Name VARCHAR(50) not null
)
WITH (MEMORY_OPTIMIZED = ON)

Output

Msg 10771, Level 16, State 7, Line 2
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Solution

The workaround of an IDENTITY value is to use a SEQUENCE object. A SEQUENCE object (introduced with SQL Server 2012) works similarly to an IDENTITY value with large scope to the IDENTITY. The SEQUENCE object is not limited to a column or table but it is scoped to an entire database. One of the advantages of a SEQUENCE object is, it can be controlled by application code also. This SEQUENCE can be shared with multiple tables.

Implement IDENTITY column in the memory optimized table with SEQUENCE object

To implement an IDENTITY column in a memory optimized table, we can use a SEQUENCE object. The following are the steps to use a SEQUENCE object as an IDENTITY column with a memory optimized table.

Step 1

Create a memory optimized table and a SEQUENCE object.

--Create memory optimized table

CREATE TABLE IdentityTest
(
        ID INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
         Name VARCHAR(50) not null
)
WITH (MEMORY_OPTIMIZED = ON)

--Create SEQUENCE object

CREATE SEQUENCE [dbo].[TableNextId]
AS [int]
             START WITH 1000
              INCREMENT BY 1
              MINVALUE  1
              MAXVALUE 10000

Step 2

Use a SEQUENCE object to generate the next Id using the “NEXT VALUE FOR” function. SQL Server writes a new value in the value in the SEQUENCE to the system table every time the NEXT VALUE FOR function is called. So we can get it filled like an IDENTITY column.
 

DECLARE @nextId INTEGER = NEXT VALUE FOR [dbo].[TableNextId]]
INSERT INTO IdentityTest VALUES (@nextId, 'my Test')

Output

Conclusion

A memory optimized table does not support an IDENTITY (auto incremented) column. But using a SEQUENCE object, we can get an auto incremented value for a numeric data type column.