SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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)
Code

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.

Nullable columns in the index key are not supported with indexes on memory optimized tables

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)
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.