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;