This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you don’t know about the Hekaton engine of SQL Server 2014, please visit below an article.
I am very surprised with the feature list of SQL Server 2016 because it came with lots of features and improvements. Whenever I explored SQL Server 2016, I got something new.
SQL Server 2016 already enhanced In-Memory OLTP by introducing the list of features. Let me share one of the features here.
In the previous version, you cannot create a FOREIGN KEY in memory optimized table. Now, SQL Server 2016 introduced a feature to store the reference of a table in memory so you can create a FOREIGN KEY in memory optimized tables.
You can create a reference between memory optimized tables; you cannot give reference of a Non-memory optimized table to a memory optimized table.
Below is a full demonstration on this.
If you do not configure Memory optimized file group and data file, use below script to configure it.
Add Memory optimized file group:
|
1 2 |
ALTER DATABASE Database_Name ADD Filegroup OLTP_InMemory CONTAINS MEMORY_OPTIMIZED_DATA |
Add data file into created Memory optimized file group:
|
1 2 3 |
ALTER DATABASE Database_Name ADD FILE (NAME = OLTP_InMemory, FILENAME = N'C:\SQLBackup\Hekaton') TO FILEGROUP OLTP_InMemory |
Create a sample In-Memory table:
|
1 2 3 4 5 6 |
CREATE TABLE tbl_Departments ( DeptID INT PRIMARY KEY NONCLUSTERED ,DeptName VARCHAR(20) )WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
Create a another In-Memory child table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_Employee ( EmpID INT PRIMARY KEY NONCLUSTERED ,EmpName VARCHAR(20) ,DeptID INT ,CONSTRAINT fk_tbl_Employee_EmpID FOREIGN KEY (DeptID) REFERENCES tbl_Departments(DeptID) ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); GO |
Try to give reference of Non-Memory optimized table to Memory optimized table:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tbl_EmpSalary ( EmpSalaryID INT PRIMARY KEY ,EmpID INT ,EmpSalary INT ,EmpSalaryMonth INT ,CONSTRAINT fk_tbl_EmpSalary_EmpID FOREIGN KEY (EmpID) REFERENCES tbl_Employee(EmpID) ) GO |
You will get below errors:
|
1 2 3 4 |
Msg 10778, Level 16, State 0, Line 1 Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported. Msg 1750, Level 16, State 1, Line 1 Could not create constraint or index. See previous errors. |
