| title | Implementing IDENTITY in a Memory-Optimized Table |
|---|---|
| description | Learn about IDENTITY in memory-optimized tables in SQL Server. Memory-optimized tables support IDENTITY for a seed and increment value of one. |
| author | MikeRayMSFT |
| ms.author | mikeray |
| ms.date | 03/01/2017 |
| ms.service | sql |
| ms.subservice | in-memory-oltp |
| ms.topic | conceptual |
| monikerRange | =azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current |
[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]
IDENTITY is supported on a memory-optimized table, as long as the seed and increment are both 1 (which is the default). Identity columns with definition of IDENTITY(x, y) where x != 1 or y != 1 are not supported on memory-optimized tables.
To increase the IDENTITY seed, insert a new row with an explicit value for the identity column, using the session option SET IDENTITY_INSERT table_name ON. With the insert of the row, the IDENTITY seed is changed to the explicitly inserted value, plus 1. For example, to increase the seed to 1000, insert a row with value 999 in the identity column. Generated identity values will then start at 1000.