Foreign Key Support in SQL Server 2016 Memory Optimized Tables

By: Ahmad Yaseen   |   Comments   |   Related: > In Memory OLTP

Problem

The SQL Server In-Memory OLTP feature, also known as Hekaton, is an interesting in-memory processing technology that was introduced in SQL Server 2014 and optimized mainly for the Online Transaction Processing (OLTP) workload. As with any new feature, it came with a number of limitations and restrictions such as the ability to create Foreign Keys on the Memory-Optimized tables. Has this limitation been removed in SQL Server 2016?

Solution

The SQL Server In-Memory OLTP feature, with its exciting in-memory processing technology, enhances SQL Server querying performance in many scenarios, but the limitations and restrictions that came with this feature make it not feasible to use in many cases. In SQL Server 2016, a number of the In-Memory OLTP limitations have been removed, making this feature more useful. One of the limitations removed is the ability to create a Foreign Key on Memory-Optimized tables, which is very important to guarantee data integrity within these tables.

In this tip, we will test the Foreign Key creation using two SQL Server instances, the first one is a SQL Server 2014 instance and the second one is a SQL Server 2016 instance, both containing the same MSSQLTipsDemo testing database. Let's prepare the MSSQLTipsDemo database in both instances to host the memory optimized tables, by adding a new filegroup that contains MEMORY_OPTIMIZED_DATA, adding a new database data file on that filegroup and finally enabling the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT as shown in the below script:

USE MSSQLTipsDemo 
GO
ALTER DATABASE MSSQLTipsDemo ADD FILEGROUP MemoryOpt_FG CONTAINS MEMORY_OPTIMIZED_DATA   
ALTER DATABASE MSSQLTipsDemo ADD FILE (name='MemoryOptDataDF', filename='D:\Data\MemoryOptDataDF') TO FILEGROUP MemoryOpt_FG   
ALTER DATABASE MSSQLTipsDemo SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO

Now the MSSQLTipsDemo databases in both instances are ready to host the memory optimized tables. We will start by creating the EmployeeDep_MemOptTable as a simple memory optimized table that will act as the parent table on both instances:

USE MSSQLTipsDemo 
GO

CREATE TABLE [EmployeeDep_MemOptTable]
(
 [DepID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_EmployeeDep_MemOptTable_Depid PRIMARY KEY NONCLUSTERED HASH (DepID) WITH (BUCKET_COUNT = 10000),
 [Dep_Name] nvarchar(10) NULL
 )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

Then trying to create the child Employee_MemOptTable memory optimized table that contains the Dep_ID column as a foreign key from the previous table. If we try to run the below CREATE TABLE script on the SQL Server 2014 instance:

USE [MSSQLTipsDemo]
GO
SELECT @@VERSION AS SQLServerVersion
GO
CREATE TABLE [Employee_MemOptTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_Employee_MemOptTable_id PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT = 10000),
 [First_Name] nvarchar(10) NULL,
 [FLast_Name] nvarchar(10) NULL,
 [Dep_ID] INT  CONSTRAINT fk_Employee_MemOptTable_DepID FOREIGN KEY REFERENCES EmployeeDep_MemOptTable(DepID),
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

The table creation will fail with the below error message, indicating that the Foreign Key is not supported with memory optimized tables:

Trying the same CREATE TABLE script on the SQL Server 2016 instance, the command will execute successfully and the table will be created with no error, as the Foreign Key is supported now in SQL Server 2016 with memory optimized tables:

We can also check the Foreign Key functionality by inserting two new records to the EmployeeDep_MemOptTable parent table:

USE [MSSQLTipsDemo]
GO
INSERT INTO [dbo].[EmployeeDep_MemOptTable]
           ([Dep_Name])
     VALUES
           ('IT'), ('HR')
GO

Then trying to insert a new record to the Employee_MemOptTable child table with Dep_ID value not in the parent table:

USE [MSSQLTipsDemo]
GO

INSERT INTO [dbo].[Employee_MemOptTable]
           ([First_Name]
           ,[FLast_Name]
           ,[Dep_ID])
     VALUES
           ('AA','BB',3)
GO

The INSERT statement will fail stating the inserted value conflicts with the Foreign Key constraint as follows:

Let's insert the record again with a valid Dep_ID value:

USE [MSSQLTipsDemo]
GO

INSERT INTO [dbo].[Employee_MemOptTable]
           ([First_Name]
           ,[FLast_Name]
           ,[Dep_ID])
     VALUES
           ('AA','BB',1)
GO

The record will be inserted successfully:

What if we try to create a standard disk based table with a Foreign Key that references a Memory optimized table? Let's try to create the Employee_PhysicalTable table with the same schema as the Employee_MemOptTable table, but this time with a normal disk based table, and again with a Foreign Key that referenced the Dep_ID column to the EmployeeDep_MemOptTable parent Memory optimized table:

USE [MSSQLTipsDemo]
CREATE TABLE [Employee_PhysicalTable]
(
 [ID] INT IDENTITY(1,1) NOT NULL  CONSTRAINT PK_Employee_PhysicalTable_id PRIMARY KEY NONCLUSTERED ,
 [First_Name] nvarchar(10) NULL,
 [FLast_Name] nvarchar(10) NULL,
 [Dep_ID] INT  CONSTRAINT fk_Employee_PhysicalTable_DepID FOREIGN KEY REFERENCES EmployeeDep_MemOptTable(DepID)
)
GO

The result is unexpected, showing that the Foreign Key relationship between the memory optimized and non-memory optimized table is not supported in SQL Server 2016 as shown in the error message below:

Next Steps

• Check out Getting started with SQL Server 2014 In-Memory OLTP.
• Check also SQL Server Memory Optimization Advisor to Migrate to In-Memory OLTP.
• Review all In-Memory OLTP tips.

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips