Identity Integers In SQL Server In-Memory OLTP

There’s a piece of no-longer-correct information floating around on the Internet, and I wanted to put up a blog post to point out the correct answer.  A lot of people say that identity integers are not allowed in SQL Server 2014’s In-Memory OLTP (i.e., Hekaton).  In fact, identity integers are allowed and are fully supported in the full release of SQL Server 2014.

Let’s create a new In-Memory OLTP table using an identity integer as the primary key.

CREATE TABLE dbo.IdentityTest
(
	Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000),
	IncidentNumber VARCHAR(19)
)
WITH
(
	MEMORY_OPTIMIZED = ON,
	DURABILITY = SCHEMA_ONLY
);

From there, I want to show you that you can certainly insert into a table with an identity column:

INSERT INTO dbo.IdentityTest(IncidentNumber) VALUES ('This can''t work!');
SELECT * FROM dbo.IdentityTest;

If you run it locally, you’ll get back the row you expect, proving that it absolutely can work.

“But wait,” you might say, “what about natively compiled stored procedures?” We know that natively compiled stored procedures are neutered in V1, so that’s a good question. Let’s gin up another example:

CREATE PROCEDURE dbo.dbo.IdentityTest_NativeProcedure
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')

	INSERT INTO dbo.IdentityTest(IncidentNumber) VALUES ('This can''t work!');

END
GO

EXEC dbo.dbo.IdentityTest_NativeProcedure;
SELECT * FROM dbo.IdentityTest;

If you run this, you’ll see that even a natively compiled stored procedure successfully uses an identity column. Given that you can’t get FOR NEXT VALUE on a sequence in a natively compiled stored procedure, this is an important finding.

Now let’s clean up the junk:

DROP PROCEDURE dbo.IdentityTest_NativeProcedure;
DROP TABLE dbo.IdentityTest;