If we will try to create memory optimized table or natively complied stored procedures. For examples:
CREATE TABLEtblEmp(
vcEmpID VARCHAR(100) NOT NULL PRIMARY KEY NONCLUSTERED HASH (vcEmpID) WITH (BUCKET_COUNT=1024),
vcName VARCHAR(50),
moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)
Or
CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
SELECT vcName FROMdbo.tblEmp
WHERE ntEmpID = 20
AND vcName = 'Scoll1'
END
We may get error message:
Msg 10772, Level 16, State 111, Procedure uspGetEmployee, Line 14
The feature 'non-bin2 collation' is not supported with natively compiled stored procedures.
The feature 'non-bin2 collation' is not supported with indexes on memory optimized tables
Cause: default collation in the database is not supported by memory optimized tables or natively compiled stored procedures.
Solutions: Use collation which is supported by memory optimized tables or procedures. For examples:
CREATE TABLEtblEmp(
vcEmpID VARCHAR(100) COLLATE Latin1_General_100_BIN2 NOT NULL PRIMARY KEY NONCLUSTERED HASH (vcEmpID) WITH (BUCKET_COUNT=1024),
vcName VARCHAR(50),
moSalary MONEY
)
WITH(MEMORY_OPTIMIZED =ON)
Or
CREATE PROCEDUREuspGetEmployee
WITH NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMICWITH(TRANSACTION ISOLATIONLEVEL = SNAPSHOT,LANGUAGE = N'us_english')
SELECT vcName FROMdbo.tblEmp
WHERE ntEmpID = 20
AND vcName = 'Scoll1' COLLATELatin1_General_100_BIN2
END
Or better to change the default collation of your database. For examples:
CREATE DATABASEMSSQL2014
COLLATE Latin1_General_100_BIN2
GO
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
No comments:
Post a Comment