Sep 26, 2013

The table type '' is not a memory optimized table type and cannot be used in a natively compiled stored procedure

If will use table type in the natively complied stored procedure in the sql server 2014:

CREATE TYPEtblEmp AS TABLE (
     ntEmpID BIGINT PRIMARY KEY,
     vcName VARCHAR(50),
     moSalary MONEY
)

Go
CREATE PROCEDUREuspGetEmp
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
     DECLARE @tblData ASdbo.tblEmp

     SELECT vcName,moSalary FROM @tblData

END


We may get following error message:

Msg 41323, Level 16, State 1, Procedure uspGetEmp, Line 39
The table type 'dbo.tblEmp' is not a memory optimized table type and cannot be used in a natively compiled stored procedure.

Cause: We can use normal table type in the natively complied stored procedures.

Solution: Create memory optimized table type. For example:

DROP TYPE tblEmp

CREATE TYPEtblEmp AS TABLE (
     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