Code Page Restrictions and Supported Collations in Memory-Optimized Tables

In-Memory OLTP released with some collation restrictions on supported code pages for varchar/char columns such as Memory-Optimized Tables, Indexes and Natively Stored Procedures. In general, column collation is inherited from the root (database collation). Database collation setting can not be changed if it contains In-Memory OLTP objects. Lets demonstrate an example;

Firstly, I will create a database which contains memory optimized data filegroup with an unsupperted code page.

Now, I can create a simple Memory-Optimized Table with an integer and varchar columns.

When we execute Memory-Optimized Table creation code, we faced with a serious error.

The data types char(n) and varchar(n) using a collation that has a code page other than 1252 are not supported with memory optimized tables.

That means VARCHAR and CHAR columns are in Memory-Optimized Tables must use code page 1252 collation! As you may already considered, you can try to change the collation of database to supported collation type. To do that you can use below T-SQL code.

Ok! Let’s try to change the collation of the database and then try to create Memory-Optimized Table again.

Command(s) completed successfully. Bingo! You can also try another way to do this operation without changing the database collation. You can use the COLLATE keyword to change the collation of VARCHAR columns. Let’s change the collation of the database to Turkish_CI_AS which is under the 1254 codepage to create a new Memory Optimized Table same as the last one. But in this table we will use COLLATE to change the collation of VARCHAR columns.

Ooops! We got a different error.

Modifying the collation of a database is not allowed when the database contains memory optimized tables or natively compiled stored procedures.

The error message is clearly straightforward. As it is indicated at the beginning of the article; “Database collation setting can not be changed if it contains In-Memory OLTP objects“. You should drop Memory-Optimized Tables under the database to be able to fix this issue. Now, drop the table and change the collation.

Now, we got Command(s) completed successfully message. Now, try to recreate our table with collating columns.

It worked! Memory-Optimized Table is created by using COLLATE keyword. We expect that the product group will remove some of these limitations in the future releases.

Posted In:
Post Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.