Sep 22, 2013

The memory optimized table '' has no primary key defined. A memory optimized table with DURABILITY=SCHEMA_AND_DATA must have a primary key

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

We may get error message something like this:

Msg 41321, Level 16, State 7, Line 1
The memory optimized table '' has no primary key defined. A memory optimized table with DURABILITY=SCHEMA_AND_DATA must have a primary key.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Cause: It is compulsory to have primary key in the table to make memory optimized.

Solution: Add primary key constraint in the table. For example:

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) 


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