Suppose we have created table by following script in sql server 2014:
CREATE TABLEtblEmp(
ntEmpID BIGINT PRIMARY KEY,
vcName VARCHAR(50),
moSalary MONEY
)
Now we want to create a natively compiled stored procedure:
CREATE PROCEDUREuspGetEmpInfo
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
SELECT vcName,moSalary FROM dbo.tblEmp
END
Then we may get error message something like:
Msg 10775, Level 16, State 1, Procedure , Line 5
Object '' is not a memory optimized table and cannot be accessed from a natively compiled stored procedure.
Cause: In the natively compiled stored procedure we can only use memory optimized table.
Solution: Change the tblEmp to memory optimized table. For example:
Step 1: Drop the existing table:
DROP TABLE dbo.tblEmp
Step 2: Create memory optimized table
CREATE TABLEtblEmp(
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)
Now we can reference above table in the natively complied stored procedure.
No comments:
Post a Comment