SQL Server Portal

Memory optimized table is a new feature introduced in SQL Server Hekaton.  As it is new for all of us, I am doing my research with different aspects of memory optimized table. In this article, we will create an identity field in the memory optimized table.

Given below is the script that we usually use to implement identity in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
[ID] integer identity(1,1) not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10771, Level 16, State 8, Line 3
The feature ‘identity column’ is not yet implemented with memory optimized tables.

Oooops.. I am unable to create identity column in memory optimized table.

So, how can we create an identity column in memory optimized table ? Here comes the SEQUENCE object solution to the rescue.

Let me create an identity column in memory optimized table in sequential steps.

Step 1 :
First of all, you need to create a memory optimized table without any identity column.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
[ID] integer not null primary key nonclustered hash
with (bucket_count = 1024),
[Name] varchar(200) not null
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Step 2 :
Once you have created the memory optimized table, create a Sequence object.

USE hkNorthwind
GO
CREATE SEQUENCE [Seq_Sample]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Insert the records using sequence in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-1')
GO
INSERT INTO tbl_sample ([ID],[Name])
VALUES (NEXT VALUE FOR [Seq_Sample],'raresql-2')
GO

Step 4 :
Just browse the table and check whether identity is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »