How to reset IDENTITY in Memory-Optimized Tables

Even though SEQUENCE object is available with much more flexibility, we still use IDENTITY property for adding sequence values to tables, specifically when we need to introduce a surrogate key. However, if you need the same with Memory-Optimized tables, you need to know certain things.

Can I add the IDENTITY property to Memory-Optimized tables?

Yes, it is possible but it should be always IDENTITY(1,1). You cannot use a different values for seed and increment, they should be always set as 1.

CREATE TABLE dbo.MemoryOptimizedTable
(
 Id int IDENTITY(1,1) NOT NULL primary key nonclustered,
 CurrentDate datetime NULL default (getdate())
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO 5

Can I reset the IDENTITY seed using DBCC CHECKIDENT?

No, this is not supported. If you need to reset the IDENTITY seed, only way is inserting a new value explicitly by turning SET IDENTITY_INSERT on. As shown in the example, once the record with value 100 is inserted, the next value of the seed is set to 100+1.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (100, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

What if insert a lower value explicitly?

It is possible as long as it does not violate any rules. Look at the code below. It inserts a record with value 50 explicitly. But it does not mean that the seed is getting reset to 50+1. This does not happen because the last generated value is greater than 50+1. Therefore, the value of next record is 102, not 51.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (50, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

SQL Server needs memory optimized file group even for non-durable memory-optimized tables?

We know that SQL Server requires an additional file group that is marked as MEMORY_OPTIMIZED_FILEGROUP and a data file associated with it if memory-optimized tables need to be created. We know for sure that memory-optimized tables that are created as SCHEMA_AND_DATA require data files to write data to disk because they are coexist with disk-based tables but tables that are created as SCHEMA_ONLY require the same?

Memory-optimized tables that are created as non-durable (SCHEMA_ONLY) maintain both data and indexes in memory. Therefor, theorically it does not need anything to be written to disk. However, if you try to create a non-durable memory-optimized table without adding a memory-optimized-file-group, you get the following message;

Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

This says even for non-durable tables, it needs the file group. Although it does not maintain data in the disk, it has to create the table as the schema has to be maintained. Therefore, regardless of the type of memory-optimized tables, file group with MEMORY_OPTIMIZED_FILEGROUP option should be added to the database with a data file before creating memory-optimized tables.