In this article, I want to explain you about How to Apply IDENTITY Column with SQL Server Memory Optimized Table. Memory Optimized tables were introduced in SQL Server 2014. As we know, memory optimized tables don't support an IDENTITY column. thus however will we produce an INDENTITY (auto incremented) column with a memory optimized table?

And here is the example code:
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)

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

The workaround of an IDENTITY value is to use a SEQUENCE object. A SEQUENCE object (introduced with SQL Server 2012) works equally to an IDENTITY value with massive scope to the IDENTITY. The SEQUENCE object isn't restricted to a column or table however it's scoped to a whole information. one among the benefits of a SEQUENCE object is, it is controlled by application code additionally. This SEQUENCE is shared with multiple tables.

Apply IDENTITY Column with SQL Server Memory Optimized Table
To apply an IDENTITY column in a memory optimized table, you must 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 get following Id using the “NEXT value FOR” operate. SQL Server writes a replacement value within the value in the SEQUENCE to the system table whenever the next value FOR function is called. thus we will catch on filled like an IDENTITY column.
DECLARE @nextId INTEGER = NEXT VALUE FOR [dbo].[TableNextId]]
INSERT INTO IdentityTest VALUES (@nextId, 'my Test')

A memory optimized table doesn't support an IDENTITY (auto incremented) column. however using a SEQUENCE object, we will get an auto incremented value for a numeric data type column.

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.