Nullable columns in the index key are not supported with indexes on memory optimized tables
SQL Server 2014 in-memory database supports memory optimized table creation with indexes on columns which are NOT NULL. A primary key constraint or a SQL index on a nullable column can not be created on memory optimized table.
Msg 12301, Level 16, State 7, Line 15
Nullable columns in the index key are not supported with indexes on memory optimized tables.
Msg 1750, Level 16, State 0, Line 15
Could not create constraint or index. See previous errors.
Here is a SQL script which can be used to create a sample memory optimized table in a SQL Server 2014 database. Below CREATE TABLE script causes the SQL engine to throw "Nullable columns in the index key are not supported with indexes on memory optimized tables" error since table columns where the primary key and the index keys are defined NULLABLE, without NOT NULL hint
CREATE TABLE dbo.MemoryTable_Employee
(
id int,-- NOT NULL,
value float,-- NOT NULL,
earnings decimal(10,2),-- NOT NULL,
CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (id),
INDEX index_memoryoptimizedtable_earnings NONCLUSTERED (earnings),
INDEX hash_index_memoryoptimizedtable_value HASH (value) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
Unfortunately above SQL database create table script will throw the exception:
Msg 12301, Level 16, State 7, Line 15
Nullable columns in the index key are not supported with indexes on memory optimized tables.
Msg 1750, Level 16, State 0, Line 15
Could not create constraint or index. See previous errors.
Above SQL Server memory optimized table has 3 columns.
And a primary key constraint is being defined on a nullable column named id.
Besides this false column definition, two indexes are created on two columns which are again nullable.
The first index is a nonclustered index and the other index is a hash index.
All these three primary key constraint and sql indexes should be created on columns which are NOT NULL in a memory optimized table
So the correct database table create script is as follows. Please take your attention to the uncommented NOT NULL column property for each of the three table columns.
CREATE TABLE dbo.MemoryTable_Employee
(
id int NOT NULL,
value float NOT NULL,
earnings decimal(10,2) NOT NULL,
CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED (id),
INDEX index_memoryoptimizedtable_earnings NONCLUSTERED (earnings),
INDEX hash_index_memoryoptimizedtable_value HASH (value) WITH (BUCKET_COUNT = 1024)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
This time above memory-optimized table create script will execute successfully on a SQL Server 2014 in-memory database.
If you are a SQL Server database developer or a SQL Server database administrator (DBA) and not yet download and install SQL Server 2014, you can download SQL Server 2014 the most recent SQL Server version which supports in-memory processing features and install it on your test servers.