I ran into a problem yesterday trying to create the first memory_optimized table on a SQL Server 2016 SP1 Enterprise Edition.
I created the database and filegroup as so
CREATE DATABASE imoltp -- Transact-SQL CONTAINMENT = NONE ON PRIMARY ( NAME = N'imoltp', FILENAME = N'F:\UNREFRESHED_DB\imoltp.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'imoltp_log', FILENAME = N'F:\UNREFRESHED_DB\imoltp_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10% ) GO ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod] CONTAINS MEMORY_OPTIMIZED_DATA; ALTER DATABASE imoltp ADD FILE (name = [imoltp_dir], filename= 'F:\UNREFRESHED_DB\imoltp_dir') TO FILEGROUP imoltp_mod; go
Then I created the table
USE imoltp GO CREATE TABLE imoltp.[dbo].[T1] ( [TempID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), [object_ids] NVARCHAR(255) NOT NULL, [names] NVARCHAR(255) NOT NULL, [comment] NVARCHAR(50) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY); GO
Which results in this error:
Msg 41334, Level 16, State 0, Line 8 The code generation directory cannot be created or set up correctly.
I finally figured out that SQL was trying to create a directory (XTP) at the default database location defined in the server properties which no longer existed in this situation. This directory is where it wants to store dll files that in-memory needs.
So my questions is this.
Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?