定序與 Unicode 支援Collation and Unicode support

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure 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 DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure 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. 與字元資料類型 (例如 charvarchar) 搭配使用的定序會指示字碼頁,以及可針對該資料類型表示的對應字元。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


定序指定位元模式,代表資料集中的每一個字元。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_140Japanese_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) 讓 UTF-8 編碼資料儲存至 SQL ServerSQL ServerEnables 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. 例如,Latin_1_General_BINJapanese_BIN 用於 Unicode 資料時會產生相同的排序結果。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. 若要完全利用 SQL ServerSQL Server 的功能,請變更預設安裝設定以使用 Windows 定序。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_SCFor 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) 0x047a0x047c 0x047a0x047c 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:


若要查詢伺服器的所有可用定序,請使用下列 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 DATABASECOLLATE 子句或 ALTER DATABASE 陳述式來指定預設資料庫定序。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. 如需詳細資訊,請參閱 COLLATE (Transact-SQL)For more information, see COLLATE (Transact-SQL).


Azure SQL DatabaseAzure SQL Database 上資料庫建立後,您就無法變更資料庫定序。You can't change the collation after the database has been created on Azure SQL DatabaseAzure 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:



改變資料庫層級定序不會影響資料行層級或運算式層級的定序。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:


運算式層級定序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;    


地區設定是一組與某個地點或文化特性建立關聯的資訊。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 指派的地區設定識別碼For 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 字碼頁一般稱為「字元集」或 charsetA 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. 由於 Unicode 主要設計為涵蓋世界上所有語言的字元,因此您不需要使用不同字碼頁來處理不同的字元集。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 XP 作業系統上設定用戶端字碼頁,請使用 [控制台] 中的 [地區設定]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 Server (SQL Server 2005 (9.x)SQL Server 2005 (9.x) 及更新版本),則請使用 Unicode 資料類型 (ncharnvarcharntext),而非 Unicode 資料類型 (charvarchartext)。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 EngineDatabase Engine 可以使用 UCS-2 表示最多 65,535 個字元,或是在使用增補字元的情況下,使用完整的 Unicode 範圍 (1,114,111 個字元)。For Unicode data types, the Database EngineDatabase 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 資料類型 (charvarchar) 會變成使用 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 資料類型 (NcharNvarcharNtext) 的行為,它們會繼續使用 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 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 Server (SQL 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_UTF8CTP 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:

  • Unicode 用戶端,其使用 OLE DB 和開放式資料庫連接 (ODBC) 3.7 版或更新版本。Unicode clients that use OLE DB and Open Database Connectivity (ODBC) version 3.7 or later.
  • 非 Unicode 用戶端,其使用 DB-Library 和 ODBC 3.6 版或較舊版本。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 Data Objects (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 Consortium 會為每個字元配置唯一的字碼指碼,其值介於 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 Consortium 已建立其它 16 個字元「平面」,每個平面的大小都與 BMP 相同。But the Unicode Consortium has established 16 additional "planes" of characters, each the same size as the BMP. 此定義可讓 Unicode 具備表示 1,114,112 個字元的潛力 (即 216 * 17 個字元),介於字碼指碼範圍 000000 到 10FFFF 中。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 的字元需要二至四個連續的 8 位元字組 (UTF-8) 或兩個連續的 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 Standard (Unicode 標準)。For more information about supplementary characters, surrogates, and surrogate pairs, refer to the Unicode Standard.

SQL ServerSQL Server 提供 ncharnvarchar 等資料類型,用來儲存 BMP 範圍 (000000 到 00FFFF) 中的 Unicode 資料,Database EngineDatabase Engine 會使用 UCS-2 來進行編碼。provides data types such as nchar and nvarchar to store Unicode data in the BMP range (000000–00FFFF), which the Database EngineDatabase Engine encodes using UCS-2.

SQL Server 2012 (11.x)SQL Server 2012 (11.x) 引進的全新系列增補字元 (_SC) 定序可以與 ncharnvarcharsql_variant 資料類型搭配使用,以代表完整的 Unicode 字元範圍 (000000 到 10FFFF)。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_SCJapanese_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) 的 charvarchar 資料類型。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


將 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.





這些函數會將每個 代理字組視為單一字碼指標,且如預期方式運作。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 傳回對應至所指定 Unicode 字碼指標值 (在範圍 0 到 0x10FFFF 中) 的字元。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. 若為更高的值,則傳回對應的 Surrogate。For higher values, the corresponding surrogate is returned. 高於 0xFFFF 的值會傳回 NULL 而非對應的 Surrogate。A value higher than 0xFFFF returns NULL instead of the corresponding surrogate.
UNICODEUNICODE 傳回 UTF-16 字碼指標 在範圍 0 到 0x10FFFF 中)。Returns a UTF-16 code point in the range 0–0x10FFFF. 傳回 UCS-2 字碼指標 (在範圍 0 到 0x10FFFF 中)。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. 當 GB18030 編碼的字元儲存在伺服器後,任何後續作業都會將其視為 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 ServerSQL Server 所提供的 SC 定序來改善搜尋和排序。If the data includes supplementary characters (surrogate pairs), you can use the SC collations that are available in SQL ServerSQL Server 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 Form 控制項具有複雜字集的功能。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 Database EngineSQL Server Database EngineTo list these collations, you can query the SQL Server Database EngineSQL 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 EngineDatabase Engine 索引、記憶體最佳化資料表、資料行存放區索引和原生編譯的模組都支援這些定序。These collations are supported in Database EngineDatabase 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 允許用於 charvarchar 資料類型,且會在您建立物件定序或將其變更為具有 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_SCLATIN1_GENERAL_100_CI_AS_SC_UTF8One 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). ncharnvarchar 資料類型只允許 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 Consortium 會為每個字元配置唯一的字碼指碼,其值介於 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 需要 2 個位元組,字碼指碼 000800–00FFFF 需要 3 個位元組,字碼指碼 0010000–0010FFFF 需要 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 需要 2 個位元組,字碼指碼 0010000–0010FFFF 需要 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
22 22
33 22


44 44

1「儲存體位元組」意指編碼的位元組長度,而非資料類型在磁碟上的儲存大小。1 Storage bytes refers to the encoded byte length, not the data-type on-disk storage size. 如需磁碟上儲存大小的詳細資訊,請參閱 nchar 與 nvarcharchar 與 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. 但在範圍為 128-2047 時只能保留 5 個字元,而在範圍為 2048-65535 時只能保留 3 個字元。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 個位元組),因此可以保留範圍 0-65535 內的 10 個字元。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 時每個字元將需要 1 個位元組,UTF-16 則需要 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. 將具有 ASCII 字元 (範圍 0-127) 的現有資料行資料類型從 NCHAR(10) 變更為 CHAR(10),並使用啟用 UTF-8 的定序,會使儲存體需求減少 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) 針對相同的 Unicode 字串表示只需要 10 個位元組。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. 在這些案例中,可比較的資料類型 (例如使用 charnchar) 沒有明顯的儲存差異。In these cases, there aren't significant storage differences for comparable data types (for example, between using char or nchar).
  • 若其內容大部分是東亞字集 (例如韓文、中文和日文),則在 UTF-8 中每個字元都需要 3 個位元組,UTF-16 中則為 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.
  • 範圍 010000 至 10FFFF 的字元在 UTF-8 和 UTF-16 中都需要 4 個位元組。Characters in the range 010000–10FFFF require 4 bytes in both UTF-8 and UTF-16. 在這些案例中,可比較的資料類型 (例如使用 charnchar) 沒有儲存體差異。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 Notebook,該範例使用 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.

TaskTask 主題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