排序规则和 Unicode 支持Collation and Unicode support

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

SQL ServerSQL Server 中的排序规则可为您的数据提供排序规则、区分大小写属性和区分重音属性。Collations in SQL ServerSQL Server provide sorting rules, case, and accent sensitivity properties for your data. 与诸如 char 和 varchar 等字符数据类型一起使用的排序规则规定可表示该数据类型的代码页和对应字符 。Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.

无论你是要安装 SQL ServerSQL Server 的新实例、还原数据库备份,还是将服务器连接到客户端数据库,都必须了解正在处理的数据的区域设置要求、排序顺序以及是否区分大小写和重音。Whether you're installing a new instance of SQL ServerSQL Server, restoring a database backup, or connecting server to client databases, it's important to understand the locale requirements, sorting order, and case and accent sensitivity of the data that you're working with. 若要列出在 SQL ServerSQL Server的实例上可用的排序规则,请参阅 sys.fn_helpcollations (Transact-SQL)To list the collations that are available on your instance of SQL ServerSQL Server, see sys.fn_helpcollations (Transact-SQL).

为服务器、数据库、列或表达式选择排序规则时,同时也是在向数据分配某些特征。When you select a collation for your server, database, column, or expression, you're assigning certain characteristics to your data. 这些特征会影响数据库中许多操作的结果。These characteristics affect the results of many operations in the database. 例如,使用 ORDER BY 构造查询时,结果集的排序顺序可能取决于应用于该数据库的排序规则或 COLLATE 子句中在查询的表达式级别规定的排序规则。For example, when you construct a query by using ORDER BY, the sort order of your result set might depend on the collation that's applied to the database or dictated in a COLLATE clause at the expression level of the query.

为了充分利用 SQL ServerSQL Server中的排序规则支持,应了解本主题中所定义的术语以及这些术语与数据的特征之间的关系。To best use collation support in SQL ServerSQL Server, you should understand the terms that are defined in this topic and how they relate to the characteristics of your data.

排序规则术语Collation terms

排序规则Collation

排序规则指定表示数据集中每个字符的位模式。A collation specifies the bit patterns that represent each character in a dataset. 排序规则还确定数据的排序和比较规则。Collations also determine the rules that sort and compare data. SQL ServerSQL Server 支持在单个数据库中存储具有不同排序规则的对象。supports storing objects that have different collations in a single database. 对于非 Unicode 列,排序规则设置指定数据的代码页以及可以表示哪些字符。For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. 必须将在非 Unicode 列间移动的数据从源代码页转换到目标代码页。The data that you move between non-Unicode columns must be converted from the source code page to the destination code page.

Transact-SQLTransact-SQL 语句在具有不同排序规则设置的不同数据库上下文中运行时,其运行结果可能会不同。Transact-SQLTransact-SQL statement results can vary when the statement is run in the context of different databases that have different collation settings. 如果可能,请为组织使用标准化排序规则。If possible, use a standardized collation for your organization. 这样就不必显式指定每个字符或 Unicode 表达式中的排序规则。This way, you don't have to specify the collation in every character or Unicode expression. 如果必须使用具有不同排序规则和代码页设置的对象,请对查询进行编码,以考虑排序规则的优先顺序规则。If you must work with objects that have different collation and code page settings, code your queries to consider the rules of collation precedence. 有关详细信息,请参阅 排序规则优先顺序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).

与排序规则关联的选项区分大小写、区分重音、区分假名、区分全半角以及区分变体选择符。The options associated with a collation are case sensitivity, accent sensitivity, kana sensitivity, width sensitivity, and variation-selector sensitivity. SQL Server 2019 (15.x)SQL Server 2019 (15.x) UTF-8 编码引入了其他选项。introduces an additional option for UTF-8 encoding.

可以通过将这些选项附加到排序规则名称中来指定这些选项。You can specify these options by appending them to the collation name. 例如,排序规则 Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_UTF8 区分大小写、区分重音、区分假名、区分全半角以及使用 UTF-8 编码。For example, the collation Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_UTF8 is case-sensitive, accent-sensitive, kana-sensitive, width-sensitive, and UTF-8 encoded. 再举一例,此排序规则 Japanese_Bushu_Kakusu_140_CI_AI_KS_WS_VSS 就不区分大小写、不区分重音、区分假名、区分全半角、区分变体选择符,并且使用非 Unicode 编码。As another example, the collation Japanese_Bushu_Kakusu_140_CI_AI_KS_WS_VSS is case-insensitive, accent-insensitive, kana-sensitive, width-sensitive, variation-selector-sensitive, and it uses non-Unicode encoding.

下表描述了与这些不同选项关联的行为:The behavior associated with these various options is described in the following table:

选项Option 说明Description
区分大小写 (_CS)Case-sensitive (_CS) 区分大写字母和小写字母。Distinguishes between uppercase and lowercase letters. 如果选择此项,排序时小写字母将在其对应的大写字母之前。If this option is selected, lowercase letters sort ahead of their uppercase versions. 如果未选择此选项,排序规则将不区分大小写。If this option isn't selected, the collation is case-insensitive. SQL ServerSQL Server 在排序时将大写字母和小写字母视为相同。That is, SQL ServerSQL Server considers the uppercase and lowercase versions of letters to be identical for sorting purposes. 通过指定 _CI,可以显式选择不区分大小写。You can explicitly select case insensitivity by specifying _CI.
区分重音 (_AS)Accent-sensitive (_AS) 区分重音字符和非重音字符。Distinguishes between accented and unaccented characters. 例如,“a”和“ấ”视为不同字符。For example, "a" is not equal to "ấ". 如果未选择此选项,则排序规则将不区分重音。If this option isn't selected, the collation is accent-insensitive. SQL ServerSQL Server 在排序时将字母的重音形式和非重音形式视为相同。That is, SQL ServerSQL Server considers the accented and unaccented versions of letters to be identical for sorting purposes. 通过指定 _AI,可以显式选择不区分重音。You can explicitly select accent insensitivity by specifying _AI.
区分假名 (_KS)Kana-sensitive (_KS) 区分日语中的两种假名字符类型:平假名和片假名。Distinguishes between the two types of Japanese kana characters: Hiragana and Katakana. 如果未选择此选项,则排序规则将不区分假名。If this option isn't selected, the collation is kana-insensitive. SQL ServerSQL Server 在排序时将平假名字符和片假名字符视为相同。That is, SQL ServerSQL Server considers Hiragana and Katakana characters to be equal for sorting purposes. 省略此选项是指定不区分假名的唯一方法。Omitting this option is the only method of specifying kana-insensitivity.
区分全半角 (_WS)Width-sensitive (_WS) 区分全角字符和半角字符。Distinguishes between full-width and half-width characters. 如果未选择此选项,SQL ServerSQL Server 会在排序时将同一字符的全角和半角形式视为相同。If this option isn't selected, SQL ServerSQL Server considers the full-width and half-width representation of the same character to be identical for sorting purposes. 省略此选项是指定不区分全半角的唯一方法。Omitting this option is the only method of specifying width-insensitivity.
区分变体选择符 (_VSS)Variation-selector-sensitive (_VSS) 区分 SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中首次引入的日语排序规则 Japanese_Bushu_Kakusu_140 和 Japanese_XJIS_140 中不同的象形变体选择符 。Distinguishes between various ideographic variation selectors in the Japanese collations Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140, which are introduced in SQL Server 2017 (14.x)SQL Server 2017 (14.x). 变体序列包含基本字符加上其他变体选择符。A variation sequence consists of a base character plus an additional variation selector. 如果未选择 _VSS 选项,排序规则不区分变体选择符,并且在比较中不考虑变体选择符。If this _VSS option isn't selected, the collation is variation-selector-insensitive, and the variation selector isn't considered in the comparison. 也就是说,出于排序的考虑,SQL ServerSQL Server 会将具有不同变体选择符但基于相同基本字符的字符视为相同。That is, SQL ServerSQL Server considers characters built upon the same base character with differing variation selectors to be identical for sorting purposes. 有关详细信息,请参阅 Unicode Ideographic Variation Database(Unicode 象形变体数据库)。For more information, see Unicode Ideographic Variation Database.

全文搜索索引中不支持区分变体选择符 (_VSS) 排序规则。Variation-selector-sensitive (_VSS) collations aren't supported in full-text search indexes. 全文搜索索引仅支持区分重音 (_AS)、区分假名 (_KS) 和区分全半角 (_WS) 选项。Full-text search indexes support only Accent-Sensitive (_AS), Kana-sensitive (_KS), and Width-sensitive (_WS) options. SQL ServerSQL Server XML 和 CLR 引擎不支持 (_VSS) 变体选择符。XML and CLR engines don't support (_VSS) Variation selectors.
二进制 (_BIN) 1Binary (_BIN)1 根据为每个字符定义的位模式对 SQL ServerSQL Server 表中的数据进行排序和比较。Sorts and compares data in SQL ServerSQL Server tables based on the bit patterns defined for each character. 二进制排序顺序不仅区分大小写,而且也区分重音。Binary sort order is case-sensitive and accent-sensitive. 二进制排序顺序的速度也最快。Binary is also the fastest sorting order. 有关详细信息,请参阅本文中的二进制排序规则部分。For more information, see the Binary collations section in this article.
二进制-码位 (_BIN2) 1Binary-code point (_BIN2)1 根据 Unicode 数据的 Unicode 码位对 SQL ServerSQL Server 表中的数据进行排序和比较。Sorts and compares data in SQL ServerSQL Server tables based on Unicode code points for Unicode data. 对于非 Unicode 数据,二进制码位将使用与二进制排序相同的比较方式。For non-Unicode data, Binary-code point uses comparisons that are identical to those for binary sorts.

使用二进制-码位排序顺序的优点是:对已排序的 SQL ServerSQL Server 数据进行比较的应用程序不必重新对数据进行排序。The advantage of using a Binary-code point sort order is that no data resorting is required in applications that compare sorted SQL ServerSQL Server data. 因此,二进制-码位排序顺序使应用程序开发变得更加简单,从而可以提高性能。As a result, a Binary-code point sort order provides simpler application development and possible performance increases. 有关详细信息,请参阅本文中的二进制排序规则部分。For more information, see the Binary collations section in this article.
UTF-8 (_UTF8)UTF-8 (_UTF8) 启用要在 SQL ServerSQL Server 中存储的 UTF-8 编码数据。Enables UTF-8 encoded data to be stored in SQL ServerSQL Server. 如果未选择此选项,SQL ServerSQL Server 会对适用的数据类型使用默认的非 Unicode 编码格式。If this option isn't selected, SQL ServerSQL Server uses the default non-Unicode encoding format for the applicable data types. 有关详细信息,请参阅本文中的 UTF-8 支持部分。For more information, see the UTF-8 Support section in this article.

1 如果选择二进制或二进制-码位,则区分大小写 (_CS)、区分重音 (_AS)、区分假名 (_KS) 和区分全半角 (_WS) 选项均不可用。1 If Binary or Binary-code point is selected, the Case-sensitive (_CS), Accent-sensitive (_AS), Kana-sensitive (_KS), and Width-sensitive (_WS) options are not available.

排序规则选项示例Examples of collation options

每个排序规则都由一系列定义大小写、重音、全半角或假名的后缀组合而成。Each collation is combined as a series of suffixes to define case-, accent-, width-, or kana-sensitivity. 下列示例说明了各种后缀组合的排序顺序行为。The following examples describe sort order behavior for various combinations of suffixes.

Windows 排序规则后缀Windows collation suffix 排序顺序说明Sort order description
_BIN1_BIN1 二进制排序Binary sort
_BIN21, 2_BIN21, 2 二进制码位排序顺序Binary-code point sort order
_CI_AI2_CI_AI2 不区分大小写、不区分重音、不区分假名、不区分全半角Case-insensitive, accent-insensitive, kana-insensitive, width-insensitive
_CI_AI_KS2_CI_AI_KS2 不区分大小写、不区分重音、区分假名、不区分全半角Case-insensitive, accent-insensitive, kana-sensitive, width-insensitive
_CI_AI_KS_WS2_CI_AI_KS_WS2 不区分大小写、不区分重音、区分假名、区分全半角Case-insensitive, accent-insensitive, kana-sensitive, width-sensitive
_CI_AI_WS2_CI_AI_WS2 不区分大小写、不区分重音、不区分假名、区分全半角Case-insensitive, accent-insensitive, kana-insensitive, width-sensitive
_CI_AS2_CI_AS2 不区分大小写、区分重音、不区分假名、不区分全半角Case-insensitive, accent-sensitive, kana-insensitive, width-insensitive
_CI_AS_KS2_CI_AS_KS2 不区分大小写、区分重音、区分假名、不区分全半角Case-insensitive, accent-sensitive, kana-sensitive, width-insensitive
_CI_AS_KS_WS2_CI_AS_KS_WS2 不区分大小写、区分重音、区分假名、区分全半角Case-insensitive, accent-sensitive, kana-sensitive, width-sensitive
_CI_AS_WS2_CI_AS_WS2 不区分大小写、区分重音、不区分假名、区分全半角Case-insensitive, accent-sensitive, kana-insensitive, width-sensitive
_CS_AI2_CS_AI2 区分大小写、不区分重音、不区分假名、不区分全半角Case-sensitive, accent-insensitive, kana-insensitive, width-insensitive
_CS_AI_KS2_CS_AI_KS2 区分大小写、不区分重音、区分假名、不区分全半角Case-sensitive, accent-insensitive, kana-sensitive, width-insensitive
_CS_AI_KS_WS2_CS_AI_KS_WS2 区分大小写、不区分重音、区分假名、区分全半角Case-sensitive, accent-insensitive, kana-sensitive, width-sensitive
_CS_AI_WS2_CS_AI_WS2 区分大小写、不区分重音、不区分假名、区分全半角Case-sensitive, accent-insensitive, kana-insensitive, width-sensitive
_CS_AS2_CS_AS2 区分大小写、区分重音、不区分假名、不区分全半角Case-sensitive, accent-sensitive, kana-insensitive, width-insensitive
_CS_AS_KS2_CS_AS_KS2 区分大小写、区分重音、区分假名、不区分全半角Case-sensitive, accent-sensitive, kana-sensitive, width-insensitive
_CS_AS_KS_WS2_CS_AS_KS_WS2 区分大小写、区分重音、区分假名、区分全半角Case-sensitive, accent-sensitive, kana-sensitive, width-sensitive
_CS_AS_WS2_CS_AS_WS2 区分大小写、区分重音、不区分假名、区分全半角Case-sensitive, accent-sensitive, kana-insensitive, width-sensitive

1 如果选择二进制或二进制-码位,则区分大小写 (_CS)、区分重音 (_AS)、区分假名 (_KS) 和区分全半角 (_WS) 选项均不可用。1 If Binary or Binary-code point is selected, the Case-sensitive (_CS), Accent-sensitive (_AS), Kana-sensitive (_KS), and Width-sensitive (_WS) options aren't available.

2 如果添加 UTF-8 选项 (_UTF8),可使用 UTF-8 对 Unicode 数据进行编码。2 Adding the UTF-8 option (_UTF8) enables you to encode Unicode data by using UTF-8. 有关详细信息,请参阅本文中的 UTF-8 支持部分。For more information, see the UTF-8 Support section in this article.

排序规则集Collation sets

SQL ServerSQL Server 支持以下排序规则集:supports the following collation sets:

Windows 排序规则Windows collations

Windows 排序规则根据关联的 Windows 系统区域设置来定义字符数据的存储规则。Windows collations define rules for storing character data that's based on an associated Windows system locale. 在 Windows 排序规则中,可以使用与 Unicode 数据相同的算法实现非 Unicode 数据的比较。For a Windows collation, you can implement a comparison of non-Unicode data by using the same algorithm as that for Unicode data. Windows 基本排序规则指定应用字典排序时所用的字母表或语言。The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied. 规则还指定用于存储非 Unicode 字符数据的代码页。The rules also specify the code page that's used to store non-Unicode character data. Unicode 排序和非 Unicode 排序都与特定 Windows 版本中的字符串比较相兼容。Both Unicode and non-Unicode sorting are compatible with string comparisons in a particular version of Windows. 这保证了 SQL ServerSQL Server 中所有数据类型的一致性,使开发人员能够使用 SQL ServerSQL Server 所使用的相同规则对应用程序中的字符串排序。This provides consistency across data types within SQL ServerSQL Server, and it lets developers sort strings in their applications by using the same rules that are used by SQL ServerSQL Server. 有关详细信息,请参阅 Windows 排序规则名称 (Transact-SQL)For more information, see Windows Collation Name (Transact-SQL).

二进制排序规则Binary collations

二进制排序规则基于区域设置和数据类型定义的编码值顺序来对数据进行排序。Binary collations sort data based on the sequence of coded values that are defined by the locale and data type. 它们区分大小写。They're case-sensitive. SQL ServerSQL Server 中的二进制排序规则定义了所使用的区域设置和 ANSI 代码页。A binary collation in SQL ServerSQL Server defines the locale and the ANSI code page that's used. 这将强制使用二进制排序顺序。This enforces a binary sort order. 由于它们相对简单,因此二进制排序规则有助于提高应用程序性能。Because they're relatively simple, binary collations help improve application performance. 对于非 Unicode 数据类型,数据比较将基于 ANSI 代码页中定义的码位。For non-Unicode data types, data comparisons are based on the code points that are defined on the ANSI code page. 对于 Unicode 数据类型,数据比较将基于 Unicode 码位。For Unicode data types, data comparisons are based on the Unicode code points. 对于 Unicode 数据类型的二进制排序规则,数据排序将不考虑区域设置。For binary collations on Unicode data types, the locale isn't considered in data sorts. 例如,对 Unicode 数据应用 Latin_1_General_BIN 和 Japanese_BIN,会得到完全相同的排序结果 。For example, Latin_1_General_BIN and Japanese_BIN yield identical sorting results when they're used on Unicode data. 有关详细信息,请参阅 Windows 排序规则名称 (Transact-SQL)For more information, see Windows Collation Name (Transact-SQL).

SQL ServerSQL Server 中有两种类型的二进制排序规则:There are two types of binary collations in SQL ServerSQL Server:

  • 早期的 BIN 排序规则对 Unicode 数据执行的是不完整的逐码位比较。The legacy BIN collations, which performed an incomplete code-point-to-code-point comparison for Unicode data. 这些早期的二进制排序规则将第一个字符作为 WCHAR 比较,接下来是逐字节比较。These legacy binary collations compared the first character as WCHAR, followed by a byte-by-byte comparison. 在 BIN 排序规则中,仅首字符按照码位排序,其余字符根据其字节值排序。In a BIN collation, only the first character is sorted according to the code point, and remaining characters are sorted according to their byte values.

  • 更新的 BIN2 排序规则可实现纯码位比较。The newer BIN2 collations, which implement a pure code-point comparison. 在 BIN2 排序规则中,所有字符根据其码位排序。In a BIN2 collation, all characters are sorted according to their code points. 由于 Intel 平台是一个 little endian 体系结构,因此 Unicode 码字符始终以字节对调的形式存储。Because the Intel platform is a little endian architecture, Unicode code characters are always stored byte-swapped.

SQL Server 排序规则SQL Server collations

SQL ServerSQL Server 排序规则 (SQL_*) 提供与 SQL ServerSQL Server 早期版本兼容的排序顺序。collations (SQL_*) provide sort order compatibility with earlier versions of SQL ServerSQL Server. 非 Unicode 数据的字典排序规则与 Windows 操作系统提供的任何排序例程都不兼容。The dictionary sorting rules for non-Unicode data are incompatible with any sorting routine that's provided by Windows operating systems. 但是,Unicode 数据的排序与特定版本的 Windows 排序规则兼容。However, sorting Unicode data is compatible with a particular version of Windows sorting rules. 由于 SQL ServerSQL Server 排序规则对非 Unicode 数据和 Unicode 数据使用不同的比较规则,因此对于相同数据的比较会看到不同的结果,具体取决于基本数据类型。Because SQL ServerSQL Server collations use different comparison rules for non-Unicode and Unicode data, you see different results for comparisons of the same data, depending on the underlying data type. 有关详细信息,请参阅 SQL Server 排序规则名称 (Transact-SQL)For more information, see SQL Server Collation Name (Transact-SQL).

SQL ServerSQL Server 安装过程中,默认安装排序规则设置由操作系统 (OS) 区域设置确定。During SQL ServerSQL Server setup, the default installation collation setting is determined by the operating system (OS) locale. 服务器级排序规则可以在安装期间进行更改,也可以在安装前通过更改 OS 区域设置进行更改。You can change the server-level collation either during setup or by changing the OS locale before installation. 出于后向兼容性原因,默认排序规则设置为与每个特定区域设置关联的最早可用版本。For backward compatibility reasons, the default collation is set to the oldest available version that's associated with each specific locale. 因此,不推荐总是使用默认排序规则。Therefore, this isn't always the recommended collation. 更改 Windows 排序规则的默认安装设置可充分利用 SQL ServerSQL Server 功能。To take full advantage of SQL ServerSQL Server features, change the default installation settings to use Windows collations. 例如,对于 OS 区域设置“英语(美国)”(代码页 1252),安装过程中的默认排序规则是 SQL_Latin1_General_CP1_CI_AS,可将其更改为最接近的 Windows 对等排序规则 Latin1_General_100_CI_AS_SC 。For example, for the OS locale "English (United States)" (code page 1252), the default collation during setup is SQL_Latin1_General_CP1_CI_AS, and it can be changed to its closest Windows collation counterpart, Latin1_General_100_CI_AS_SC.

备注

升级 SQL ServerSQL Server 的英文实例时可以指定 SQL ServerSQL Server 排序规则 (SQL_*),以便与现有 SQL ServerSQL Server 实例兼容。When you upgrade an English-language instance of SQL ServerSQL Server, you can specify SQL ServerSQL Server collations (SQL_*) for compatibility with existing instances of SQL ServerSQL Server. 由于 SQL ServerSQL Server 实例的默认排序规则是在安装过程中定义的,因此在以下情况下请确保慎重指定排序规则设置:Because the default collation for an instance of SQL ServerSQL Server is defined during setup, make sure that you specify the collation settings carefully when the following conditions are true:

  • 应用程序代码依赖早期 SQL ServerSQL Server 排序规则的行为。Your application code depends on the behavior of previous SQL ServerSQL Server collations.
  • 必须存储反映多种语言的字符数据。You must store character data that reflects multiple languages.

排序规则级别Collation levels

支持在 SQL ServerSQL Server实例的下列级别设置排序规则:Setting collations are supported at the following levels of an instance of SQL ServerSQL Server:

服务器级排序规则Server-level collations

默认服务器排序规则是在 SQL ServerSQL Server 安装过程中设置的,它将成为系统数据库和所有用户数据库的默认排序规则。The default server collation is determined during SQL ServerSQL Server setup, and it becomes the default collation of the system databases and all user databases.

下表显示由操作系统 (OS) 区域设置确定的默认排序规则标识,其中包括它们的 Windows 和 SQL 语言代码标识符 (LCID):The following table shows the default collation designations, as determined by the operating system (OS) locale, including their Windows and SQL Language Code Identifiers (LCID):

Windows 区域设置Windows locale Windows LCIDWindows LCID SQL LCIDSQL LCID 默认排序规则Default collation
南非荷兰语(南非)Afrikaans (South Africa) 0x04360x0436 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
阿尔巴尼亚语(阿尔巴尼亚)Albanian (Albania) 0x041c0x041c 0x041c0x041c Albanian_CI_ASAlbanian_CI_AS
阿尔萨斯语(法国)Alsatian (France) 0x04840x0484 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
阿姆哈拉语(埃塞俄比亚)Amharic (Ethiopia) 0x045e0x045e 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
阿拉伯语(阿尔及利亚)Arabic (Algeria) 0x14010x1401 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(巴林)Arabic (Bahrain) 0x3c010x3c01 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(埃及)Arabic (Egypt) 0x0c010x0c01 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(伊拉克)Arabic (Iraq) 0x08010x0801 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(约旦)Arabic (Jordan) 0x2c010x2c01 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(科威特)Arabic (Kuwait) 0x34010x3401 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(黎巴嫩)Arabic (Lebanon) 0x30010x3001 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(利比亚)Arabic (Libya) 0x10010x1001 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(摩洛哥)Arabic (Morocco) 0x18010x1801 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(阿曼)Arabic (Oman) 0x20010x2001 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(卡塔尔)Arabic (Qatar) 0x40010x4001 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(沙特阿拉伯)Arabic (Saudi Arabia) 0x04010x0401 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(叙利亚)Arabic (Syria) 0x28010x2801 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(突尼斯)Arabic (Tunisia) 0x1c010x1c01 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(阿拉伯联合酋长国)Arabic (U.A.E.) 0x38010x3801 0x04010x0401 Arabic_CI_ASArabic_CI_AS
阿拉伯语(也门)Arabic (Yemen) 0x24010x2401 0x04010x0401 Arabic_CI_ASArabic_CI_AS
亚美尼亚语(亚美尼亚)Armenian (Armenia) 0x042b0x042b 0x04190x0419 Latin1_General_CI_ASLatin1_General_CI_AS
阿萨姆语(印度)Assamese (India) 0x044d0x044d 0x044d0x044d 在服务器级别不可用Not available at server level
阿塞拜疆语(阿塞拜疆,西里尔文)Azerbaijani (Azerbaijan, Cyrillic) 0x082c0x082c 0x082c0x082c 不推荐使用,在服务器级别不可用Deprecated, not available at server level
阿塞拜疆语(阿塞拜疆,拉丁语)Azerbaijani (Azerbaijan, Latin) 0x042c0x042c 0x042c0x042c 不推荐使用,在服务器级别不可用Deprecated, not available at server level
巴什基尔语(俄罗斯)Bashkir (Russia) 0x046d0x046d 0x046d0x046d Latin1_General_CI_AILatin1_General_CI_AI
巴斯克语(巴斯克)Basque (Basque) 0x042d0x042d 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
白俄罗斯语(白俄罗斯)Belarusian (Belarus) 0x04230x0423 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
孟加拉语(孟加拉国)Bangla (Bangladesh) 0x08450x0845 0x04450x0445 在服务器级别不可用Not available at server level
孟加拉语(印度)Bengali (India) 0x04450x0445 0x04390x0439 在服务器级别不可用Not available at server level
波斯尼亚语(波斯尼亚和黑塞哥维那,西里尔文)Bosnian (Bosnia and Herzegovina, Cyrillic) 0x201a0x201a 0x201a0x201a Latin1_General_CI_AILatin1_General_CI_AI
波斯尼亚语(波斯尼亚和黑塞哥维那,拉丁语)Bosnian (Bosnia and Herzegovina, Latin) 0x141a0x141a 0x141a0x141a Latin1_General_CI_AILatin1_General_CI_AI
布列塔尼语(法国)Breton (France) 0x047e0x047e 0x047e0x047e Latin1_General_CI_AILatin1_General_CI_AI
保加利亚语(保加利亚)Bulgarian (Bulgaria) 0x04020x0402 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
加泰罗尼亚语(加泰罗尼亚)Catalan (Catalan) 0x04030x0403 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
中文(香港特别行政区)Chinese (Hong Kong SAR, PRC) 0x0c040x0c04 0x04040x0404 Chinese_Taiwan_Stroke_CI_ASChinese_Taiwan_Stroke_CI_AS
中文(澳门特别行政区)Chinese (Macao SAR) 0x14040x1404 0x14040x1404 Latin1_General_CI_AILatin1_General_CI_AI
中文(澳门特别行政区)Chinese (Macau) 0x214040x21404 0x214040x21404 Latin1_General_CI_AILatin1_General_CI_AI
中文(中华人民共和国)Chinese (PRC) 0x08040x0804 0x08040x0804 Chinese_PRC_CI_ASChinese_PRC_CI_AS
中文(中华人民共和国)Chinese (PRC) 0x208040x20804 0x208040x20804 Chinese_PRC_Stroke_CI_ASChinese_PRC_Stroke_CI_AS
中文(新加坡)Chinese (Singapore) 0x10040x1004 0x08040x0804 Chinese_PRC_CI_ASChinese_PRC_CI_AS
中文(新加坡)Chinese (Singapore) 0x210040x21004 0x208040x20804 Chinese_PRC_Stroke_CI_ASChinese_PRC_Stroke_CI_AS
中文(台湾)Chinese (Taiwan) 0x304040x30404 0x304040x30404 Chinese_Taiwan_Bopomofo_CI_ASChinese_Taiwan_Bopomofo_CI_AS
中文(台湾)Chinese (Taiwan) 0x04040x0404 0x04040x0404 Chinese_Taiwan_Stroke_CI_ASChinese_Taiwan_Stroke_CI_AS
科西嘉语(法国)Corsican (France) 0x04830x0483 0x04830x0483 Latin1_General_CI_AILatin1_General_CI_AI
克罗地亚语(波斯尼亚和黑塞哥维那,拉丁语)Croatian (Bosnia and Herzegovina, Latin) 0x101a0x101a 0x041a0x041a Croatian_CI_ASCroatian_CI_AS
克罗地亚语(克罗地亚)Croatian (Croatia) 0x041a0x041a 0x041a0x041a Croatian_CI_ASCroatian_CI_AS
捷克语(捷克共和国)Czech (Czech Republic) 0x04050x0405 0x04050x0405 Czech_CI_ASCzech_CI_AS
丹麦语(丹麦)Danish (Denmark) 0x04060x0406 0x04060x0406 Danish_Norwegian_CI_ASDanish_Norwegian_CI_AS
达里语(阿富汗)Dari (Afghanistan) 0x048c0x048c 0x048c0x048c Latin1_General_CI_AILatin1_General_CI_AI
迪维希语(马尔代夫)Divehi (Maldives) 0x04650x0465 0x04650x0465 在服务器级别不可用Not available at server level
荷兰语(比利时)Dutch (Belgium) 0x08130x0813 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
荷兰语(荷兰)Dutch (Netherlands) 0x04130x0413 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(澳大利亚)English (Australia) 0x0c090x0c09 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(伯利兹)English (Belize) 0x28090x2809 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(加拿大)English (Canada) 0x10090x1009 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(加勒比海)English (Caribbean) 0x24090x2409 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(印度)English (India) 0x40090x4009 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(爱尔兰)English (Ireland) 0x18090x1809 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(牙买加)English (Jamaica) 0x20090x2009 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(马来西亚)English (Malaysia) 0x44090x4409 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(新西兰)English (New Zealand) 0x14090x1409 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(菲律宾)English (Philippines) 0x34090x3409 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(新加坡)English (Singapore) 0x48090x4809 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(南非)English (South Africa) 0x1c090x1c09 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(特立尼达和多巴哥)English (Trinidad and Tobago) 0x2c090x2c09 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(英国)English (United Kingdom) 0x08090x0809 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
英语(美国)English (United States) 0x04090x0409 0x04090x0409 SQL_Latin1_General_CP1_CI_ASSQL_Latin1_General_CP1_CI_AS
英语(津巴布韦)English (Zimbabwe) 0x30090x3009 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
爱沙尼亚语(爱沙尼亚)Estonian (Estonia) 0x04250x0425 0x04250x0425 Estonian_CI_ASEstonian_CI_AS
法罗语(法罗群岛)Faroese (Faroe Islands) 0x04380x0438 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
菲律宾语(菲律宾)Filipino (Philippines) 0x04640x0464 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
芬兰语(芬兰)Finnish (Finland) 0x040b0x040b 0x040b0x040b Finnish_Swedish_CI_ASFinnish_Swedish_CI_AS
法语(比利时)French (Belgium) 0x080c0x080c 0x040c0x040c French_CI_ASFrench_CI_AS
法语(加拿大)French (Canada) 0x0c0c0x0c0c 0x040c0x040c French_CI_ASFrench_CI_AS
法语(法国)French (France) 0x040c0x040c 0x040c0x040c French_CI_ASFrench_CI_AS
法语(卢森堡)French (Luxembourg) 0x140c0x140c 0x040c0x040c French_CI_ASFrench_CI_AS
法语(摩纳哥)French (Monaco) 0x180c0x180c 0x040c0x040c French_CI_ASFrench_CI_AS
法语(瑞士)French (Switzerland) 0x100c0x100c 0x040c0x040c French_CI_ASFrench_CI_AS
弗里西亚语(荷兰)Frisian (Netherlands) 0x04620x0462 0x04620x0462 Latin1_General_CI_AILatin1_General_CI_AI
加利西亚语Galician 0x04560x0456 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
格鲁吉亚语(格鲁吉亚)Georgian (Georgia) 0x104370x10437 0x104370x10437 Georgian_Modern_Sort_CI_ASGeorgian_Modern_Sort_CI_AS
格鲁吉亚语(格鲁吉亚)Georgian (Georgia) 0x04370x0437 0x04190x0419 Latin1_General_CI_ASLatin1_General_CI_AS
德语 - 电话簿排序 (DIN)German - Phone Book Sort (DIN) 0x104070x10407 0x104070x10407 German_PhoneBook_CI_ASGerman_PhoneBook_CI_AS
德语(奥地利)German (Austria) 0x0c070x0c07 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
德语(德国)German (Germany) 0x04070x0407 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
德语(列支敦士登)German (Liechtenstein) 0x14070x1407 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
德语(卢森堡)German (Luxembourg) 0x10070x1007 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
德语(瑞士)German (Switzerland) 0x08070x0807 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
希腊语(希腊)Greek (Greece) 0x04080x0408 0x04080x0408 Greek_CI_ASGreek_CI_AS
格陵兰语(格陵兰)Greenlandic (Greenland) 0x046f0x046f 0x04060x0406 Danish_Norwegian_CI_ASDanish_Norwegian_CI_AS
古吉拉特语(印度)Gujarati (India) 0x04470x0447 0x04390x0439 在服务器级别不可用Not available at server level
豪撒语(尼日利亚,拉丁语)Hausa (Nigeria, Latin) 0x04680x0468 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
希伯来语(以色列)Hebrew (Israel) 0x040d0x040d 0x040d0x040d Hebrew_CI_ASHebrew_CI_AS
印地语(印度)Hindi (India) 0x04390x0439 0x04390x0439 在服务器级别不可用Not available at server level
匈牙利语(匈牙利)Hungarian (Hungary) 0x040e0x040e 0x040e0x040e Hungarian_CI_ASHungarian_CI_AS
匈牙利语技术排序Hungarian Technical Sort 0x1040e0x1040e 0x1040e0x1040e Hungarian_Technical_CI_ASHungarian_Technical_CI_AS
冰岛语(冰岛)Icelandic (Iceland) 0x040f0x040f 0x040f0x040f Icelandic_CI_ASIcelandic_CI_AS
伊博语(尼日利亚)Igbo (Nigeria) 0x04700x0470 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
印度尼西亚语(印度尼西亚)Indonesian (Indonesia) 0x04210x0421 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
因纽特语(加拿大,拉丁语)Inuktitut (Canada, Latin) 0x085d0x085d 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
因纽特语(语音节)加拿大Inuktitut (Syllabics) Canada 0x045d0x045d 0x045d0x045d Latin1_General_CI_AILatin1_General_CI_AI
爱尔兰语(爱尔兰)Irish (Ireland) 0x083c0x083c 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
意大利语(意大利)Italian (Italy) 0x04100x0410 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
意大利语(瑞士)Italian (Switzerland) 0x08100x0810 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
日语(日本 XJIS)Japanese (Japan XJIS) 0x04110x0411 0x04110x0411 Japanese_CI_ASJapanese_CI_AS
日语(日本)Japanese (Japan) 0x0404110x040411 0x404110x40411 Latin1_General_CI_AILatin1_General_CI_AI
卡纳达语(印度)Kannada (India) 0x044b0x044b 0x04390x0439 在服务器级别不可用Not available at server level
哈萨克语(哈萨克斯坦)Kazakh (Kazakhstan) 0x043f0x043f 0x043f0x043f Kazakh_90_CI_ASKazakh_90_CI_AS
高棉语(柬埔寨)Khmer (Cambodia) 0x04530x0453 0x04530x0453 在服务器级别不可用Not available at server level
基切语(危地马拉)K'iche (Guatemala) 0x04860x0486 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
卢旺达语(卢旺达)Kinyarwanda (Rwanda) 0x04870x0487 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
孔卡尼语(印度)Konkani (India) 0x04570x0457 0x04390x0439 在服务器级别不可用Not available at server level
韩语(韩语字典排序)Korean (Korea Dictionary Sort) 0x04120x0412 0x04120x0412 Korean_Wansung_CI_ASKorean_Wansung_CI_AS
柯尔克孜语(吉尔吉斯斯坦)Kyrgyz (Kyrgyzstan) 0x04400x0440 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
老挝语(老挝人民民主共和国)Lao (Lao PDR) 0x04540x0454 0x04540x0454 在服务器级别不可用Not available at server level
拉脱维亚语(拉脱维亚)Latvian (Latvia) 0x04260x0426 0x04260x0426 Latvian_CI_ASLatvian_CI_AS
立陶宛语(立陶宛)Lithuanian (Lithuania) 0x04270x0427 0x04270x0427 Lithuanian_CI_ASLithuanian_CI_AS
下索布语(德国)Lower Sorbian (Germany) 0x082e0x082e 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
卢森堡语(卢森堡)Luxembourgish (Luxembourg) 0x046e0x046e 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
马其顿语(北马其顿,FYROM)Macedonian (North Macedonia, FYROM) 0x042f0x042f 0x042f0x042f Macedonian_FYROM_90_CI_ASMacedonian_FYROM_90_CI_AS
马来语(文莱达鲁萨兰国)Malay (Brunei Darussalam) 0x083e0x083e 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
马来语(马来西亚)Malay (Malaysia) 0x043e0x043e 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
马拉雅拉姆语(印度)Malayalam (India) 0x044c0x044c 0x04390x0439 在服务器级别不可用Not available at server level
马耳他语(马耳他)Maltese (Malta) 0x043a0x043a 0x043a0x043a Latin1_General_CI_AILatin1_General_CI_AI
毛利语(新西兰)Maori (New Zealand) 0x04810x0481 0x04810x0481 Latin1_General_CI_AILatin1_General_CI_AI
马普丹冈语(智利)Mapudungun (Chile) 0x047a0x047a 0x047a0x047a Latin1_General_CI_AILatin1_General_CI_AI
马拉地语(印度)Marathi (India) 0x044e0x044e 0x04390x0439 在服务器级别不可用Not available at server level
莫霍克语(加拿大)Mohawk (Canada) 0x047c0x047c 0x047c0x047c Latin1_General_CI_AILatin1_General_CI_AI
蒙古语(蒙古)Mongolian (Mongolia) 0x04500x0450 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
蒙古语(中国)Mongolian (PRC) 0x08500x0850 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
尼泊尔语(尼泊尔)Nepali (Nepal) 0x04610x0461 0x04610x0461 在服务器级别不可用Not available at server level
挪威语(博克马尔语,挪威)Norwegian (Bokmål, Norway) 0x04140x0414 0x04140x0414 Latin1_General_CI_AILatin1_General_CI_AI
挪威语(尼诺斯克语,挪威)Norwegian (Nynorsk, Norway) 0x08140x0814 0x04140x0414 Latin1_General_CI_AILatin1_General_CI_AI
奥克西唐语(法国)Occitan (France) 0x04820x0482 0x040c0x040c French_CI_ASFrench_CI_AS
奥里亚语(印度)Odia (India) 0x04480x0448 0x04390x0439 在服务器级别不可用Not available at server level
普什图语(阿富汗)Pashto (Afghanistan) 0x04630x0463 0x04630x0463 在服务器级别不可用Not available at server level
波斯语(伊朗)Persian (Iran) 0x04290x0429 0x04290x0429 Latin1_General_CI_AILatin1_General_CI_AI
波兰语(波兰)Polish (Poland) 0x04150x0415 0x04150x0415 Polish_CI_ASPolish_CI_AS
葡萄牙语(巴西)Portuguese (Brazil) 0x04160x0416 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
葡萄牙语(葡萄牙)Portuguese (Portugal) 0x08160x0816 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
旁遮普语(印度)Punjabi (India) 0x04460x0446 0x04390x0439 在服务器级别不可用Not available at server level
克丘亚语(玻利维亚)Quechua (Bolivia) 0x046b0x046b 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
克丘亚语(厄瓜多尔)Quechua (Ecuador) 0x086b0x086b 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
克丘亚语(秘鲁)Quechua (Peru) 0x0c6b0x0c6b 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
罗马尼亚语(罗马尼亚)Romanian (Romania) 0x04180x0418 0x04180x0418 Romanian_CI_ASRomanian_CI_AS
罗曼什语(瑞士)Romansh (Switzerland) 0x04170x0417 0x04170x0417 Latin1_General_CI_AILatin1_General_CI_AI
俄语(俄罗斯)Russian (Russia) 0x04190x0419 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
萨哈语(俄罗斯)Sahka (Russia) 0x04850x0485 0x04850x0485 Latin1_General_CI_AILatin1_General_CI_AI
萨米语(伊纳里,芬兰)Sami (Inari, Finland) 0x243b0x243b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(律勒欧,挪威)Sami (Lule, Norway) 0x103b0x103b 0x043b0x043b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(律勒欧,瑞典)Sami (Lule, Sweden) 0x143b0x143b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(北方,芬兰)Sami (Northern, Finland) 0x0c3b0x0c3b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(北方,挪威)Sami (Northern, Norway) 0x043b0x043b 0x043b0x043b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(北方,瑞典)Sami (Northern, Sweden) 0x083b0x083b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(斯科特,芬兰)Sami (Skolt, Finland) 0x203b0x203b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(南方,挪威)Sami (Southern, Norway) 0x183b0x183b 0x043b0x043b Latin1_General_CI_AILatin1_General_CI_AI
萨米语(南方,瑞典)Sami (Southern, Sweden) 0x1c3b0x1c3b 0x083b0x083b Latin1_General_CI_AILatin1_General_CI_AI
梵语(印度)Sanskrit (India) 0x044f0x044f 0x04390x0439 在服务器级别不可用Not available at server level
塞尔维亚语(波斯尼亚和黑塞哥维那,西里尔文)Serbian (Bosnia and Herzegovina, Cyrillic) 0x1c1a0x1c1a 0x0c1a0x0c1a Latin1_General_CI_AILatin1_General_CI_AI
塞尔维亚语(波斯尼亚和黑塞哥维那,拉丁语)Serbian (Bosnia and Herzegovina, Latin) 0x181a0x181a 0x081a0x081a Latin1_General_CI_AILatin1_General_CI_AI
塞尔维亚语(塞尔维亚,西里尔语)Serbian (Serbia, Cyrillic) 0x0c1a0x0c1a 0x0c1a0x0c1a Latin1_General_CI_AILatin1_General_CI_AI
塞尔维亚语(塞尔维亚,拉丁语)Serbian (Serbia, Latin) 0x081a0x081a 0x081a0x081a Latin1_General_CI_AILatin1_General_CI_AI
巴索托语/北索托语(南非)Sesotho sa Leboa/Northern Sotho (South Africa) 0x046c0x046c 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
茨瓦纳语/博茨瓦纳(南非)Setswana/Tswana (South Africa) 0x04320x0432 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
僧伽罗语(斯里兰卡)Sinhala (Sri Lanka) 0x045b0x045b 0x04390x0439 在服务器级别不可用Not available at server level
斯洛伐克语(斯洛伐克)Slovak (Slovakia) 0x041b0x041b 0x041b0x041b Slovak_CI_ASSlovak_CI_AS
斯洛文尼亚语(斯洛文尼亚)Slovenian (Slovenia) 0x04240x0424 0x04240x0424 Slovenian_CI_ASSlovenian_CI_AS
西班牙语(阿根廷)Spanish (Argentina) 0x2c0a0x2c0a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(玻利维亚)Spanish (Bolivia) 0x400a0x400a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(智利)Spanish (Chile) 0x340a0x340a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(哥伦比亚)Spanish (Colombia) 0x240a0x240a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(哥斯达黎加)Spanish (Costa Rica) 0x140a0x140a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(多米尼加共和国)Spanish (Dominican Republic) 0x1c0a0x1c0a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(厄瓜多尔)Spanish (Ecuador) 0x300a0x300a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(萨尔瓦多)Spanish (El Salvador) 0x440a0x440a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(危地马拉)Spanish (Guatemala) 0x100a0x100a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(洪都拉斯)Spanish (Honduras) 0x480a0x480a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(墨西哥)Spanish (Mexico) 0x080a0x080a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙(尼加拉瓜)Spanish (Nicaragua) 0x4c0a0x4c0a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(巴拿马)Spanish (Panama) 0x180a0x180a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(巴拉圭)Spanish (Paraguay) 0x3c0a0x3c0a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(秘鲁)Spanish (Peru) 0x280a0x280a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(波多黎各)Spanish (Puerto Rico) 0x500a0x500a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(西班牙)Spanish (Spain) 0x0c0a0x0c0a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(西班牙,传统风格)Spanish (Spain, Traditional Sort) 0x040a0x040a 0x040a0x040a Traditional_Spanish_CI_ASTraditional_Spanish_CI_AS
西班牙语(美国)Spanish (United States) 0x540a0x540a 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
西班牙语(乌拉圭)Spanish (Uruguay) 0x380a0x380a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
西班牙语(委内瑞拉)Spanish (Venezuela) 0x200a0x200a 0x0c0a0x0c0a Modern_Spanish_CI_ASModern_Spanish_CI_AS
斯瓦希里语(肯尼亚)Swahili (Kenya) 0x04410x0441 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
瑞典语(芬兰)Swedish (Finland) 0x081d0x081d 0x040b0x040b Finnish_Swedish_CI_ASFinnish_Swedish_CI_AS
瑞典语(瑞典)Swedish (Sweden) 0x041d0x041d 0x040b0x040b Finnish_Swedish_CI_ASFinnish_Swedish_CI_AS
叙利亚语(叙利亚)Syriac (Syria) 0x045a0x045a 0x045a0x045a 在服务器级别不可用Not available at server level
塔吉克语(塔吉克斯坦)Tajik (Tajikistan) 0x04280x0428 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
塔马塞特文(阿尔及利亚,拉丁语)Tamazight (Algeria, Latin) 0x085f0x085f 0x085f0x085f Latin1_General_CI_AILatin1_General_CI_AI
泰米尔语(印度)Tamil (India) 0x04490x0449 0x04390x0439 在服务器级别不可用Not available at server level
鞑靼语(俄罗斯)Tatar (Russia) 0x04440x0444 0x04440x0444 Cyrillic_General_CI_ASCyrillic_General_CI_AS
泰卢固语(印度)Telugu (India) 0x044a0x044a 0x04390x0439 在服务器级别不可用Not available at server level
泰语(泰国)Thai (Thailand) 0x041e0x041e 0x041e0x041e Thai_CI_ASThai_CI_AS
藏语(中国)Tibetan (PRC) 0x04510x0451 0x04510x0451 在服务器级别不可用Not available at server level
土耳其语(土耳其)Turkish (Turkey) 0x041f0x041f 0x041f0x041f Turkish_CI_ASTurkish_CI_AS
土库曼语(土库曼斯坦)Turkmen (Turkmenistan) 0x04420x0442 0x04420x0442 Latin1_General_CI_AILatin1_General_CI_AI
维吾尔语(中国)Uighur (PRC) 0x04800x0480 0x04800x0480 Latin1_General_CI_AILatin1_General_CI_AI
乌克兰语(乌克兰)Ukrainian (Ukraine) 0x04220x0422 0x04220x0422 Ukrainian_CI_ASUkrainian_CI_AS
上索布语(德国)Upper Sorbian (Germany) 0x042e0x042e 0x042e0x042e Latin1_General_CI_AILatin1_General_CI_AI
乌尔都语(巴基斯坦)Urdu (Pakistan) 0x04200x0420 0x04200x0420 Latin1_General_CI_AILatin1_General_CI_AI
乌兹别克语(乌兹别克斯坦,西里尔文)Uzbek (Uzbekistan, Cyrillic) 0x08430x0843 0x04190x0419 Cyrillic_General_CI_ASCyrillic_General_CI_AS
乌兹别克语(乌兹别克斯坦,拉丁语)Uzbek (Uzbekistan, Latin) 0x04430x0443 0x04430x0443 Uzbek_Latin_90_CI_ASUzbek_Latin_90_CI_AS
越南语(越南)Vietnamese (Vietnam) 0x042a0x042a 0x042a0x042a Vietnamese_CI_ASVietnamese_CI_AS
威尔士语(英国)Welsh (United Kingdom) 0x04520x0452 0x04520x0452 Latin1_General_CI_AILatin1_General_CI_AI
沃洛夫语(塞内加尔)Wolof (Senegal) 0x04880x0488 0x040c0x040c French_CI_ASFrench_CI_AS
班图语/索萨语(南非)Xhosa/isiXhosa (South Africa) 0x04340x0434 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
彝语(中国)Yi (PRC) 0x04780x0478 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
约鲁巴语(尼日利亚)Yoruba (Nigeria) 0x046a0x046a 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS
祖鲁语(南非)Zulu/isiZulu (South Africa) 0x04350x0435 0x04090x0409 Latin1_General_CI_ASLatin1_General_CI_AS

为服务器分配排序规则后,只能通过导出所有数据库对象和数据来更改它,重新生成 master 数据库,并导入所有数据库对象和数据。After you've assigned a collation to the server, you can change it only by exporting all database objects and data, rebuilding the master database, and importing all database objects and data. 与更改 SQL ServerSQL Server 实例的默认排序规则相反,可在创建新数据库或数据库列时指定所需的排序规则。Instead of changing the default collation of an instance of SQL ServerSQL Server, you can specify the desired collation when you create a new database or database column.

若要查询 SQL ServerSQL Server 实例的服务器排序规则,请使用下列 SERVERPROPERTY 函数:To query the server collation for an instance of SQL ServerSQL Server, use the SERVERPROPERTY function:

SELECT CONVERT(varchar, SERVERPROPERTY('collation'));

若要查询服务器以找到所有可用的排序规则,请使用以下 fn_helpcollations() 内置函数:To query the server for all available collations, use the following fn_helpcollations() built-in function:

SELECT * FROM sys.fn_helpcollations();

数据库级排序规则Database-level collations

创建或修改数据库时,可使用 CREATE DATABASEALTER DATABASE 语句的 COLLATE 子句指定默认数据库排序规则。When you create or modify a database, you can use the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement to specify the default database collation. 如果未指定排序规则,将为该数据库分配服务器排序规则。If no collation is specified, the database is assigned the server collation.

除非更改服务器的排序规则,否则无法更改系统数据库的排序规则。You can't change the collation of system databases unless you change the collation for the server.

数据库排序规则将应用于数据库中的所有元数据,并且是所有字符串列、临时对象、变量名称和数据库中使用的任何其他字符串的默认排序规则。The database collation is used for all metadata in the database, and the collation is the default for all string columns, temporary objects, variable names, and any other strings used in the database. 当更改用户数据库的排序规则时,如果在数据库访问临时表中进行查询,则可能出现排序规则冲突。When you change the collation of a user database, there can be collation conflicts when queries in the database access temporary tables. 临时表始终存储在 tempdb 系统数据库中,该数据库使用实例的排序规则。Temporary tables are always stored in the tempdb system database, which uses the collation for the instance. 如果排序规则导致计算字符数据时出现冲突,则比较用户数据库和 tempdb 之间的字符数据的查询可能会失败。Queries that compare character data between the user database and tempdb might fail if the collations cause a conflict in evaluating the character data. 可以通过在查询中指定 COLLATE 子句来解决此问题。You can resolve this issue by specifying the COLLATE clause in the query. 有关详细信息,请参阅排序规则 (Transact-SQL)For more information, see COLLATE (Transact-SQL).

备注

Azure SQL 数据库Azure SQL Database 上创建数据库后,将无法更改排序规则。You can't change the collation after the database has been created on Azure SQL 数据库Azure SQL Database.

可使用如下的 ALTER DATABASE 语句更改用户数据库的排序规则:You can change the collation of a user database by using an ALTER DATABASE statement that's similar to the following:

ALTER DATABASE myDB COLLATE Greek_CS_AI;

重要

更改数据库级排序规则不会影响列级排序规则或表达式级排序规则。Altering the database-level collation doesn't affect column-level or expression-level collations.

可使用如下的语句来检索数据库的当前排序规则:You can retrieve the current collation of a database by using a statement that's similar to the following:

SELECT CONVERT (VARCHAR(50), DATABASEPROPERTYEX('database_name','collation'));

列级排序规则Column-level collations

当创建或更改表时,可使用 COLLATE 子句指定每个字符串列的排序规则。When you create or alter a table, you can specify collations for each character-string column by using the COLLATE clause. 如果不指定排序规则,将为列分配数据库的默认排序规则。If you don't specify a collation, the column is assigned the default collation of the database.

可使用如下的 ALTER TABLE 语句更改列的排序规则:You can change the collation of a column by using an ALTER TABLE statement that's similar to the following:

ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI;

表达式级排序规则Expression-level collations

表达式级排序规则在语句运行时设置,并且影响结果集的返回方式。Expression-level collations are set when a statement is run, and they affect the way a result set is returned. 这可以使 ORDER BY 排序结果特定于区域设置。This enables ORDER BY sort results to be locale-specific. 要实现表达式级别的排序规则,请使用如下的 COLLATE 子句:To implement expression-level collations, use a COLLATE clause such as the following:

SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;    

区域设置Locale

区域设置是与位置或区域性相关联的一组信息。A locale is a set of information that's associated with a location or a culture. 此信息可以包括所用语言的名称和标识符、用于书写该语言的文字以及文化习俗。The information can include the name and identifier of the spoken language, the script that's used to write the language, and cultural conventions. 排序规则可以与一个或多个区域设置相关联。Collations can be associated with one or more locales. 有关详细信息,请参阅 Microsoft 分配的区域设置 IDFor more information, see Locale IDs Assigned by Microsoft.

代码页Code page

代码页是给定脚本的有序字符集,其中数值索引(即码位值)与每个字符相关联。A code page is an ordered set of characters of a given script in which a numeric index, or code point value, is associated with each character. Windows 代码页通常被称为“字符集” 。A Windows code page is typically referred to as a character set or a charset. 代码页用于支持不同的 Windows 系统区域设置所使用的字符集和键盘布局。Code pages are used to provide support for the character sets and keyboard layouts that are used by different Windows system locales.

排序顺序Sort order

排序顺序指定数据值的排序方式。Sort order specifies how data values are sorted. 该顺序影响数据比较的结果。The order affects the results of data comparison. 数据的排序通过使用排序规则而实现,且可使用索引对排序进行优化。Data is sorted by using collations, and it can be optimized by using indexes.

Unicode 支持Unicode support

Unicode 是一种将码位映射到字符的标准。Unicode is a standard for mapping code points to characters. 由于它旨在涵盖全球所有语言的所有字符,因此,无需使用不同代码页来处理不同字符集。Because it's designed to cover all the characters of all the languages of the world, you don't need different code pages to handle different sets of characters.

Unicode 基础知识Unicode basics

当只使用字符数据和代码页时,在一个数据库内很难以多种语言存储数据。Storing data in multiple languages within one database is difficult to manage when you use only character data and code pages. 也很难为数据库找到一种能存储所有需要的特定语言字符的代码页。It's also difficult to find one code page for the database that can store all the required language-specific characters. 此外,当运行不同代码页的不同客户端读取和更新特殊字符时,很难保证正确转换这些字符。Additionally, it's difficult to guarantee the correct translation of special characters when they're being read or updated by a variety of clients that are running various code pages. 支持国际化客户端的数据库应始终使用 Unicode 数据类型,而不应使用非 Unicode 数据类型。Databases that support international clients should always use Unicode data types instead of non-Unicode data types.

例如,有一个必须处理三种主要语言的北美洲客户的数据库:For example, consider a database of customers in North America that must handle three major languages:

  • 墨西哥使用的西班牙语名称和地址Spanish names and addresses for Mexico
  • 魁北克使用的法语名称和地址French names and addresses for Quebec
  • 加拿大其余地区和美国使用的英语名称和地址English names and addresses for the rest of Canada and the United States

当只使用字符列和代码页时,必须小心处理,确保与数据库一起安装的代码页能处理所有这三种语言的字符。When you use only character columns and code pages, you must take care to ensure that the database is installed with a code page that will handle the characters of all three languages. 当正在运行的代码页的客户端读取另一种语言的字符时,还必须注意确保从任何一种语言正确转换字符。You must also take care to guarantee the correct translation of characters from any of the languages when the characters are read by clients that are running a code page for another language.

备注

客户端使用的代码页由操作系统 (OS) 设置确定。The code pages that a client uses are determined by the operating system (OS) settings. 若要在 Windows 操作系统上设置客户端代码页,请使用“控制面板”中的 “区域设置”To set client code pages on the Windows operating system, use Regional Settings in Control Panel.

对于支持世界范围的读者所需的所有字符的字符数据类型,很难为其选择代码页。It would be difficult to select a code page for character data types that will support all the characters that are required by a worldwide audience. 在国际化数据库中,最简单的字符数据管理方法是始终使用支持 Unicode 的数据类型。The easiest way to manage character data in international databases is to always use a data type that supports Unicode.

Unicode 数据类型Unicode data types

如果在 SQL ServerSQL ServerSQL Server 2005 (9.x)SQL Server 2005 (9.x) 及更高版本)中存储反映多种语言的字符数据,请使用 Unicode 数据类型(nchar、nvarchar 和 ntext),而不是非 Unicode 数据类型(char、varchar 和 text )。If you store character data that reflects multiple languages in SQL ServerSQL Server (SQL Server 2005 (9.x)SQL Server 2005 (9.x) and later), use Unicode data types (nchar, nvarchar, and ntext) instead of non-Unicode data types (char, varchar, and text).

备注

对于 Unicode 数据类型,数据库引擎Database Engine最多可以使用 UCS-2 表示 65,535 个字符;或者,如果使用了附属字符,可表示整个 Unicode 范围(‭1,114,111 个字符)。For Unicode data types, the 数据库引擎Database Engine can represent up to 65,535 characters using UCS-2, or the full Unicode range (‭1,114,111‬ characters) if supplementary characters are used. 如需详细了解如何启用增补字符,请参阅字符For more information about enabling supplementary characters, see Supplementary Characters.

或者,从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,如果使用支持 UTF-8 的排序规则 (_UTF8),则以前的非 Unicode 数据类型(char 和 varchar)将变为使用 UTF-8 编码的 Unicode 数据类型 。Alternatively, starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), if a UTF-8 enabled collation (_UTF8) is used, previously non-Unicode data types (char and varchar) become Unicode data types using UTF-8 encoding. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 不会更改以前存在的 Unicode 数据类型(nchar、nvarchar 和 ntext)的行为,且继续使用 UCS-2 或 UTF-16 编码 。doesn't change the behavior of previously existing Unicode data types (nchar, nvarchar, and ntext), which continue to use UCS-2 or UTF-16 encoding. 有关详细信息,请参阅 UTF-8 与 UTF-16 的存储差异For more information, see Storage differences between UTF-8 and UTF-16.

Unicode 注意事项Unicode considerations

非 Unicode 数据类型有明显的局限性,Significant limitations are associated with non-Unicode data types. 这是因为非 Unicode 计算机只能使用单个代码页。This is because a non-Unicode computer is limited to using a single code page. 使用 Unicode,你可能会体验到性能提升,因为这只需要较少的代码页转换。You might experience performance gain by using Unicode, because it requires fewer code-page conversions. 必须在数据库级、列级或表达式级单独选择 Unicode 排序规则,因为在服务器级不支持 Unicode 排序规则。Unicode collations must be selected individually at the database, column, or expression level because they aren't supported at the server level.

在将数据从服务器移到客户端时,服务器排序规则可能无法由旧版本的客户端驱动程序识别。When you move data from a server to a client, your server collation might not be recognized by older client drivers. 将数据从 Unicode 服务器移到非 Unicode 客户端时,可能会出现此情况。This can occur when you move data from a Unicode server to a non-Unicode client. 最好升级客户端操作系统,以使基础系统排序规则得以升级。Your best option might be to upgrade the client operating system so that the underlying system collations are updated. 如果客户端上安装了数据库客户端软件,则可以考虑对数据库客户端软件应用服务更新。If the client has database client software installed, you might consider applying a service update to the database client software.

提示

还可以尝试针对服务器上的数据使用另一个排序规则。You can also try to use a different collation for the data on the server. 选择一个映射到客户端上的代码页的排序规则。Choose a collation that maps to a code page on the client.

若要使用 SQL ServerSQL ServerSQL Server 2012 (11.x)SQL Server 2012 (11.x) 及更高版本)中提供的 UTF-16 排序规则来改进对一些 Unicode 字符的搜索和排序(仅 Windows 排序规则),可以选择增补字符 (_SC) 排序规则之一,或版本 140 排序规则之一。To use the UTF-16 collations that are available in SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later) to improve searching and sorting of some Unicode characters (Windows collations only), you can select either one of the supplementary characters (_SC) collations or one of the version 140 collations.

若要使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中提供的 UTF-8 排序规则来改进对某些 Unicode 字符的搜索和排序(仅 Windows 排序规则),必须选择已启用 UTF-8 编码的排序规则 (_UTF8)。To use the UTF-8 collations that are available in SQL Server 2019 (15.x)SQL Server 2019 (15.x), and to improve searching and sorting of some Unicode characters (Windows collations only), you must select UTF-8 encoding-enabled collations(_UTF8).

  • UTF8 标志可应用于:The UTF8 flag can be applied to:

    • 已支持补充字符 (_SC) 或区分变体选择符 (_VSS) 感知的语言排序规则Linguistic collations that already support supplementary characters (_SC) or variation-selector-sensitive (_VSS) awareness
    • BIN21 二进制排序规则BIN21 binary collation
  • UTF8 标志可应用于:The UTF8 flag can't be applied to:

    • 不支持补充字符 (_SC) 或区分变体选择符 (_VSS) 感知的语言排序规则Linguistic collations that don't support supplementary characters (_SC) or variation-selector-sensitive (_VSS) awareness
    • BIN 或 BIN22 二进制排序规则The BIN or BIN22 binary collations
    • SQL_* 排序规则The SQL_* collations

1SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3 起。1 Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3. SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 3.0 已将排序规则 UTF8_BIN2 替换为 Latin1_General_100_BIN2_UTF8 。CTP 3.0 replaced collation UTF8_BIN2 with Latin1_General_100_BIN2_UTF8.
2截至 SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3。2 Up to with SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.3.

若要评估与使用 Unicode 或非 Unicode 数据类型相关的问题,请测试您的具体方案以确定您所在环境下的性能差异大小。To evaluate issues that are related to using Unicode or non-Unicode data types, test your scenario to measure performance differences in your environment. 最好对整个组织中的系统所使用的排序规则进行标准化,并尽可能部署 Unicode 服务器和客户端。It's a good practice to standardize the collation that's used on systems across your organization, and to deploy Unicode servers and clients wherever possible.

在许多情况下,SQL ServerSQL Server 与其他服务器或客户端交互,你的组织可能会使用应用程序和服务器实例之间的多种数据访问标准。In many situations, SQL ServerSQL Server interacts with other servers or clients, and your organization might use multiple data-access standards between applications and server instances. SQL ServerSQL Server 客户端是两种主要类型之一:clients are one of two main types:

  • 使用 OLE DB 和开放式数据库连接 (ODBC) 3.7 版或更高版本的 Unicode 客户端。Unicode clients that use OLE DB and Open Database Connectivity (ODBC) version 3.7 or later.
  • 使用 DB-Library 和 ODBC 3.6 版或更低版本的非 Unicode 客户端。Non-Unicode clients that use DB-Library and ODBC version 3.6 or earlier.

下表提供有关以 Unicode 和非 Unicode 服务器的各种组合使用多语言数据的信息:The following table provides information about using multilingual data with various combinations of Unicode and non-Unicode servers:

服务器Server ClientClient 优点或局限性Benefits or limitations
UnicodeUnicode UnicodeUnicode 因为 Unicode 数据在整个系统中使用,所以此方案可提供最佳的性能并可保护检索到的数据免受破坏。Because Unicode data is used throughout the system, this scenario provides the best performance and protection from corruption of retrieved data. ActiveX 数据对象 (ADO)、OLE DB 和 ODBC 3.7 版或更高版本都采用这样的配置。This is the situation with ActiveX Data Objects (ADO), OLE DB, and ODBC version 3.7 or later.
UnicodeUnicode 非 UnicodeNon-Unicode 在这种情况下,尤其对于正在运行新操作系统的服务器与正在运行旧版本 SQL ServerSQL Server或基于旧操作系统的客户端之间的连接,当向客户端计算机移动数据时,会受到限制或出现错误。In this scenario, especially with connections between a server that's running a newer operating system and a client that's running an earlier version of SQL ServerSQL Server, or on an older operating system, there can be limitations or errors when you move data to a client computer. 服务器上的 Unicode 数据尝试映射到非 Unicode 客户端的对应代码页,以转换数据。Unicode data on the server tries to map to a corresponding code page on the non-Unicode client to convert the data.
非 UnicodeNon-Unicode UnicodeUnicode 该方案对使用多语言数据不是理想配置。This isn't an ideal configuration for using multilingual data. 在此方案下不能向非 Unicode 服务器写入 Unicode 数据。You can't write Unicode data to the non-Unicode server. 在向服务器的代码页之外的服务器发送数据时,很可能会发生问题。Problems are likely to occur when data is sent to servers that are outside the server's code page.
非 UnicodeNon-Unicode 非 UnicodeNon-Unicode 这是对多语言数据有极大局限性的方案。This is a very limiting scenario for multilingual data. 您只可使用一个代码页。You can use only a single code page.

增补字符Supplementary characters

Unicode 联盟为每个字符都分配一个唯一码位(介于 000000-10FFFF 之间的值)。The Unicode Consortium allocates to each character a unique code point, which is a value in the range 000000–10FFFF. 最常用字符的码位值介于范围 000000-00FFFF(65,535 个字符)之间,可以装入内存中和磁盘上的 8 位字或 16 位字中。The most frequently used characters have code point values in the range 000000–00FFFF (65,535 characters) which fit into an 8-bit or 16-bit word in memory and on-disk. 通常将此范围指定为基本多文种平面 (BMP)。This range is usually designated as the Basic Multilingual Plane (BMP).

但 Unicode 联盟额外建立了 16 个字符“平面”,每个平面的大小都与 BMP 相同。But the Unicode Consortium has established 16 additional "planes" of characters, each the same size as the BMP. 此定义允许 Unicode 表示介于码位范围 000000-10FFFF 之间的 1,114,112 个字符(即 216* 17 个字符)。This definition allows Unicode the potential to represent 1,114,112 characters (that is, 216 * 17 characters) within the code point range 000000–10FFFF. 码位值大于 00FFFF 的字符需要 2 到 4 个连续 8 位字 (UTF-8),或 2 个连续 16 位字 (UTF-16)。Characters with code point values larger than 00FFFF require two to four consecutive 8-bit words (UTF-8), or two consecutive 16-bit words (UTF-16). 超出 BMP 的字符称为“附属字符”,其他连续 8 位字或 16 位字称为“代理项对”。These characters located beyond the BMP are called supplementary characters, and the additional consecutive 8-bit or 16-bit words are called surrogate pairs. 如需了解增补字符、代理项、代理项对的更多详细信息,请参阅 Unicode 标准For more information about supplementary characters, surrogates, and surrogate pairs, refer to the Unicode Standard.

SQL ServerSQL Server 提供用于存储介于 BMP 范围(000000-00FFFF)内的 Unicode 数据的数据类型(如 nchar 和 nvarchar),而数据库引擎Database Engine使用 UCS-2 编码它们。provides data types such as nchar and nvarchar to store Unicode data in the BMP range (000000–00FFFF), which the 数据库引擎Database Engine encodes using UCS-2.

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 引入了新增补字符 (_SC) 排序规则系列,可以与下面的数据类型结合使用来表示整个 Unicode 字符范围(000000-10FFFF):nchar、nvarchar 和 sql_variant。introduced a new family of supplementary character (_SC) collations that can be used with the nchar, nvarchar, and sql_variant data types to represent the full Unicode character range (000000–10FFFF). 例如:Latin1_General_100_CI_AS_SC 或 Japanese_Bushu_Kakusu_100_CI_AS_SC(如果使用日语排序规则) 。For example: Latin1_General_100_CI_AS_SC or, if you're using a Japanese collation, Japanese_Bushu_Kakusu_100_CI_AS_SC.

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 将增补字符支持扩展到,与已启用 UTF-8 的新排序规则 (_UTF8) 结合使用的数据类型 char 和 varchar。extends supplementary character support to the char and varchar data types with the new UTF-8 enabled collations (_UTF8). 这些数据类型也能表示整个 Unicode 字符范围。These data types are also capable of representing the full Unicode character range.

备注

SQL Server 2014 (12.x)SQL Server 2014 (12.x) 起,所有新 _140 排序规则都自动支持增补字符。Starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), all new _140 collations automatically support supplementary characters.

如果使用增补字符:If you use supplementary characters:

  • 只有在 90 版本或更高版本的排序规则中才可以将增补字符用于排序和比较操作。Supplementary characters can be used in ordering and comparison operations in collation versions 90 or greater.

  • 所有 100 版本的排序规则均支持使用补充字符进行语言排序。All version 100 collations support linguistic sorting with supplementary characters.

  • 不支持在元数据(如数据库对象的名称)中使用增补字符。Supplementary characters aren't supported for use in metadata, such as in names of database objects.

  • SC 标志可应用于:The SC flag can be applied to:

    • 90 版本的排序规则Version 90 collations
    • 100 版本的排序规则Version 100 collations
  • SC 标志不能应用于:The SC flag can't be applied to:

    • 80 版本非版本化 Windows 排序规则Version 80 non-versioned Windows collations
    • BIN 或 BIN2 二进制排序规则The BIN or BIN2 binary collations
    • SQL* 排序规则The SQL* collations
    • 140 版本的排序规则(这些排序规则无需 SC 标志,因为它们已支持增补字符)Version 140 collations (these don't need the SC flag, because they already support supplementary characters)

下表比较了某些字符串函数和字符串运算符在使用具有和没有补充字符识别 (SCA) 排序规则的补充字符时的行为:The following table compares the behavior of some string functions and string operators when they use supplementary characters with and without a supplementary character-aware (SCA) collation:

字符串函数或运算符String function or operator 具有 SCA 排序规则With an SCA collation 没有 SCA 排序规则Without an SCA collation
CHARINDEXCHARINDEX

LENLEN

PATINDEXPATINDEX
UTF-16 代理项对作为单个码位计数。The UTF-16 surrogate pair is counted as a single code point. UTF-16 代理项对作为两个码位计数。The UTF-16 surrogate pair is counted as two code points.
LEFTLEFT

REPLACEREPLACE

REVERSEREVERSE

RIGHTRIGHT

SUBSTRINGSUBSTRING

STUFFSTUFF
这些函数会将每个代理项对作为单个码位处理并按预期方式工作。These functions treat each surrogate pair as a single code point and work as expected. 这些函数可能拆分任意代理项对并导致意外的结果。These functions might split any surrogate pairs and lead to unexpected results.
NCHARNCHAR 返回对应于 0-0x10FFFF 范围内指定的 Unicode 码位值的字符。Returns the character that corresponds to the specified Unicode code point value in the range 0–0x10FFFF. 如果指定的值位于 0-0xFFFF 范围内,则返回一个字符。If the specified value lies in the range 0–0xFFFF, one character is returned. 对于较高的值,则返回相应的代理项。For higher values, the corresponding surrogate is returned. 对于高于 0xFFFF 的值,将返回 NULL 而非相应的代理项。A value higher than 0xFFFF returns NULL instead of the corresponding surrogate.
UNICODEUNICODE 返回 0-0x10FFFF 范围内的一个 UTF-16 码位。Returns a UTF-16 code point in the range 0–0x10FFFF. 返回 0-0xFFFF 范围内的一个 UCS-2 码位。Returns a UCS-2 code point in the range 0–0xFFFF.
匹配一个通配符Match One Character Wildcard

通配符 - 无需匹配的字符Wildcard - Character(s) Not to Match
增补字符支持所有通配符操作。Supplementary characters are supported for all wildcard operations. 增补字符不支持这些通配符操作。Supplementary characters aren't supported for these wildcard operations. 支持其他通配符运算符。Other wildcard operators are supported.

GB18030 支持GB18030 support

GB18030 是中国对中文字符进行编码的一个单独标准。GB18030 is a separate standard that's used in the People's Republic of China for encoding Chinese characters. 在 GB18030 中,字符长度可以是 1 个字节、2 个字节或 4 个字节。In GB18030, characters can be 1, 2, or 4 bytes in length. SQL ServerSQL Server 通过对从客户端应用程序进入服务器的 GB18030 编码字符进行确认,然后在本机将其转换并存储为 Unicode 字符,来对这些字符提供支持。provides support for GB18030-encoded characters by recognizing them when they enter the server from a client-side application and converting and storing them natively as Unicode characters. 这些字符存储在服务器中后,在所有后续操作中均视为 Unicode 字符。After they're stored in the server, they're treated as Unicode characters in any subsequent operations.

可以使用任何中文排序规则,最好使用最新的 100 版本。You can use any Chinese collation, preferably the latest 100 version. 所有 _100 级排序规则均支持使用 GB18030 字符进行语言排序。All _100 level collations support linguistic sorting with GB18030 characters. 如果数据中包含增补字符(代理项对),则可以使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 中提供的 SC 排序规则来改进搜索和排序操作。If the data includes supplementary characters (surrogate pairs), you can use the SC collations that are available in SQL Server 2019 (15.x)SQL Server 2019 (15.x) to improve searching and sorting.

备注

确保 SQL Server Management StudioSQL Server Management Studio 等客户端工具使用 Dengxian 字体来正确地显示包含 GB18030 编码字符的字符串。Ensure that your client tools, such as SQL Server Management StudioSQL Server Management Studio, use the Dengxian font to correctly display strings that contain GB18030-encoded characters.

复杂文种支持Complex script support

SQL ServerSQL Server 可支持输入、存储、更改和显示复杂文种。can support inputting, storing, changing, and displaying complex scripts. 复杂文种包括下列几种类型:Complex scripts include the following types:

  • 脚本包括从右到左和从左到右两种文字组合,如阿拉伯语和英语文字的组合。Scripts that include the combination of both right-to-left and left-to-right text, such as a combination of Arabic and English text.
  • 脚本的字符根据其位置或在与其他字符组合时改变形状,如阿拉伯语、印度语和泰语字符。Scripts whose characters change shape depending on their position, or when combined with other characters, such as Arabic, Indic, and Thai characters.
  • 像泰语这样的语言需要内部词典识别单词,因为这些单词之间不断字。Languages, such as Thai, that require internal dictionaries to recognize words because there are no breaks between them.

SQL ServerSQL Server 交互的数据库应用程序必须使用支持复杂文种的控件。Database applications that interact with SQL ServerSQL Server must use controls that support complex scripts. 在托管代码中创建的标准 Windows 窗体控件支持复杂文种。Standard Windows form controls that are created in managed code are complex-script-enabled.

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 中添加的日语排序规则Japanese collations added in SQL Server 2017 (14.x)SQL Server 2017 (14.x)

SQL Server 2017 (14.x)SQL Server 2017 (14.x) 开始,支持新的日语排序规则系列,并具有各种排列选项(_CS、_AS、_KS、_WS 和 _VSS)。Starting with SQL Server 2017 (14.x)SQL Server 2017 (14.x), new Japanese collation families are supported, with the permutations of various options (_CS, _AS, _KS, _WS, and _VSS).

若要列出这些排序规则,可以查询 SQL Server 数据库引擎SQL Server Database EngineTo list these collations, you can query the SQL Server 数据库引擎SQL Server Database Engine:

SELECT Name, Description FROM fn_helpcollations()  
WHERE Name LIKE 'Japanese_Bushu_Kakusu_140%' OR Name LIKE 'Japanese_XJIS_140%'

由于所有新排序规则都内置有对增补字符的支持,因此新 _140 排序规则都没有(或不需要)SC 标志。All the new collations have built-in support for supplementary characters, so none of the new _140 collations has (or needs) the SC flag.

数据库引擎Database Engine索引、内存优化表、列存储索引和本机编译模块支持这些排序规则。These collations are supported in 数据库引擎Database Engine indexes, memory-optimized tables, columnstore indexes, and natively compiled modules.

UTF-8 支持UTF-8 support

SQL Server 2019 (15.x)SQL Server 2019 (15.x) 完全支持广泛使用的 UTF-8 字符编码作为导入或导出编码,以及作为字符串数据的数据库级别或列级别排序规则。introduces full support for the widely used UTF-8 character encoding as an import or export encoding, and as database-level or column-level collation for string data. UTF-8 受 char 和 varchar 数据类型支持,并在创建对象的排序规则或将其更改为带有 UTF8 后缀的排序规则时启用。UTF-8 is allowed in the char and varchar data types, and it's enabled when you create or change an object's collation to a collation that has a UTF8 suffix. 例如,将 LATIN1_GENERAL_100_CI_AS_SC 更改为 LATIN1_GENERAL_100_CI_AS_SC_UTF8 。One example is changing LATIN1_GENERAL_100_CI_AS_SC to LATIN1_GENERAL_100_CI_AS_SC_UTF8.

UTF-8 仅适用于支持增补字符的 Windows 排序规则,如 SQL Server 2012 (11.x)SQL Server 2012 (11.x) 中所述。UTF-8 is available only to Windows collations that support supplementary characters, as introduced in SQL Server 2012 (11.x)SQL Server 2012 (11.x). nchar 和 nvarchar 数据类型仅支持 UCS-2 或 UTF-16 编码,并保持不变。The nchar and nvarchar data types allow UCS-2 or UTF-16 encoding only, and they remain unchanged.

UTF-8 与 UTF-16 的存储差异Storage differences between UTF-8 and UTF-16

Unicode 联盟为每个字符都分配一个唯一码位(介于 000000-10FFFF 之间的值)。The Unicode Consortium allocates to each character a unique code point, which is a value in the range 000000–10FFFF. 使用 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 时,UTF-8 和 UTF-16 编码都可用来表示整个范围:With SQL Server 2019 (15.x)SQL Server 2019 (15.x), both UTF-8 and UTF-16 encodings are available to represent the full range:

  • 如果使用 UTF-8 编码,ASCII 范围(000000-00007F)内的字符需要 1 个字节,介于 000080 和 0007FF、000800 和 00FFFF 以及 0010000 和 0010FFFF 之间的码位分别需要 2、3 和 4 个字节。With UTF-8 encoding, characters in the ASCII range (000000–00007F) require 1 byte, code points 000080–0007FF require 2 bytes, code points 000800–00FFFF require 3 bytes, and code points 0010000–0010FFFF require 4 bytes.
  • 如果使用 UTF-16 编码,介于 000000 和 00FFFF 以及 0010000 和 0010FFFF 之间的码位分别需要 2 和 4 个字节。With UTF-16 encoding, code points 000000–00FFFF require 2 bytes, and code points 0010000–0010FFFF require 4 bytes.

下表列出了各个字符范围和编码类型的编码存储字节:The following table lists the encoding storage bytes for each character range and encoding type:

代码范围(十六进制)Code range (hexadecimal) 代码范围(十进制)Code range (decimal) 使用 UTF-8 时的存储字节1Storage bytes1 with UTF-8 使用 UTF-16 时的存储字节1Storage bytes1 with UTF-16
000000–00007F000000–00007F 0–1270–127 11 22
000080–00009F000080–00009F
0000A0–0003FF0000A0–0003FF
000400–0007FF000400–0007FF
128–159128–159
160–1,023160–1,023
1,024–2,0471,024–2,047
22 22
000800–003FFF000800–003FFF
004000–00FFFF004000–00FFFF
2,048–16,3832,048–16,383
16,384–65,53516,384–65,535
33 22
010000–03FFFF2010000–03FFFF2

040000–10FFFF2040000–10FFFF2
65,536–262,143265,536–262,1432

262,144–1,114,1112262,144–1,114,1112
44 44

1 存储字节是指编码字节长度,而不是数据类型在磁盘上的存储大小。1 Storage bytes refers to the encoded byte length, not the data-type on-disk storage size. 若要详细了解磁盘上的存储大小,请参阅 nchar 和 nvarchar,以及 char 和 varcharFor more information about on-disk storage sizes, see nchar and nvarchar and char and varchar.

2增补字符的码位范围。2 The code point range for supplementary characters.

提示

通常认为,在 CHAR(n) 和 VARCHAR(n) 或在 NCHAR(n) 和 NVARCHAR(n) 中,n 定义字符数 。It's common to think, in CHAR(n) and VARCHAR(n) or in NCHAR(n) and NVARCHAR(n), that n defines the number of characters. 这是因为在示例 CHAR(10) 列中,可以使用排序规则(如 Latin1_General_100_CI_AI)存储在 0-127 范围内的 10 ASCII 字符,因为此范围内的每个字符仅使用 1 个字节。This is because, in the example of a CHAR(10) column, 10 ASCII characters in the range 0–127 can be stored by using a collation such as Latin1_General_100_CI_AI, because each character in this range uses only 1 byte.

但是,在 CHAR(n) 和 VARCHAR(n) 中,n 以字节数 (0-8,000) 定义字符串大小,而在 NCHAR(n) 和 NVARCHAR(n) 中,n 以字节对 (0-4,000) 定义字符串大小 。However, in CHAR(n) and VARCHAR(n), n defines the string size in bytes (0–8,000), and in NCHAR(n) and NVARCHAR(n), n defines the string size in byte-pairs (0–4,000). n 不会定义可存储的字符数。n never defines numbers of characters that can be stored.

如你所见,选择适当的 Unicode 编码和数据类型可以节省大量存储或增加当前存储占用,具体视使用的字符集而定。As you've just seen, choosing the appropriate Unicode encoding and data type might give you significant storage savings or increase your current storage footprint, depending on the character set in use. 例如,如果使用启用了 UTF-8 的拉丁语排序规则(如 Latin1_General_100_CI_AI_SC_UTF8),则 CHAR(10) 列可存储 10 个字节,并且可保留 0-127 范围内的 10 ASCII 字符。For example, when you use a Latin collation that's UTF-8 enabled, such as Latin1_General_100_CI_AI_SC_UTF8, a CHAR(10) column stores 10 bytes and can hold 10 ASCII characters in the range 0–127. 但只可保留 5 个 128-2047 范围内的字符和 3 个 2048-65535 范围内的字符。But it can hold only 5 characters in the range 128–2047 and only 3 characters in the range 2048–65535. 相比之下,由于 NCHAR(10) 列存储 10 个字节对(20 个字节),因此该列可保留 10 个 0-65535 范围内的字符。By comparison, because a NCHAR(10) column stores 10 byte-pairs (20 bytes), it can hold 10 characters in the range 0–65535.

在选择是要将 UTF-8 编码还是 UTF-16 编码用于数据库或列前,请先考虑要存储的字符串数据的分布情况:Before you choose whether to use UTF-8 or UTF-16 encoding for a database or column, consider the distribution of string data that will be stored:

  • 如果它主要在 ASCII 范围 0-127 内(如英语),使用 UTF-8 和 UTF-16 时每个字符分别需要 1 个和 2 个字节。If it's mostly in the ASCII range 0–127 (such as English), each character requires 1 byte with UTF-8 and 2 bytes with UTF-16. UTF-8 具有存储优势。Using UTF-8 provides storage benefits. 如果使用已启用 UTF-8 的排序规则将包含在 0-127 范围内的 ASCII 字符的现有列数据类型从 NCHAR(10) 更改为 CHAR(10),则会减少 50% 的存储需求。Changing an existing column data type with ASCII characters in the range 0–127 from NCHAR(10) to CHAR(10), and using an UTF-8 enabled collation, translates into a 50 percent reduction in storage requirements. 之所以会有这种减少是因为,NCHAR(10) 需要 20 个字节进行存储,而 CHAR(10) 相比则需要 10 个字节用于相同的 Unicode 字符串表示形式。This reduction is because NCHAR(10) requires 20 bytes for storage, compared with CHAR(10), which requires 10 bytes for the same Unicode string representation.
  • 如果超出 ASCII 范围(几乎所有拉丁字母语言以及希腊语、西里尔文、科普特语、亚美尼亚语、希伯来语、阿拉伯语、叙利亚语、它拿语和西非书面文),使用 UTF-8 和 UTF-16 时每个字符都需要 2 个字节。Above the ASCII range, almost all Latin-based script, and Greek, Cyrillic, Coptic, Armenian, Hebrew, Arabic, Syriac, Tāna, and N’Ko, require 2 bytes per character in both UTF-8 and UTF-16. 在这种情况下,可比较的数据类型(例如,char 与 nchar 之间)没有显著的存储差异。In these cases, there aren't significant storage differences for comparable data types (for example, between using char or nchar).
  • 如果它主要是东亚语言(如韩语、中文和日语),使用 UTF-8 和 UTF-16 时每个字符分别需要 3 个和 2 个字节。If it's mostly East Asian script (such as Korean, Chinese, and Japanese), each character requires 3 bytes with UTF-8 and 2 bytes with UTF-16. UTF-16 具有存储优势。Using UTF-16 provides storage benefits.
  • 使用 UTF-8 和 UTF-16 时,介于 010000 和 10FFFF 范围内的字符都需要 4 个字节。Characters in the range 010000–10FFFF require 4 bytes in both UTF-8 and UTF-16. 在这种情况下,可比较的数据类型(例如,char 与 nchar 之间)没有存储差异。In these cases, there aren't storage differences for comparable data types (for example, between using char or nchar).

有关其他注意事项,请参阅编写国际化 Transact-SQL 语句For other considerations, see Write International Transact-SQL Statements.

转换为 UTF-8Converting to UTF-8

因为在 CHAR(n) 和 VARCHAR(n) 或在 NCHAR(n) 和 NVARCHAR(n) 中,n 定义字节存储大小,而不定义可以存储的字符数,所以确定必须转换的数据类型大小很重要,这可以避免数据被截断 。Because in CHAR(n) and VARCHAR(n) or in NCHAR(n) and NVARCHAR(n), the n defines the byte storage size, not the number of characters that can be stored, it's important to determine the data type size you must convert to, in order to avoid data truncation.

例如,考虑定义为 NVARCHAR(100) 的列,该列存储了 180 个字节的日语字符。For example, consider a column defined as NVARCHAR(100) that stores 180 bytes of Japanese characters. 在本示例中,当前使用 UCS-2 或 UTF-16 对列数据进行编码,每个字符使用 2 个字节。In this example, the column data is currently encoded using UCS-2 or UTF-16, which uses 2 bytes per character. 将列类型转换为 VARCHAR(200) 不足以防止数据被截断,因为新的数据类型只能存储 200 个字节,而使用 UTF-8 编码时,日语字符需要 3 个字节。Converting the column type to VARCHAR(200) is not enough to prevent data truncation, because the new data type can only store 200 bytes, but Japanese characters require 3 bytes when encoded in UTF-8. 因此,必须将列定义为 VARCHAR(270),以避免由于数据截断而丢失数据。So the column must be defined as VARCHAR(270) to avoid data loss through data truncation.

因此,在将现有数据转换为 UTF-8 之前,需要事先知道列定义的预计字节大小,并相应地调整新数据类型的大小。Therefore, it's required to know in advance what's the projected byte size for the column definition before converting existing data to UTF-8, and adjust the new data type size accordingly. 请参阅数据示例 GitHub 中的 Transact-SQLTransact-SQL 脚本或 SQL 笔记本,其中使用 DATALENGTH 函数和 COLLATE 语句来确定现有数据库中 UTF-8 转换操作的正确数据长度要求。Refer to the Transact-SQLTransact-SQL script or the SQL Notebook in the Data Samples GitHub, which use the DATALENGTH function and the COLLATE statement to determine the correct data length requirements for UTF-8 conversion operations in an existing database.

要更改现有表中的列排序规则和数据类型,请使用设置或更改列排序规则中所述的一种方法。To change the column collation and data type in an existing table, use one of the methods described in Set or Change the Column Collation.

要更改数据库排序规则(默认允许新对象继承数据库排序规则)或更改服务器排序规则(默认允许新数据库继承系统排序规则),请参阅本文的相关任务部分。To change the database collation, allowing new objects to inherit the database collation by default, or to change the server collation, allowing new databases to inherit the system collation by default, see the Related tasks section of this article.

任务Task 主题Topic
介绍如何设置或更改 SQL Server 实例的排序规则。Describes how to set or change the collation of the instance of SQL Server. 请注意,更改服务器排序规则不会更改现有数据库的排序规则。Note that changing the server collation does not change the collation of existing databases. 设置或更改服务器排序规则Set or Change the Server Collation
介绍如何设置或更改用户数据库的排序规则。Describes how to set or change the collation of a user database. 请注意,更改数据库排序规则不会更改现有表列的排序规则。Note that changing a database collation does not change the collation of existing table columns. 设置或更改数据库排序规则Set or Change the Database Collation
介绍如何设置或更改数据库中的列的排序规则Describes how to set or change the collation of a column in the database 设置或更改列排序规则Set or Change the Column Collation
介绍如何返回服务器级、数据库级或列级的排序规则信息Describes how to return collation information at the server, database, or column level 查看排序规则信息View Collation Information
介绍如何编写更易于在不同语言之间移植或更轻松地支持多种语言的 Transact-SQL 语句Describes how to write Transact-SQL statements that are more portable from one language to another, or support multiple languages more easily 编写国际化 Transact-SQL 语句Write International Transact-SQL Statements
介绍如何更改有关日期、时间和货币数据的使用和显示方式的错误消息和首选项的语言Describes how to change the language of error messages and preferences for how date, time, and currency data is used and displayed 设置会话语言Set a Session Language

有关详细信息,请参阅下列相关内容:For more information, see the following related content:

另请参阅See also

包含数据库的排序规则 Contained Database Collations
创建全文索引时选择语言 Choose a Language When Creating a Full-Text Index
sys.fn_helpcollations (Transact-SQL) sys.fn_helpcollations (Transact-SQL)
单字节和多字节字符集Single-Byte and Multibyte Character Sets