This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Microsoft SQL Server 2016 comes with a variety of new additions and I am happy for exploring the new features of SQL Server 2016.
Microsoft SQL Server 2014 introduced Hekaton engine which used for In-Memory Database objects.
SQL Server 2014: Create Memory Optimized File Group and Table
SQL Server 2016 introduced Columnstore index for Memory-Optimized Table which was not possible previously.
If you are planning to store large amount of data In Memory, you can apply Columnstore Index on that In Memory OLTP Table.
It comes with one condition: you can not apply Columnstore Index on the particular column. It must apply to all the columns of In-Memory Table.
Small demonstration with all the possible errors:
Create a sample memory optimized table with column store index:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Employees ( EmpID INT ,EmpName VARCHAR(50) ,EmpSalary INT ,EmpDOJ DATETIME ,INDEX idx_tbl_Employees_columnstore CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON) GO |
You will get a below error:
|
1 2 |
Msg 41321, Level 16, State 7, Line 1 The memory optimized table 'tbl_Employees' with DURABILITY=SCHEMA_AND_DATA must have a primary key. |
Add primary key on script and execute it:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Employees ( EmpID INT PRIMARY KEY ,EmpName VARCHAR(50) ,EmpSalary INT ,EmpDOJ DATETIME ,INDEX idx_tbl_Employees_columnstore CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON) GO |
You will get a below error:
|
1 2 |
Msg 12317, Level 16, State 78, Line 3 Clustered indexes, which are the default for primary keys, are not supported with memory optimized tables. Specify a NONCLUSTERED index instead. |
Apply Non Clustered Primary key and execute it:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Employees ( EmpID INT PRIMARY KEY NONCLUSTERED ,EmpName VARCHAR(50) ,EmpSalary INT ,EmpDOJ DATETIME ,INDEX idx_tbl_Employees_columnstore CLUSTERED COLUMNSTORE ) WITH (MEMORY_OPTIMIZED = ON) GO |
If you have not created Memory Optimized File group and related files, you may get a below error:
|
1 2 |
Msg 41337, Level 16, State 100, Line 1 Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container. |
Create memory optimized file group and data files:
|
1 2 3 4 5 6 7 8 9 |
USE [master] GO ALTER DATABASE [Database_Name] ADD FILEGROUP [Memory_Optimized] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [Database_Name] ADD FILE ( NAME = N'HekatonMemory', FILENAME = N'C:\Hekaton\hek.mdf' ) TO FILEGROUP [Memory_Optimized] GO |
Leave a Reply