撰寫國際通用的 Transact-SQL 陳述式Write International Transact-SQL Statements

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

如果遵循下列的指導方針,使用 Transact-SQLTransact-SQL 陳述式的資料庫與資料庫應用程式將更能從一個語言移植至另一個語言,或可支援多種語言:Databases and database applications that use Transact-SQLTransact-SQL statements will become more portable from one language to another, or will support multiple languages, if the following guidelines are followed:

  • SQL Server 2019 預覽SQL Server 2019 preview 開始,使用:Starting with SQL Server 2019 預覽SQL Server 2019 preview, use either:

    • charvarcharvarchar(max) 資料類型 (使用啟用 UTF-8 的定序)。The char, varchar, and varchar(max) data types with a UTF-8 enabled collation.
    • charvarcharvarchar(max) 資料類型 (使用啟用增補字元的定序)。The nchar, nvarchar, and nvarchar(max) data types with supplementary character enabled collation.

    這可避免字碼頁轉換問題。This avoids code page conversion issues. 如需其它考量事項,請參閱 UTF-8 和 UTF-16 間的儲存差異For other considerations, see Storage differences between UTF-8 and UTF-16.

  • 最多到 SQL Server 2017 (14.x)SQL Server 2017 (14.x),使用 ncharnvarcharnvarchar(max) 來取代所有使用的 charvarcharvarchar(max) 資料類型。Up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), replace all uses of the char, varchar, and varchar(max) data types with nchar, nvarchar, and nvarchar(max). 這可避免字碼頁轉換問題。This avoids code page conversion issues. 如需詳細資訊,請參閱 Collation and Unicode SupportFor more information, see Collation and Unicode Support.

    重要

    text 資料類型已淘汰,不應用於新的開發工作。The text data type is deprecated and should not be used in new development work. 計劃將 text 資料轉換為 varchar(max)Plan to convert text data to varchar(max).

  • 您在執行月份和週中日的比較和運算時,請使用數值的日期部分,而不要使用名稱字串。When doing month and day-of-week comparisons and operations, use the numeric date parts instead of the name strings. 不同的語言設定會傳回不同的月份和週中日名稱。Different language settings return different names for the months and weekdays. 例如,語言設定為「英文 (美國)」時,DATENAME(MONTH,GETDATE()) 會傳回 May;當語言設定為「德文」時,會傳回 Mai;而當語言設定為「法文」時,會傳回 maiFor example, DATENAME(MONTH,GETDATE()) returns May when the language is set to U.S. English, returns Mai when the language is set to German, and returns mai when the language is set to French. 請改用如 DATEPART 的函式,使用數字月份而非名稱。Instead, use a function such as DATEPART that uses the number of the month instead of the name. 當您要將結果集顯示給使用者時,請使用 DATEPART 名稱,因為日期名稱通常比數值表示法來得有意義。Use the DATEPART names when you build result sets to be displayed to a user, because the date names are frequently more meaningful than a numeric representation. 但是,不要根據特定語言的顯示名稱來撰寫任何程式碼邏輯。However, don't code any logic that depends on the displayed names being from a specific language.

  • 當您在比較或輸入至 INSERT 或 UPDATE 陳述式中指定日期時,請使用所有語言設定都作相同解譯的常數:When you specify dates in comparisons or for input to INSERT or UPDATE statements, use constants that are interpreted the same way for all language settings:

    • ADO、OLE DB 和 ODBC 應用程式應該使用以下形式的 ODBC 時間戳記、日期和時間逸出子句:ADO, OLE DB, and ODBC applications should use the ODBC timestamp, date, and time escape clauses of:

      { ts' yyyy - mm - dd hh : mm : ss [ . fff] '} 例如: { ts'1998-09-24 10:02:20'}{ ts' yyyy - mm - dd hh : mm : ss [.fff] '} such as: { ts'1998-09-24 10:02:20'}

      { d' yyyy - mm - dd '} 例如: { d'1998-09-24'}{ d' yyyy - mm - dd '} such as: { d'1998-09-24'}

      { t' hh : mm : ss '} 例如: { t'10:02:20'}{ t' hh : mm : ss '} such as: { t'10:02:20'}

    • 使用其他 API 或 Transact-SQLTransact-SQL 指令碼、預存程序和觸發程序的應用程式,應該使用未分隔的數值字串。Applications that use other APIs, or Transact-SQLTransact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. 例如 yyyymmdd 為 19980924。For example, yyyymmdd as 19980924.

    • 使用其他 API 的應用程式,或 Transact-SQLTransact-SQL 指令碼、預存程序及觸發程序,都應該針對 timedatesmalldatedatetimedatetime2datetimeoffset 資料類型與字元字串資料類型之間的所有轉換,使用具有明確樣式參數的 CONVERT 陳述式。Applications that use other APIs, or Transact-SQLTransact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types. 例如,下列陳述式在所有日期格式連接設定下的解譯都是一樣的:For example, the following statement is interpreted in the same way for all language or date format connection settings:

      SELECT *  
      FROM AdventureWorks2012.Sales.SalesOrderHeader  
      WHERE OrderDate = CONVERT(DATETIME, '20060719', 101)  
      

另請參閱See also

CAST 和 CONVERT (Transact-SQL) CAST and CONVERT (Transact-SQL)
DATEPART (Transact-SQL) DATEPART (Transact-SQL)
定序與 Unicode 支援Collation and Unicode Support