Hello my Followers in the last previous post i covered some points not supported in SQL Server 2014 in the new Technology and Feature Memory-Optimized-Table :
- Memory Optimized table doesn’t support Alter.
- We can’t create index after we create the table but before we can do (Non-Clustered Hash / Non-Clustered )
- Memory optimized tables cannot be created in system databases.
- The index option ‘fillfactor’ , data_compression is not supported with indexes on memory optimized tables.
- Indexes on character columns that do not use a *_BIN2 collation are not supported with indexes on memory optimized tables
- The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.
Today we are going to Show another Important point must be the DB Developer and DB Analyst / Architecture / DBA take care about it let’s go for our DEMO : I will work on the Same Database i sued it on the previous Post
What we will Cover today in this Post is :
- We can Create Index on NULL Column on Memory-optimized-table ..?
- We can Create Memory-optimized-table Without Primary Key Column ..?
- Clustered index Supported or not Supported in Memory-optimized-table ..?
- What about Computed Column / PERSISTED Supported or No in Memory-optimized-table ..?
- How we can Update the Primary key Column in Memory-optimized-table ..?
- Nullable columns in the index key are not supported with indexes on memory optimized tables
We Can’t Create index on Nullable Column on Memory optimized-table it Should be Not Null Column
CREATE TABLE [dbo].[orders_mem]( [O_OrderKey] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000), [O_CustKey] [int] NULL, [O_OrderStatus] [Nvarchar](64) NULL, [O_TotalPrice] [decimal](13, 2) NULL, [O_OrderDate] [datetime] NULL, --,INDEX IX_OrderDate NONCLUSTERED [O_OrderPriority] [Nvarchar](15) NULL, [O_Clerk] [Nvarchar](64) NULL, [O_ShipPriority] [int] NULL, [O_Comment] [Nvarchar](80) NULL, [skip] [Nvarchar](64) NULL INDEX IX_OrderDate (O_OrderDate ASC) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA) GO
- No Heap table in Memory Optimized table
The memory optimized table with DURABILITY=SCHEMA_AND_DATA must have a primary key
CREATE TABLE Employee ( ID int, NAME Nvarchar(50)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
- Clustered indexes not Supported in Memory Optimized table
As we see in the Pervious point No Memory optimized table without Primary key so i added the primary key and we now the default index for the primary key is Clustered index if we didn’t have any Clustered index on the table but the fact here in memory optimized table No Clsutered index so we Shoukd write behind the Primary key NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000) this the newNONCLUSTERED Type index on SQL Server 2014 NONCLUSTERED HASH
CREATE TABLE Employee ( ID int NOT NULL PRIMARY KEY , NAME Nvarchar(50)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
Msg 12317, Level 16, State 72, Line 1
Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables.
True Way to create Memory Optimized table with primary key
CREATE TABLE Employee ( ID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000), NAME Nvarchar(50)) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
- Computed columns are not supported with memory optimized tables
USE SQL2014_WorkShop GO CREATE TABLE Employee_Salary ( ID int NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=3000000), BASIC_Salary bigint NOT NULL, Monthly_bonuses Bigint NULL, ToTal_Salary as (BASIC_Salary+Monthly_bonuses)PERSISTED )WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_AND_DATA)
- Update Action not Supported on Primary Key Column on Memory-Optimized-table
WoooW in Memory-Optimized-table We can’t Update the primary Key Column If you need to do this you must delete it and insert the row again with new Value for the Primary key.
USE SQL2014_WorkShop go CREATE TABLE PRIMARY_table ( ID INT not null primary key nonclustered hash with (bucket_count = 1024), NAME Nvarchar(50) ) WITH (MEMORY_OPTIMIZED = ON) Go INSERT INTO PRIMARY_table (ID,Name) VALUES (1,'Mostafa')
NOW LET’S TY TO UPDATE THE PRIMARY KEY COLUMN
UPDATE PRIMARY_table SET ID = 2 WHERE ID = 1
Msg 12302, Level 16, State 9, Line 1
Updating columns that are part of the PRIMARY KEY constraint is not supported with memory optimized tables.
What we Should do in this Case ..? We Should do workaround like this
DELETE FROM PRIMARY_table WHERE ID = 1 GO INSERT INTO PRIMARY_table (ID,Name) VALUES (2,'Mostafa')
this only the way to update the primary key Column on Memory-Optimized-table
this not the End if you need to know more Secrets, more Surprise , More Workaround in SQL Server 2014 don’t close your email Still follow me Mostafa Elmasry to be the first one know this new Secrets in SQL Serve 2014 and you can View all my tips
See you in the next blog Post