Sep 24, 2013

Memory optimized tables cannot be created in system databases

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

USE MASTER

CREATE TABLEtblEmployee(
     ntEmpID BIGINT NOT NULL PRIMARY KEY NONCLUSTERED HASH (ntEmpID)
     WITH (BUCKET_COUNT=1024),

     vcName VARCHAR(50),
     moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)

We may get error message something like:

Msg 41326, Level 16, State 1, Line 1
Memory optimized tables cannot be created in system databases.

Cause: Sql server 2012 has restriction that we cannot create memory optimized tables in the system databases (master, model,tempdb and msdb)

Solution: Create own memory optimized database and then create table in it. For example:

CREATE DATABASEMSSQL2014
GO

USE MSSQL2014

ALTER DATABASEMSSQL2014
ADD FILEGROUP  sample_database_filegroup CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASEMSSQL2014
ADD FILE
  (NAME = sample_database_filegroup_file,
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data\MemoryOptiDB_1')
TO FILEGROUPsample_database_filegroup

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