36 Chambers – The Legendary Journeys: Execution to the max!

May 27, 2014

Identity Integers In SQL Server In-Memory OLTP

Filed under: Database Administration — Kevin Feasel @ 6:00 pm

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;
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 92 other followers

%d bloggers like this: