Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – Seed and Increment value other than 1 not supported

Hi friends, in this blog we will discussed another limitation of In-Memory OLTP that seed and increment value other than 1 while defining identity property to a column is not supported.

Let’s quickly see a demo on this –

First we will create a table defining identity property with seed 1 and incremental value 1.

CREATE TABLE T4

(

ID INT IDENTITY(1,1),

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

When I execute this query, table will created successfully.

Now, let’s change the identity value (1,1) to (1,4) and re run the query.
We will drop the table T4 and re execute the query modifying identity property.

DROP TABLE T4

Recreate the table again –

CREATE TABLE T4

(

ID INT IDENTITY(1,4),

[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH

WITH (BUCKET_COUNT = 100000),

[City] varchar(32)  null,

[State_Province] varchar(32)  null,

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Oops we got an error ‘The use of seed and increment values other than 1 is not supported with memory optimized tables.’

Lim3

So, finally we got to know one more limitation of In-memory OLTP tables that no seed and increment value other than 1 is supported with it.

That’s all for the day folks.

Advertisements

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