Collation supported by memory-optimized tables

In SQL Server, Collation determines the byte representation and code Page of a varchar or char field. In a memory-optimized table, the column of the character type (Varchar,char,nvarchar,nchar) must use the collation of code page=1252, if memory-optimized index Created on a column of character type, the column can only use collation ending with BIN2.

Limit 1, create memory-optimized index on character columns (Unicode and Non-nicode), then the character column must use BIN2 collation.

Collation:bosnian_latin_100_bin2 's code Page is 1520 and is used for the LastName varchar () times Error: thedata Types char (n) and varchar (n) using a collation that have a code page other than 1252 is not supported with memory optimize D tables.

--Create a table with collationCREATE TABLEdbo. Employees (EmployeeIDint  not NULL, LastNamevarchar( -) COLLATE bosnian_latin_100_bin2 not NULL INDEXIx_lastnamenonclustered, FirstNamenvarchar(Ten) not NULL ,  CONSTRAINTPk__employeesPRIMARY KEY nonclusteredHASH (EmployeeID) with(Bucket_count=1024x768))  with(memory_optimized= on, durability=schema_and_data)GO

In a memory-optimized table, to create an index on the non-unicode character column, you must satisfy two conditions: Collation Name ends with BIN2, the Code page is 1252, and index is created on the Unicode character column. Only one condition needs to be met: Collation Name ends with BIN2.

Indexes on (n) (Var) char columns can is specified with BIN2 collations

 --  all supported collations for indexes on memory-optimized tables and   Comparison/sorting in natively compiled stored procedures  select  *  from   Sys.fn_helpcollations ()  where  name like    %bin2        and  collationproperty (name,  codepage  ) =  1252 ; 

Limit 2, in memory-optimized tables, the code page of the Non-unicode character column must be 1252. If you need to store characters that are not 1252 of the code page, you must use a Unicode data type.

(Var) char columns in memory-optimized tables must use code page 1252 collation. This restriction does is not an apply to N (Var) char columns. If you need to the store non-latin characters, use N (Var) char columns.

-- All supported collations for (VAR) char columns in memory-optimized tables Select *
 from sys.fn_helpcollations () where ' codepage ' = 1252;

citing the Collations and Code Pages:

In-memory OLTP have restrictions on supported code pages for (Var) char columns in memory-optimized tables and supported Col Lations used in indexes and natively compiled stored procedures.

The code page for a (Var) char value determines the mapping between characters and the byte representation it is stored I n the table. For example, with the Windows Latin 1 code page (1252; the SQL Server default), the character ' a ' corresponds to the byte 0x61.

The code page of a (Var) char value is determined by the collation associated with the value. For example, the collation SQL_Latin1_General_CP1_CI_AS have the associated code page 1252.

The collation of a value is either inherited from the database collation, or can be specified explicitly using the COLLATE Keyword. Database collation cannot be changed if the database contains memory-optimized tables or natively compiled stored Procedur Es. The following example sets the database collation and creates a table, which have a column with a different collation. The database uses Latin case-insensitive collation.

Indexes can only is created on string columns if they use a BIN2 collation. The LastName variable uses BIN2 collation. FirstName uses the database default, which is Ci_as (case-insensitive, accent-sensitive).

Example: Create a memory-optimized table, use the COLLATE clause to explicitly specify the collation of the character column, and then create a memory-optimized index on the character columns.

 UseMasterGo --CREATE DATABASECreate DatabaseTestmemorydb;--Add FilegroupAlter DatabaseTestmemorydbAddFileGroup Fg_testmemorydbcontainsMemory_optimized_data;--Add FileAlter DatabaseTestmemorydbAdd file(Name='testmemorydbdirectory', filename='D:\MSSQLServerData\MSSQL12. Mssqlserver\mssql\data\testmemorydbdirectory') toFileGroup Fg_testmemorydb;--Set the database collationsALTER DATABASEtestmemorydb COLLATE latin1_general_100_ci_asGO UseTestmemorydbGO--Create a table with collationCREATE TABLEdbo. Employees (EmployeeIDint  not NULL, LastNamevarchar( -) COLLATE latin1_general_100_bin2 not NULL INDEXIx_lastnamenonclustered, FirstNamenvarchar(Ten) not NULL ,  CONSTRAINTPk__employeesPRIMARY KEY nonclusteredHASH (EmployeeID) with(Bucket_count=1024x768))  with(memory_optimized= on, durability=schema_and_data)GO

Reference doc:

Collations and Code Pages

Collation supported by memory-optimized tables