Posted in 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

Author:

I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on http://www.sqlservercentral.com/blogs/kapil-blogs/

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s