Sep 22, 2013

Hash indexes are permitted only in memory optimized tables

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

CREATE TABLEtblEmp(
     ntEmpID BIGINT NOT NULL
     CONSTRAINT PK_memoryoptimizedtable PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024)
)

We may get error message something like this:

Msg 10791, Level 15, State 1, Line 1
Hash indexes are permitted only in memory optimized tables.

Cause: We can create has indexes only in the memory optimized tables.
Solution: Make the table as memory optimized table. 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