Sep 22, 2013

The feature 'clustered index' is not supported with memory optimized tables

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

CREATE TABLEtblEmployee(
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

We may get error message something like this:

Msg 10770, Level 16, State 81, Line 1
The feature 'clustered index' is not supported with memory optimized tables.

Cause: There is not any concept of clustered index in the memory optimized table. By default sql server 2014 creates clustered index on the primary key column.

Solution: Specify Non-clustered index in the primary key column. For example:

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

Or

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

WITH(MEMORY_OPTIMIZED =ON)  

No comments:

Post a Comment