Sep 24, 2013

The feature 'non-bin2 collation' is not supported with indexes on memory optimized tables

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