Precedenza delle regole di confronto

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureAzure Synapse AnalyticsPiattaforma di strumenti analitici (PDW)Endpoint di analisi SQL in Microsoft FabricWarehouse in Microsoft Fabric

La precedenza delle regole di confronto, nota anche come regole di coercizione delle regole di confronto, determina i due risultati seguenti:

  • Le regole di confronto del risultato finale di un'espressione che restituisce una stringa di caratteri.

  • Le regole di confronto utilizzate dagli operatori sensibili alle regole di confronto che utilizzano input di stringhe di caratteri ma non restituiscono stringhe di caratteri, ad esempio gli operatori LIKE e IN.

Le regole sulla precedenza delle regole di confronto vengono applicate solo alle stringhe di caratteri di tipo char, varchar, text, nchar, nvarchar e ntext. Gli oggetti con altri tipi di dati non vengono inclusi nelle valutazioni delle regole di confronto.

Etichette delle regole di confronto

Nella tabella seguente sono elencate e descritte le quattro categorie in base alle quali vengono classificate le regole di confronto di tutti gli oggetti. Il nome di ogni categoria corrisponde all'etichetta per le regole di confronto.

Etichetta per le regole di confronto Tipi di oggetto
Coercible-default Qualsiasi variabile, parametro, valore letterale di stringhe di caratteri Transact-SQL oppure l'output di una funzione di catalogo predefinita o una funzione predefinita che non accetta input di stringhe ma restituisce stringhe.

A un oggetto dichiarato in una funzione definita dall'utente, in una stored procedure o in un trigger vengono assegnate le regole di confronto predefinite del database in cui si crea la funzione, la stored procedure o il trigger. A un oggetto dichiarato in un batch vengono assegnate le regole di confronto predefinite del database corrente per la connessione.
Implicit X Riferimento di colonna. Le regole di confronto dell'espressione (X) derivano dalle regole di confronto definite per la colonna della tabella o vista.

Anche se alla colonna sono state assegnate in modo esplicito regole di confronto usando una COLLATE clausola nell'istruzione CREATE TABLE o CREATE VIEW , il riferimento alla colonna viene classificato come implicito.
Explicit X Espressione convertita in modo esplicito in regole di confronto specifiche (X) tramite una clausola COLLATE nell'espressione.
No-collation Indica che il valore di un'espressione è il risultato di un'operazione tra due stringhe con regole di confronto di tipo Implicit in conflitto. Tale espressione viene considerata priva di regole di confronto.

Regole di confronto

L'etichetta per le regole di confronto di un'espressione semplice che fa riferimento a un solo oggetto stringa di caratteri corrisponde all'etichetta dell'oggetto a cui viene fatto riferimento.

L'etichetta per le regole di confronto di un'espressione complessa che fa riferimento a due espressioni operando aventi la stessa etichetta per le regole di confronto corrisponde all'etichetta di tali espressioni.

L'etichetta per le regole di confronto del risultato finale di un'espressione complessa che fa riferimento a due espressioni operando con regole di confronto diverse viene determinata in base alle regole seguenti:

  • L'etichetta Explicit è prioritaria rispetto all'etichetta Implicit, che a sua volta è prioritaria rispetto all'etichetta Coercible-default.

    Explicit > Implicit > Coercible-default

  • Se si combinano due espressioni di tipo Explicit a cui sono assegnate regole di confronto diverse, viene generato un errore.

    Explicit X + Explicit Y = Errore

  • Se si combinano due espressioni di tipo Implicit a cui sono assegnate regole di confronto diverse, si ottiene l'etichetta per le regole di confronto No-collation.

    Implicit X + Implicit Y = No-collation

  • Se si combina un'espressione di tipo No-collation e un'espressione con qualsiasi etichetta, ad eccezione di Explicit X (vedi punto successivo), al risultato viene associata l'etichetta per le regole di confronto No-collation.

    No-collation + qualsiasi etichetta = No-collation

  • Se si combina un'espressione di tipo No-collation e un'espressione con regole di confronto di tipo Explicit, si ottiene un'espressione con l'etichetta Explicit.

    No-collation + Explicit X = Explicit

Nella tabella seguente vengono riepilogate le regole descritte in precedenza.

Etichetta di coercizione dell'operando Explicit X Implicit X Coercible-default No-collation
Explicit Y Genera un errore Il risultato è Explicit Y Il risultato è Explicit Y Il risultato è Explicit Y
Implicit Y Il risultato è Explicit X Il risultato è No-collation Il risultato è Implicit Y Il risultato è No-collation
Coercible-default Il risultato è Explicit X Il risultato è Implicit X Il risultato è Coercible-default Il risultato è No-collation
No-collation Il risultato è Explicit X Il risultato è No-collation Il risultato è No-collation Il risultato è No-collation

Per la precedenza delle regole di confronto vengono applicate inoltre le regole aggiuntive seguenti:

  • Un'espressione che è già esplicita non può includere più clausole COLLATE. La clausola WHERE seguente, ad esempio, non è valida perché viene specificata una clausola COLLATE per un'espressione che è già esplicita.

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

  • Le conversioni tra tabelle codici per il tipo di dati text non sono consentite. Non è possibile eseguire il cast di un'espressione di tipo text tra regole di confronto con tabelle codici diverse. Tramite l'operatore di assegnazione non è possibile assegnare valori se alle regole di confronto dell'operando di testo a destra è associata una tabella codice diversa da quella dell'operando di testo a sinistra.

La precedenza delle regole di confronto viene determinata dopo la conversione dei tipi di dati. L'operando da cui derivano le regole di confronto risultanti può essere diverso dall'operando il cui tipo di dati viene applicato al risultato finale. Si consideri, ad esempio, il batch seguente.

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

Nota

Il tipo di dati nvarchar non è supportato in Fabric Warehouse, ma la maggior parte degli esempi in questo articolo è applicabile sia a varchar che a UTF-8 e nvarchar e quindi rimangono applicabili a Fabric Warehouse, se non diversamente specificato.

Il tipo di dati Unicode dell'espressione semplice N'abc' ha una precedenza più alta a livello di tipo di dati. Nell'espressione risultante il tipo di dati Unicode viene assegnato a N'abc'. L'etichetta delle regole di confronto dell'espressione CharCol è Implicit e N'abc' è associata a un'etichetta di coercizione di livello inferiore, ovvero Coercible-default. Le regole di confronto utilizzate saranno pertanto le regole French_CI_AS di CharCol.

Esempi di regole di confronto

Nell'esempio seguente viene illustrato il funzionamento delle regole di confronto. Per eseguire gli esempi descritti, è necessario creare la tabella di prova seguente.

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

Conflitto e errore delle regole di confronto

Nel predicato della query seguente esiste un conflitto di regole di confronto che genera un errore.

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol;

Questo è il set di risultati.

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.

Etichetta esplicita e etichetta implicita

Il predicato della query seguente viene valutato nelle regole di confronto greek_ci_as perché l'etichetta dell'espressione di destra è Explicit. Questa etichetta ha la priorità sull'etichetta Implicit dell'espressione di sinistra.

SELECT *   
FROM TestTab   
WHERE GreekCol = LatinCol COLLATE greek_ci_as;

Questo è il set di risultati.

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

Etichette senza regole di confronto

Nota

A causa della differenza tra il comportamento di nvarchar e varchar in regole di confronto UTF-8, questo esempio non si applica in Fabric Warehouse.

Le espressioni CASE delle query seguenti sono associate all'etichetta No-collation. Di conseguenza non sono visualizzabili nell'elenco di selezione, né utilizzabili con gli operatori sensibili alle regole di confronto. Le espressioni possono essere tuttavia utilizzate con operatori non sensibili alle regole di confronto.

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

Questo è il set di risultati.

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;

Questo è il set di risultati.

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;

Questo è il set di risultati.

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

Regole di confronto sensibili alle regole di confronto senza distinzione tra regole di confronto

Gli operatori e le funzioni possono essere sensibili alle regole di confronto o meno.

  • Sensibile alle regole di confronto: ciò significa che specificare un operando no-collation è un errore in fase di compilazione. L'espressione risultante non può essere di tipo No-collation.
  • Regole di confronto senza distinzione: ciò significa che gli operandi e il risultato possono essere senza regole di confronto.

Operatori e regole di confronto

Gli operatori di confronto e gli MAXoperatori , MINBETWEEN, LIKEe IN sono sensibili alle regole di confronto. Alla stringa utilizzata dagli operatori viene assegnata l'etichetta per le regole di confronto dell'operando con priorità maggiore. L'istruzione UNION è anche sensibile alle regole di confronto e a tutti gli operandi stringa e al risultato finale vengono assegnate le regole di confronto dell'operando con la precedenza più alta. La precedenza delle regole di confronto dell'operando e del UNION risultato viene valutata in base alla colonna.

L'operatore di assegnazione non è sensibile alle regole di confronto. All'espressione di destra vengono applicate le regole di confronto dell'espressione di sinistra.

L'operatore di concatenazione delle stringhe è sensibile alle regole di confronto. Ai due operandi stringa e al risultato viene assegnata l'etichetta per le regole di confronto dell'operando le cui regole di confronto hanno priorità maggiore. Le UNION ALL istruzioni e CASE sono senza distinzione tra regole di confronto e a tutti gli operandi stringa e ai risultati finali viene assegnata l'etichetta delle regole di confronto dell'operando con la precedenza più alta. La precedenza delle regole di confronto degli operandi e dei UNION ALL risultati viene valutata in base alla colonna.

Funzioni e regole di confronto

Le CASTfunzioni , CONVERTe COLLATE sono sensibili alle regole di confronto per i tipi di dati char, varchar e text . Se l'input e l'output delle CAST funzioni e CONVERT sono stringhe di caratteri, la stringa di output ha l'etichetta delle regole di confronto della stringa di input. Se l'input non è una stringa di caratteri, la stringa di output è Coercible-default e ha assegnato le regole di confronto del database corrente per la connessione oppure il database che contiene la funzione, la stored procedure o il trigger definito dall'utente in cui viene fatto riferimento a CAST o CONVERT .

Per le funzioni predefinite che restituiscono una stringa ma non accettano un input stringa, la stringa di risultato è Coercible-default. Alla stringa di risultato vengono assegnate le regole di confronto del database corrente o le regole di confronto del database che contiene la funzione definita dall'utente, la stored procedure o il trigger a cui fa riferimento la funzione.

Le funzioni elencate di seguito sono sensibili alle regole di confronto e alle stringhe di output corrispondenti è associata l'etichetta per le regole di confronto della stringa di input:

CHARINDEX

REPLACE

DIFFERENCE

REVERSE

ISNUMERIC

RIGHT

LEFT

SOUNDEX

LEN

STUFF

LOWER

SUBSTRING

PATINDEX

UPPER

Contenuto correlato