SQL Server Portal

‘Update statement’ is one of the frequently used Data Manipulation Language (DML) in SQL Server scripting.  We usually use this statement to rectify the data. Recently, I was working on memory optimized table and I tried to update a primary key column and I came across an error.
Given below is the script that we usually use to update any disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
--Create table
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--Insert record into the table
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (1, 'SQL Server 2012',getdate())
GO
--Update table
Update tbl_Product_Master SET [Product ID]=11 WHERE [Product ID]=1
GO

Msg 10770, Level 16, State 10, Line 40
The operation ‘primary key update’ is not supported with memory optimized tables.

Oooops.. I am unable to update a primary key in memory optimized table. So how to update the primary key of a memory optimized table?

Let me explain it step by step.

Step 1 :
In this step, you need to delete the record which you need to update .

USE hkNorthwind
GO
DELETE FROM tbl_Product_Master WHERE [Product ID]=1
GO

Step 2 :
In this step, you need to insert the record with the updated data.

USE hkNorthwind
GO
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (11, 'SQL Server 2012',getdate())
GO

Step 3 :
Browse the table and check whether the data has been updated or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

Read Full Post »