When it comes to SQL Server, there are always new features and capabilities being introduced with each version. One such addition is the InMemory OLTP support for Foreign Key. However, it is important to understand the limitations and considerations when using this feature.

In a recent blog post, I discussed the concept of InMemory OLTP support for Foreign Key and its benefits. However, one of my readers encountered an error while experimenting with this feature. This led me to investigate further and understand the issue.

The reader mentioned that they were getting an error stating that Foreign Key relationships between memory optimized tables and non-memory optimized tables are not supported. After some discussion, it became clear that the reader had modified the script provided in the blog post and changed the creation of one of the tables to a Disk based table instead of an InMemory OLTP table.

Here is the modified script:

CREATE TABLE Products (
    ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
    ProductName VARCHAR(25)
);

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

As you can see, the Products table was created as a normal Disk based table, while the ProductSales table was created as an InMemory OLTP table. This mismatch in table types resulted in the error.

It is important to note that Foreign Key relationships between memory optimized tables and non-memory optimized tables are not supported in SQL Server. This means that if you are using InMemory OLTP tables, all related tables should also be InMemory OLTP tables.

While it is possible that the SQL Server product team may address this limitation in future versions, it is crucial to understand the current capabilities and limitations of InMemory OLTP support for Foreign Key.

So, how important are these capabilities to you? Do you require relationship management between InMemory and Disk based tables? I would love to hear your thoughts and experiences in the comments section.