Cross-Database Memory Optimized Queries Still Not Available

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;

My result:

(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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s