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;

Putin or Hitler? The definitive primer

Many, many news outlets have broadcasted stories comparing President of the Russian Federation Vladimir Putin with Chancellor of the Third Reich Adolf Hitler. It strikes me that it might be useful to help people figure out the difference! The primer is simple: start from the top and work your way down.

— Is the person living or dead? If dead, it is Hitler. If living, it is Putin.*

— Look at the person’s face. If they have a mustache, it is Hitler. If they do not, it is Putin.

— Look at the top of the head. If you see hair, it’s Hitler. If you see very little to no hair, it is Putin.

— If you are looking at a photograph, is the shirt on or off? If there is a shirt, it could be either. If there is no shirt, it is Putin.

— Is the person eating a delicious steak or other meat product? If they are, it is Putin. If they are not, it is Hitler.

— Is the person consuming an alcoholic beverage? If so, it is Putin. If not, it is Hitler.

— When they smile, do they appear genuinely happy? If so, it is Hitler. If not, but they do look like they are mocking you, it is Putin.

— Address the individual in German. If you get a response, it could be either Hitler or Putin. If you do not get a response, it is probably Hitler. Please make sure you verified if the person is living or dead.**

— Address the individual in Russian. If the response is virulent hatred, it is probably Hitler, depending on the content of your address. If you get any other response, including no response, it is Putin.***

— Ask the individual about the Soviet Union. If you get a nostalgic sigh, it is Putin. If the response is murderous rage, it is Hitler.

— Ask the person about Karl Marx. If the individual you were talking to is now a smoking crater, caused by spontaneous combustion from pure, unadulterated hatred, it is Hitler. In all other cases, it is Putin.

— In fact, if the response to anything is any emotion other than boredom or cynical mocking, it’s probably Hitler.

I hope you have found this primer helpful. If you have questions, please do not hesitate to ask!

*As of this blog post. This will become untrue in the future. Future responses, obviously, imagine a reanimated, sentient Hitler. Not a zombie Hitler, though.

**Putin lived in East Germany as part of the KGB.

***No response because you are not worth his time.