Sep 22, 2013

The durability option 'schema_only' is supported only with memory optimized tables

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

CREATE TABLEtblEmp(
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(DURABILITY =SCHEMA_ONLY)

We may get error message something like:

Msg 10779, Level 15, State 1, Line 6
The durability option 'schema_only' is supported only with memory optimized tables.

Cause: Durability option schema_only means table will be deleted when server will be restared. This option is only applicable for memory optimized table.

Solution:
First solution:
Change Durability to SCHEMA_AND DATA. For example:

CREATE TABLEtblEmp(
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(DURABILITY =SCHEMA_AND_DATA)

Second solution: Make 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)  

No comments:

Post a Comment