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 SQL Server 2014 Download and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.

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)

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 Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems