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.
|
|
USE master GO CREATE DATABASE IMODB1 ON PRIMARY (NAME = [IMODB1_data], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB1_data.mdf', SIZE = 500MB), FILEGROUP [IMODB1_fg] CONTAINS MEMORY_OPTIMIZED_DATA (NAME = [IMODB1_mod], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB1_mod') LOG ON (NAME = [IMODB1_log], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB1_log.ldf', SIZE = 500MB) COLLATE Turkish_CI_AS GO |
Now, I can create a simple Memory-Optimized Table with an integer and varchar columns.
|
|
USE IMODB1 GO CREATE TABLE imo_Address ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), Address_Line1 VARCHAR(100) NOT NULL, Address_Line2 VARCHAR(100) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON) GO |
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.
|
|
SELECT name AS [Collation], COLLATIONPROPERTY(name, 'codepage') AS [CodePage] FROM sys.fn_helpcollations() WHERE COLLATIONPROPERTY(name, 'codepage') = 1252 |
Ok! Let’s try to change the collation of the database and then try to create Memory-Optimized Table again.
|
|
USE master GO ALTER DATABASE IMODB1 COLLATE Latin1_General_100_CS_AS GO USE IMODB1 GO CREATE TABLE imo_Address ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), Address_Line1 VARCHAR(100) NOT NULL, Address_Line2 VARCHAR(100) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON) GO |
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.
|
|
USE master GO ALTER DATABASE IMODB1 COLLATE Turkish_CI_AS GO |
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.
|
|
USE IMODB1 GO DROP TABLE imo_Address GO USE master GO ALTER DATABASE IMODB1 COLLATE Turkish_CI_AS GO |
Now, we got Command(s) completed successfully message. Now, try to recreate our table with collating columns.
|
|
USE IMODB1 GO CREATE TABLE imo_Address ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), Address_Line1 VARCHAR(100) COLLATE Latin1_General_100_CI_AS NOT NULL, Address_Line2 VARCHAR(100) COLLATE Latin1_General_100_CI_AS NOT NULL ) WITH (MEMORY_OPTIMIZED = ON) GO |
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.
Tags:
Posted On: 26/04/2014
Post Details
