Implementing IDENTITY in a Memory-Optimized Table
IDENTITY(1, 1) is supported on a memory-optimized table. However, identity columns with definition of IDENTITY(x, y) where x != 1 or y != 1 are not supported on memory-optimized tables. The workaround for IDENTITY values uses the SEQUENCE object (Sequence Numbers).
First remove the IDENTITY property from the table you are converting to In-Memory OLTP. Then, define a new SEQUENCE object for the column in the table. SEQUENCE objects as identity columns rely on the ability to create DEFAULT values for columns that use the NEXT VALUE FOR syntax to get a new identity value. Since DEFAULTs are not supported in In-Memory OLTP, you need to pass the newly-generated SEQUENCE value either to the INSERT statement or to a natively compiled stored procedure that does the insert. The following example demonstrates this pattern.
-- Create a new In-Memory OLTP table to simulate IDENTITY insert
-- Here the column C1 was the identity column in the original table
--
create table T1
(
[c1] integer not null primary key T1_c1 nonclustered,
[c2] varchar(32) not null,
[c3] datetime not null
) with (memory_optimized = on)
go
-- This is a sequence provider that will give us values for column [c1]
--
create sequence usq_SequenceForT1 as integer start with 2 increment by 1
go
-- insert a sample row using the sequence
-- note that a new value needs to be retrieved form
-- the sequence object for every insert
--
declare @c1 integer = next value for [dbo].[usq_SequenceForT1]
insert into T1 values (@c1, 'test', getdate())
After performing the insert several times, you see valid monotonically increasing values in column [c1]. This result set is generated using table scan and hash index without ORDER BY so the rows are not ordered.