Collations and character sets for Microsoft SQL Server - Amazon Relational Database Service

Collations and character sets for Microsoft SQL Server

SQL Server supports collations at multiple levels. You set the default server collation when you create the DB instance. You can override the collation in the database, table, or column level.

Server-level collation for Microsoft SQL Server

When you create a Microsoft SQL Server DB instance, you can set the server collation that you want to use. If you don't choose a different collation, the server-level collation defaults to SQL_Latin1_General_CP1_CI_AS. The server collation is applied by default to all databases and database objects.

Note

You can't change the collation when you restore from a DB snapshot.

Currently, Amazon RDS supports the following server collations:

Collation Description

Chinese_PRC_CI_AS

Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Chinese_Taiwan_Stroke_CI_AS

Chinese-Taiwan-Stroke, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Finnish_Swedish_CI_AS

Finnish, Swedish, and Swedish (Finland), case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

French_CI_AS

French, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Hebrew_BIN

Hebrew, binary sort

Japanese_CI_AS

Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Korean_Wansung_CI_AS

Korean-Wansung, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Latin1_General_100_BIN

Latin1-General-100, binary sort

Latin1_General_100_BIN2

Latin1-General-100, binary code point comparison sort

Latin1_General_100_CI_AS

Latin1-General-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Latin1_General_BIN

Latin1-General, binary sort

Latin1_General_CI_AI

Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive

Latin1_General_CI_AS

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Latin1_General_CS_AS

Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Modern_Spanish_CI_AS

Modern-Spanish, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

SQL_Latin1_General_CP1_CI_AI

Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 54 on Code Page 1252 for non-Unicode Data

SQL_Latin1_General_CP1_CI_AS (default)

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

SQL_Latin1_General_CP1_CS_AS

Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

SQL_Latin1_General_CP437_CI_AI

Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 34 on Code Page 437 for non-Unicode Data

SQL_Latin1_General_CP850_BIN2

Latin1-General, binary code point comparison sort for Unicode Data, SQL Server Sort Order 40 on Code Page 850 for non-Unicode Data

SQL_Latin1_General_CP850_CI_AS

Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 42 on Code Page 850 for non-Unicode Data

To choose the collation:

  • If you're using the Amazon RDS console, when creating a new DB instance choose Additional configuration, then choose the collation from the Collation menu under Database options. For more information, see Creating an Amazon RDS DB instance.

  • If you're using the AWS CLI, use the --character-set-name option with the create-db-instance command. For more information, see create-db-instance.

  • If you're using the Amazon RDS API, use the CharacterSetName parameter with the CreateDBInstance operation. For more information, see CreateDBInstance.

Database-level collation for Microsoft SQL Server

You can change the default collation at the database, table, or column level by overriding the collation when creating a new database or database object. For example, if your default server collation is SQL_Latin1_General_CP1_CI_AS, you can change it to Mohawk_100_CI_AS for Mohawk collation support. Even arguments in a query can be type-cast to use a different collation if necessary.

For example, the following query would change the default collation for the AccountName column to Mohawk_100_CI_AS

CREATE TABLE [dbo].[Account] ( [AccountID] [nvarchar](10) NOT NULL, [AccountName] [nvarchar](100) COLLATE Mohawk_100_CI_AS NOT NULL ) ON [PRIMARY];

The Microsoft SQL Server DB engine supports Unicode by the built-in NCHAR, NVARCHAR, and NTEXT data types. For example, if you need CJK support, use these Unicode data types for character storage and override the default server collation when creating your databases and tables. Here are several links from Microsoft covering collation and Unicode support for SQL Server: