定序優先順序

  • 發行項

適用於：SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric 中的 SQL 分析端點Microsoft Fabric 中的倉儲

定序優先順序，也稱為定序強制規則，會決定下列兩個結果：

  • 評估得出字元字串之運算式最終結果的定序。

  • 輸入是字元字串而不傳回字元字串的區分定序運算子 (如 LIKE 和 IN) 所用的定序。

定序優先順序規則只適用於字元字串資料類型：char、varchar、text、nchar、nvarchar 和 ntext。 其他資料類型的物件不參與定序評估。

定序標籤

下表列出和描述識別所有物件之定序的四個類別目錄。 每個類別目錄的名稱都稱為定序標籤。

定序標籤 物件類型
Coercible-default 任何 Transact-SQL 字元字串變數、參數、常值，或目錄內建函數的輸出，或輸入不是字串卻會產生字串輸出的內建函數。

如果物件宣告在使用者自訂函數、預存程序或觸發程序中，便會將建立了函數、預存程序或觸發程序之資料庫的預設定序指派給物件。 如果物件宣告在批次中，便會將連接目前資料庫的預設定序指派給物件。
Implicit X 資料行參考。 運算式 (X) 的定序取自定義給資料表或檢視中之資料行的定序。

即使數據行在 或 CREATE VIEW 語句中使用 COLLATECREATE TABLE 子句明確指派定序，數據行參考也會分類為隱含。
Explicit X 在運算式中，利用 COLLATE 子句明確轉換成特定定序 (X) 的運算式。
No-collation 指出運算式的值是兩個含有隱含定序標籤衝突定序字串之間的運算結果。 運算式結果定義為無定序。

定序規則

只參考單一字元字串物件的簡單運算式，其定序標籤是被參考物件的定序標籤。

參考兩個含有相同定序標籤的運算元運算式之複雜運算式，其定序標籤是運算元運算式的定序標籤。

參考兩個含不同定序的運算元運算式之複雜運算式，其最終結果的定序標籤是以下列規則為基礎：

  • 明確優先於隱含。 隱含優先於強制預設：

    明確 > 隱含 > 強制預設

  • 組合兩個已指派了不同定序的明確運算式，會產生一則錯誤：

    明確 X + 明確 Y = 錯誤

  • 組合兩個含不同定序的隱含運算式會產生無定序的結果：

    隱含 X + 隱含 Y = 無定序

  • 組合無定序運算式和任何標籤的運算式，除了明確定序 (請參閱下列規則) 之外，會產生含無定序標籤的結果：

    無定序 + 任何項目 = 無定序

  • 組合無定序運算式和明確定序運算式，會產生含明確標籤的運算式：

    無定序 + 明確 X = 明確

下表彙總這些規則。

運算元強制標籤 明確 X 隱含 X 強制預設 無定序
Explicit Y 產生錯誤 結果是明確 Y 結果是明確 Y 結果是明確 Y
Implicit Y 結果是明確 X 結果是無定序 結果是隱含 Y 結果是無定序
Coercible-default 結果是明確 X 結果是隱含 X 結果是強制預設 結果是無定序
No-collation 結果是明確 X 結果是無定序 結果是無定序 結果是無定序

定序優先順序也適用下列其他規則：

  • 已是明確運算式的運算式，不能有多個 COLLATE 子句。 例如，下列 WHERE 子句無效，因為 COLLATE 子句的指定運算式已是明確運算式：

    WHERE ColumnA = ( 'abc' COLLATE French_CI_AS) COLLATE French_CS_AS

  • 不允許進行 text 資料類型的字碼頁轉換。 若有不同的字碼頁，您便不能將 text 運算式的定序轉換成另外一個。 當右文字運算元的定序，其字碼頁與左文字運算元不同時，指派運算子便不能指派值。

定序優先順序是在資料類型轉換之後所決定的。 得到結果定序的運算元，可能不是提供最終結果的資料類型之運算元。 例如，請考量下列批次：

CREATE TABLE TestTab  
   (PrimaryKey int PRIMARY KEY,  
    CharCol char(10) COLLATE French_CI_AS  
   )  
  
SELECT *  
FROM TestTab  
WHERE CharCol LIKE N'abc'

注意

網狀架構倉儲不支持 nvarchar 數據類型，但本文中的大多數範例都適用於使用 UTF-8 和 nvarchar 的 varchar，因此除非另有說明，否則仍適用於網狀架構倉儲。

簡單運算式 N'abc' 的 Unicode 資料類型，資料類型優先順序較高。 因此，結果運算式會將 Unicode 資料類型指派給 N'abc'。 不過，CharCol 運算式會有隱含定序標籤，N'abc' 會有較低的強制預設強制標籤。 因此，所用的定序是 French_CI_AS 的 CharCol 定序。

定序規則的範例

下列範例會顯示定序規則的運作方式。 若要執行這些範例，請建立下列測試資料表。

USE tempdb;  
GO  
  
CREATE TABLE TestTab (  
   id int,   
   GreekCol nvarchar(10) collate greek_ci_as,   
   LatinCol nvarchar(10) collate latin1_general_cs_as  
   )  
INSERT TestTab VALUES (1, N'A', N'a');  
GO

定序衝突和錯誤

下列查詢中的述詞發生定序衝突，產生錯誤。

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol;

以下為結果集。

Msg 448, Level 16, State 9, Line 2  
Cannot resolve collation conflict between 'Latin1_General_CS_AS' and 'Greek_CI_AS' in equal to operation.

明確標籤與隱含標籤

下列查詢中的述詞是以 greek_ci_as 定序來評估的，因為右側運算式有明確的標籤。 它的優先順序比左側運算式的隱含標籤高。

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol COLLATE greek_ci_as;

以下為結果集。

id          GreekCol             LatinCol  
----------- -------------------- --------------------  
          1 A                    a  
  
(1 row affected)

無定序標籤

注意

由於UTF-8定序中 nvarchar和 varchar 的行為差異，此範例不適用於網狀架構倉儲。

下列查詢中的 CASE 運算式具備無定序標籤；因此，它們不能出現在選取清單中，也不能由區分定序的運算子來處理。 不過，任何不區分定序的運算子都能夠處理這些運算式。

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END)   
FROM TestTab;

以下為結果集。

Msg 451, Level 16, State 1, Line 1  
Cannot resolve collation conflict for column 1 in SELECT statement.  

SELECT PATINDEX((CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END), 'a')  
FROM TestTab;

以下為結果集。

Msg 446, Level 16, State 9, Server LEIH2, Line 1  
Cannot resolve collation conflict for patindex operation.  

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE Latin1_General_CI_AS   
FROM TestTab;

以下為結果集。

--------------------  
a  
  
(1 row affected)

區分定序和定序不區分

運算子和函數會區分定序或不區分定序。

  • 區分定序：這表示指定 No-collation 操作數是編譯時期錯誤。 運算式結果不能無定序。
  • 定序不區分：這表示操作數和結果可以是無定序。

運算子和定序

比較運算子和 MAX、 MIN、 BETWEEN、 LIKE和 IN 運算子會區分定序。 運算子所用的字串會指派優先順序較高之運算元的定序標籤。 語句 UNION 也會區分定序，而且所有字串操作數和最終結果都會指派優先順序最高的操作數定序。 操作數和結果的 UNION 定序優先順序會依數據行評估數據行。

指派運算子不區塊定序，右側運算式會轉換成左側的定序。

字串串連運算子區分定序，兩個字串運算元和結果都會指派定序優先順序最高之運算元的定序標籤。 UNION ALL和 CASE 語句不區分定序，而且所有字串操作數和最終結果都會指派具有最高優先順序的操作數定序標籤。 操作數和結果的 UNION ALL 定序優先順序會依數據行評估數據行。

函式和定序

CAST、 CONVERT和 COLLATE 函式會區分 char、varchar 和 text 數據類型的定序。 如果和 CONVERT 函式的CAST輸入和輸出是字元字串，則輸出字串具有輸入字串的定序標籤。 如果輸入不是字元字串，則輸出字串為 Coercible-default，並指派連接目前資料庫的定序，或包含使用者定義函數、預存程式或觸發程式的資料庫，其中 CAST 會參考 或 CONVERT 。

對於傳回字串但不接受字串輸入的內建函式，結果字串為 Coercible-default。 結果字串會指派目前資料庫的定序，或包含使用者定義函數、預存程式或觸發程式的資料庫定序，以參考函式。

下列函數會區分定序，它們的輸出字串會有輸入字串的定序標籤：

CHARINDEX

REPLACE

DIFFERENCE

REVERSE

ISNUMERIC

RIGHT

LEFT

SOUNDEX

LEN

STUFF

LOWER

SUBSTRING

PATINDEX

UPPER

相關內容