NVARCHAR memorizza i caratteri non supportati dalla codifica UCS-2 su SQL Server

Question

Secondo la documentazione di SQL Server (e la documentazione legacy ), un nvarcharcampo senza _SCregole di confronto dovrebbe utilizzare l'estensione UCS-2 ENCODING.

A partire da SQL Server 2012 (11.x), quando vengono utilizzate regole di confronto abilitate per i caratteri supplementari (SC), questi tipi di dati archiviano l'intera gamma di dati dei caratteri Unicode e utilizzano la codifica dei caratteri UTF-16. Se vengono specificate regole di confronto non SC, questi tipi di dati memorizzano solo il sottoinsieme di dati di caratteri supportati dalla codifica dei caratteri UCS-2.

Dichiara inoltre che UCS-2 ENCODINGmemorizza solo i caratteri del sottoinsieme supportati da UCS-2. Dalle UCS-2 specifiche di wikipedia :

UCS-2, utilizza un valore di codice [...] singolo compreso tra 0 e 65.535 per ogni carattere e consente esattamente due byte (una parola di 16 bit) per rappresentare quel valore. UCS-2 consente quindi una rappresentazione binaria di ogni punto di codice nel BMP che rappresenta un carattere. UCS-2 non può rappresentare punti di codice al di fuori del BMP.

Quindi, secondo le specifiche sopra, sembra che non sarò in grado di memorizzare un'emoji come: 😍 che ha un valore di 0x1F60D(o 128525 in decimale, molto al di sopra del limite di 65535 di UCS-2). Ma su SQL Server 2008 R2 o SQL Server 2019 (entrambi con l'impostazione predefinita SQL_Latin1_General_CP1_CI_AS COLLATION), su un nvarcharcampo, è perfettamente archiviato e restituito (sebbene non supportato nei confronti con LIKEo =):

SMSS non visualizza correttamente le emoji, ma ecco il valore copiato e incollato dal risultato della query: 😍

Quindi le mie domande sono:

1. Il nvarcharcampo viene davvero utilizzato USC-2su SQL Server 2008 R2 (ho anche testato su SQL Server 2019, con le stesse non _SCregole di confronto e gli stessi risultati)?

2. La documentazione di Microsoft su nchar/ nvarcharfuorviante su "allora questi tipi di dati memorizzano solo il sottoinsieme di dati di caratteri supportati dalla codifica dei caratteri UCS-2"?

3. Fa UCS-2 ENCODINGdi supporto o meno punti di codice al di là di 65535?

4. In che modo SQL Server è stato in grado di archiviare e recuperare correttamente i dati di questo campo, quando non è supportato da UCS-2 ENCODING?

NOTA: le regole di confronto del server sono SQL_Latin1_General_CP1_CI_ASe quelle di campo sono Latin1_General_CS_AS.
NOTA 2: la domanda originale prevedeva test su SQL Server 2008. Ho testato e ottenuto gli stessi risultati su SQL Server 2019, con gli stessi rispettivi COLLATIONs.
NOTA 3: ogni altro personaggio che ho testato, al di fuori UCS-2dell'intervallo supportato, si comporta allo stesso modo. Alcuni sono: 𝕂, 😂, 𨭎, 𝕬, 𝓰

Answer 1

Ci sono diversi chiarimenti da fare qui riguardo agli snippet di documentazione MS inseriti nella domanda, e per il codice di esempio, per le domande stesse e per le dichiarazioni fatte nei commenti sulla domanda. La maggior parte della confusione può essere chiarita, credo, dalle informazioni fornite nel mio post seguente:

Quanti byte per carattere in SQL Server: una guida completamente completa

Per prima cosa (che è l'unico modo in cui può essere, giusto?): Non sto insultando le persone che hanno scritto la documentazione di MS poiché SQL Server da solo è un prodotto enorme e c'è molto da coprire, ecc, ma per il momento (finché non avrò la possibilità di aggiornarlo), si prega di leggere la documentazione "ufficiale" con un senso di cautela. Sono presenti diversi errori relativi a Collations / Unicode.

1. UCS-2 è una codifica che gestisce un sottoinsieme del set di caratteri Unicode. Funziona in unità da 2 byte. Con 2 byte, è possibile codificare valori da 0 a 65535. Questo intervallo di punti di codice è noto come BMP (Basic Multilingual Plane). Il BMP è tutti i caratteri che non sono caratteri supplementari (perché quelli sono supplementari al BMP), ma contiene un insieme di punti di codice che vengono utilizzati esclusivamente per codificare caratteri supplementari in UTF-16 (cioè i 2048 punti di codice surrogati ). Questo è un sottoinsieme completo di UTF-16.

2. UTF-16 è una codifica che gestisce tutto il set di caratteri Unicode. Funziona anche in unità da 2 byte. In effetti, non c'è differenza tra UCS-2 e UTF-16 per quanto riguarda i punti e i caratteri del codice BMP. La differenza è che UTF-16 utilizza quei 2048 punti di codice surrogati nel BMP per creare coppie surrogate che sono le codifiche per tutti i caratteri supplementari. Sebbene i caratteri supplementari siano 4 byte (in UTF-8, UTF-16 e UTF-32), sono in realtà due unità di codice a 2 byte quando si codifica in UTF-16 (allo stesso modo, sono quattro unità da 1 byte in UTF -8 e uno da 4 byte in UTF-32).

3. Poiché UTF-16 estende semplicemente ciò che può essere fatto con UCS-2 (definendo effettivamente l'utilizzo dei punti di codice surrogato), non c'è assolutamente alcuna differenza nelle sequenze di byte che possono essere memorizzate in entrambi i casi. Tutti i 2048 punti di codice surrogati utilizzati per creare caratteri supplementari in UTF-16 sono punti di codice validi in UCS-2, semplicemente non hanno alcun utilizzo definito (cioè interpretazione) in UCS-2.

4. NVARCHAR, NCHARE la deprecata-so-do-not-uso-da- NTEXTtipi di dati tutti i negozi caratteri Unicode codificati in UCS-2 / UTF-16. Dal punto di vista dell'archiviazione non c'è assolutamente alcuna differenza. Quindi, non importa se qualcosa (anche al di fuori di SQL Server) dice che può memorizzare UCS-2. Se è in grado di farlo, può archiviare intrinsecamente UTF-16. Infatti, anche se non ho avuto la possibilità di aggiornare il post collegato sopra, sono stato in grado di archiviare e recuperare, come previsto, emoji (la maggior parte dei quali sono caratteri supplementari) in SQL Server 2000 in esecuzione su Windows XP. Non sono stati definiti caratteri supplementari fino al 2003, credo, e certamente non nel 1999, quando è stato sviluppato SQL Server 2000. In effetti (di nuovo), UCS-2 è stato utilizzato solo in Windows / SQL Server perché Microsoft ha portato avanti lo sviluppo prima che UTF-16 venisse finalizzato e pubblicato (e non appena lo è stato, UCS-2 è diventato obsoleto).

5. L'unica differenza tra UCS-2 e UTF-16 è che UTF-16 sa come interpretare le coppie surrogate (costituite da una coppia di punti di codice surrogati, quindi almeno sono denominati in modo appropriato). È qui che entrano in gioco le _SCregole di confronto (e, a partire da SQL Server 2017, anche le _140_regole di confronto delle versioni che includono il supporto per i caratteri supplementari, quindi nessuno di loro ha il _SCnome nel loro nome): consentono alle funzioni integrate di SQL Server di interpretare correttamente i caratteri supplementari . Questo è tutto! Queste regole di confronto non hanno nulla a che fare con l'archiviazione e il recupero di caratteri supplementari, né hanno nulla a che fare con l'ordinamento o il confronto (anche se la documentazione "Collation and Unicode Support" dice specificamente che questo è ciò che fanno quelle regole di confronto - un altro elemento su la mia lista di "cose ​​da fare" da correggere). Per le regole di confronto che non hanno né _SCné _140_nel loro nome (anche se il nuovo SQL Server 2019 Latin1_General_100_BIN2_UTF8 potrebbe essere un'area grigia, almeno, ricordo che c'era qualche incongruenza lì o con le Japanese_*_140_BIN2regole di confronto), le funzioni integrate solo gestire i punti di codice BMP (ad esempio UCS-2).

6. Non "gestire" caratteri supplementari significa non interpretare una sequenza valida di due punti di codice surrogati come se fosse effettivamente un punto di codice supplementare singolare. Quindi, per le regole di confronto non "SC", il punto di codice surrogato BMP 1 (B1) e il punto di codice surrogato BMP 2 (B2) sono solo quei due punti di codice, nessuno dei quali è definito, quindi appaiono come due "niente" (cioè B1 seguito da B2). Questo è il motivo per cui è possibile dividere un carattere supplementare in due usando SUBSTRING/ LEFT/ RIGHTperché non sapranno tenere insieme questi due punti di codice BMP. Ma un confronto "SC" leggerà quei punti di codice B1 e B2 dal disco o dalla memoria e vedrà un singolo punto di codice supplementare S. Ora può essere gestito correttamente tramite SUBSTRING/ CHARINDEX/ ecc.

7. La NCHAR()funzione (non il tipo di dati; sì, la funzione con nome errato;) è anche sensibile al fatto che le regole di confronto predefinite del database corrente supportino o meno i caratteri supplementari. In caso affermativo, il passaggio di un valore compreso tra 65536 e 1114111 (l'intervallo di caratteri supplementari) restituirà un non NULLvalore. In caso contrario, verrà restituito un valore superiore a 65535 NULL. (Ovviamente, sarebbe molto meglio se NCHAR()funzionasse sempre, dato che l'archiviazione / recupero funziona sempre, quindi per favore vota questo suggerimento: la funzione NCHAR () dovrebbe sempre restituire il carattere supplementare per i valori 0x10000 - 0x10FFFF indipendentemente dalle regole di confronto predefinite del database attivo ) .

8. Fortunatamente, non è necessario un confronto "SC" per produrre un carattere supplementare. È possibile incollare il carattere letterale o convertire la coppia surrogata codificata UTF-16 Little Endian o utilizzare la NCHAR()funzione per generare la coppia surrogata. Quanto segue funziona in SQL Server 2000 (utilizzando SSMS 2005) in esecuzione su Windows XP:

   SELECT N'💩', -- 💩
   CONVERT(VARBINARY(4), N'💩'), -- 0x3DD8A9DC
   CONVERT(NVARCHAR(10), 0x3DD8A9DC), -- 💩 (regardless of DB Collation)
   NCHAR(0xD83D) + NCHAR(0xDCA9) -- 💩 (regardless of DB Collation)
   

   Per ulteriori dettagli sulla creazione di caratteri supplementari quando si utilizzano regole di confronto non "SC", vedere la mia risposta alla seguente domanda DBA.SE: Come si imposta una stringa Unicode / NVARCHAR di SQL Server su un'emoji o un carattere supplementare?

9. Niente di tutto ciò influisce su ciò che vedi. Se memorizzi un punto di codice, allora è lì. Il modo in cui si comporta - ordinamento, confronto, ecc. - è controllato dalle regole di confronto. Ma come appare è controllato dai caratteri e dal sistema operativo. Nessun font può contenere tutti i caratteri, quindi font diversi contengono set di caratteri diversi, con molte sovrapposizioni sui caratteri più utilizzati. Tuttavia, se un carattere ha una particolare sequenza di byte mappata, può visualizzare quel carattere. Questo è il motivo per cui l'unico lavoro richiesto per visualizzare correttamente i caratteri supplementari in SQL Server 2000 (utilizzando SSMS 2005) in esecuzione su Windows XP è stato aggiungere un carattere contenente i caratteri ed eseguire una o due modifiche minori del registro (nessuna modifica a SQL Server).

10. I caratteri supplementari nelle SQL_*regole di confronto e nelle regole di confronto senza un numero di versione nel nome non hanno pesi di ordinamento. Quindi, sono tutti uguali tra loro così come a qualsiasi altro punto di codice BMP che non ha pesi di ordinamento (inclusi "spazio" (U + 0020) e "null" (U + 0000)). Hanno iniziato a risolvere questo problema nelle _90_regole di confronto delle versioni .

11. SSMS non ha nulla a che fare con nulla di tutto ciò, a parte la possibile necessità del carattere utilizzato per l'editor delle query e / o dei risultati della griglia e / o degli errori + dei messaggi modificati in uno che ha i caratteri desiderati. (SSMS non esegue il rendering di nulla al di fuori forse dei dati spaziali; i caratteri sono resi dal driver video + definizioni di font + forse qualcos'altro).

Pertanto, la seguente dichiarazione nella documentazione (dalla domanda):

Se vengono specificate regole di confronto non SC, questi tipi di dati memorizzano solo il sottoinsieme di dati di caratteri supportati dalla codifica dei caratteri UCS-2.

è sia assurdo che errato. Probabilmente intendevano dire che i tipi di dati avrebbero memorizzato solo un sottoinsieme della codifica UTF-16 (poiché UCS-2 è il sottoinsieme). Inoltre, anche se dicesse "codifica caratteri UTF-16" sarebbe comunque sbagliato perché i byte che passi verranno memorizzati (supponendo che abbastanza spazio libero nella colonna o variabile).