How to manage identity column in In-Memory tables


As there are certain limitation which I also mentioned in my previous articles.
One of them is identity is not supported.
So when there is identity column is used in table we have to change code and use SEQUENCE instead.

Lets see an example




CREATE TABLE TEST
(id INT identity(1,1)  NOT NULL ,
data VARCHAR(50) NOT NULL ,
INDEX IDX_ID NONCLUSTERED (id))
WITH
(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)







We have to remove identity  from this script



CREATE TABLE TEST
(id INT   NOT NULL ,
data VARCHAR(50) NOT NULL ,
INDEX IDX_ID NONCLUSTERED (id))
WITH

(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)


Now we will have to create  a sequence for inserting data


CREATE SEQUENCE SEQ_INSERT AS INTEGER START WITH  1 INCREMENT BY 1



DECLARE @I INTEGER
DECLARE @SEQ INTEGER
SET @I = 1
WHILE @I <= 1000
BEGIN
       SET @SEQ = NEXT VALUE FOR DBO.SEQ_INSERT
       INSERT INTO TEST VALUES (@SEQ, 'VALUE :' + CONVERT(VARCHAR(10) , @SEQ ) )
       SET @I = @I + 1
END






No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts