SQL SERVER 2016 – InMemory OLTP support for Foreign Key

When InMemory OLTP was introduced in SQL Server 2014, there were a number of limitations that restricted customers to effectively use the capability. One of the most commonly asked capability was around the ability to create Foreign Keys. On first look, I thought this was always available. But while experimenting and reading the documentation figured out this was the most asked and missing capability. SQL Server 2016 finally brings InMemory OLTP support for Foreign Key.

With SQL Server 2016, I found this feature was added subtly and the first thing I wanted to do was to check if it works on my local CTP3.2 versions. Yes, this would also work once the RTM happens but I was excited because a lot of times, people want to use Foreign Keys because date integrity is of prime importance when working with databases.

Here, I am going to start creating our InMemory OLTP database and create two tables with constraints and add some data to it.

USE MASTER
GO
DROP DATABASE IF EXISTS [InMem_OLTP]
GO
-- Create the database
CREATE DATABASE [InMem_OLTP]
ON  PRIMARY
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',size = 30MB)
LOG ON
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',size = 10MB)
GO
-- Add Inmemory Filegroup
USE InMem_OLTP
GO
ALTER DATABASE InMem_OLTP
ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE InMem_OLTP
ADD FILE (NAME = InMem_OLTP_InMemory, FILENAME = N'C:\Data\InMem_OLTP_mopt')
TO FILEGROUP [InMem_OLTP_InMemory]

We will create two tables as Products and Sales and create respective constraints to them.

-- Let us create the table srtuctures
USE InMem_OLTP
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY NONCLUSTERED HASH (ProductID) WITH (BUCKET_COUNT = 10000),
ProductName VARCHAR(25)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY NONCLUSTERED HASH (SalesID) WITH (BUCKET_COUNT = 10000),
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

Next is to test if the Foreign Key has taken effect. Let us insert some values into our database tables for the same.

-- Insert Values to see
INSERT INTO Products VALUES (1, 'SQLAuthority');
-- Success
INSERT INTO ProductSales VALUES (1, 1, 'Pinal');
-- Error
INSERT INTO ProductSales VALUES (2, 2, 'Dave');

As guessed right, we get the foreign key violation error as:

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint “fk_productSales_pid”. The conflict occurred in database “InMem_OLTP”, table “dbo.Products”, column ‘ProductID’.
The statement has been terminated.

As you can see, SQL Server 2016 has introduced capabilities that remove a lot of limitations that was available in prior editions. We will explore each of these in future too.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.