为何MSSQL的CREATE DATABASE语句不支持指定CHARACTER SET？

Great question! This boils down to the fundamental differences in how MySQL and SQL Server (MSSQL) handle character encoding and collation rules. Let's break it down clearly:

• MSSQL binds character sets directly to collations
  Unlike MySQL, where CHARACTER SET and COLLATION are separate configurable options, SQL Server embeds character set information within the collation name itself. Every collation in MSSQL is tightly tied to a specific character set—you can't have a collation that exists independently of an encoding.

  For example:

  • Latin1_General_100_CI_AI_SC uses the Windows-1252 character set (often referenced as Latin1 in MSSQL)
  • UTF8_General_100_CI_AI_SC uses the UTF-8 character set
  • Chinese_PRC_90_CI_AS uses the GBK character set (for simplified Chinese)

  When you specify a collation during database creation, you're implicitly choosing the character set that the database will use.

• MySQL uses a decoupled design
  MySQL separates the storage encoding (CHARACTER SET) from the sorting/comparison rules (COLLATION). A single character set can have multiple collations (e.g., utf8mb4 supports utf8mb4_unicode_ci, utf8mb4_general_ci, etc.), so you need to specify either one or both to define the database's encoding behavior.

• How to confirm the character set in MSSQL
  If you want to verify which character set your MSSQL database is using, you can query the system catalogs with this snippet:

  SELECT 
      name AS DatabaseName,
      collation_name,
      code_page
  FROM sys.databases 
  WHERE name = 'YourDatabaseName';
  

  The code_page column maps directly to the character set:

  • 65001 = UTF-8
  • 1252 = Windows-1252
  • 936 = GBK (Chinese)

In short: MSSQL doesn't let you specify a CHARACTER SET because that information is already included in the collation you choose. It's a design choice that simplifies configuration compared to MySQL's more granular, split approach.

内容的提问来源于stack exchange，提问作者Shuzheng