Sep 22, 2013

The option 'bucket_count' must be specified for index '' on table ''. It is required for hash indexes.

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(MEMORY_OPTIMIZED =ON)

Msg 10789, Level 15, State 1, Line 6
The option 'bucket_count' must be specified for index '' on table ''. It is required for hash indexes.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Cause: It is necessary to specify bucket count for the hash indexes in the memory optimized tables.

Solution: Add bucket count option. 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