Sep 22, 2013

The feature 'nullable columns' is not supported with indexes on memory optimized tables

In sql server 2014 if we will try to create a memory optimized table by following script:

CREATE TABLEtblEmployee(
     ntEmpID BIGINT ,
     vcName VARCHAR(50),
     moSalary MONEY,
     CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)
WITH(MEMORY_OPTIMIZED =ON) 

We may get error message something like this:

Msg 10794, Level 16, State 7, Line 1
The feature 'nullable columns' is not supported with indexes on memory optimized tables.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Cause: In sql server 2014 we cannot create indexes on that column which allow null values in the memory optimized tables.

Solution: Add constraint NOT NULL in the indexed column. For example:

CREATE TABLEtblEmployee(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID) WITH (BUCKET_COUNT=1024),
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON) 

Or

CREATE TABLEtblEmployee(
     ntEmpID BIGINT NOT NULL,
     vcName VARCHAR(50),
     moSalary MONEY,
     CONSTRAINT PK_sample_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)

WITH(MEMORY_OPTIMIZED =ON)

No comments:

Post a Comment