In SQL Server 2014, we learned that memory-optimized tables cannot join to tables outside the current database. I wanted to see if this had changed in SQL Server 2016 CTP 3.2.
CREATE TABLE [dbo].[MOTest] ( MOTestID BIGINT IDENTITY(1,1) NOT NULL, ProductSubcategoryName VARCHAR(50) NOT NULL, CONSTRAINT [PK_MOTest] PRIMARY KEY NONCLUSTERED ( MOTestID ASC ) ) WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY) GO INSERT into dbo.MOTest ( ProductSubcategoryName ) VALUES ('Mountain Bikes'), ('Road Bikes'), ('Touring Bikes'), ('Cranksets'), ('Chains'), ('Something Else'); SELECT TOP (100) * FROM AdventureWorks2014.Production.ProductSubcategory ps INNER JOIN dbo.MOTest m ON m.ProductSubcategoryName = ps.Name;
(6 row(s) affected)
Msg 41317, Level 16, State 5, Line 24
A user transaction that accesses memory optimized tables or natively compiled modules cannot access more than one user database or databases model and msdb, and it cannot write to master.
In other words, we still cannot cross databases with memory-optimized tables.