Kodowanie UCS-2 ma zawsze 2 bajty na znak i ma zakres od 0 do 65535 (0x0000 - 0xFFFF). UTF-16 (niezależnie od Big Endian lub Little Endian) ma zakres od 0 do 1114111 (0x0000 - 0x10FFFF). Zakres 0 - 65535 / 0x0000 - 0xFFFF dla UTF-16 wynosi 2 bajty na znak, podczas gdy zakres powyżej 65536 / 0xFFFF wynosi 4 bajty na znak.
Windows i SQL Server zaczęły od kodowania UCS-2, ponieważ były one dostępne, a UTF-16 nie został jeszcze sfinalizowany. Na szczęście jednak w projektach UCS-2 i UTF-16 było wystarczająco dużo myśli, aby odwzorowania UCS-2 były kompletnym podzbiorem odwzorowań UTF-16 (co oznacza: zakres 0 - 65535 / 0x0000 - 0xFFFF UTF-16 to UCS-2). ORAZ zakres 65536 - 1114111 (0x10000 - 0x10FFFF) UTF-16 jest skonstruowany z dwóch punktów kodowych w zakresie UCS-2 (konkretnie zakresów 0xD800 - 0xDBFF i 0xDC00 - 0xDFFF), które zostały zarezerwowane do tego celu, a poza tym nie mają znaczenie. Ta kombinacja dwóch Punktów Kodowych jest znana jako Para Zastępcza, a Pary Zastępcze reprezentują postacie spoza zakresu UCS-2, które są znane jako Postacie Uzupełniające.
Wszystkie te informacje wyjaśniają dwa aspekty danych NVARCHAR/ Unicode w SQL Server:
- Kilka wbudowanych funkcji (nie tylko
NCHAR()) nie obsługują zastępczych par / dodatkowych znaków, gdy nie jest używany charakter uzupełniający-Aware Sortowanie (SCA, czyli jeden z _SC, lub _140_ ale nie _BIN*w nazwie), ponieważ non-SCA Konfrontacje (zwłaszcza SQL_Zestawienia) zostały pierwotnie zaimplementowane przed ukończeniem UTF-16 (chyba w 2000 roku). W nie- SQL_Konfrontacje, które mają _90_lub _100_w ich imieniu, ale nie _SCmają minimalne wsparcie dla znaków uzupełniające w zakresie porównywania i sortowania.
- Pełny zestaw znaków Unicode / UTF-16 można przechowywać bez utraty danych w typach danych
NVARCHAR/ NCHAR/ XML/, NTEXTponieważ UCS-2 i UTF-16 są dokładnie tymi samymi sekwencjami bajtów. Jedyną różnicą jest to, że UTF-16 korzysta z punktów kodu zastępczego do konstruowania par surogatów, a UCS-2 po prostu nie może mapować ich na żadne znaki, dlatego wydają się one wbudowanym funkcjom jako dwa nieznane znaki.
Mając na uwadze te informacje podstawowe, możemy teraz przejść do szczegółowych pytań:
Chciałbym SELECT NCHAR(128512);zwrócić to samo:SELECT N'😀';
Może się to zdarzyć tylko wtedy, gdy bieżąca baza danych - w której wykonywane jest zapytanie - ma domyślne sortowanie, czyli uzupełniające rozpoznawanie znaków i te zostały wprowadzone w SQL Server 2012. Funkcje wbudowane, które mają parametry wejściowe ciągu, mogą mieć zapewnione sortowanie inline poprzez COLLATEklauzulę (tj. LEN(N'string' COLLATE Some_Collation_SC)) i nie muszą być wykonywane w bazie danych, która ma domyślne sortowanie SCA. Jednak wbudowane funkcje, takie jak NCHAR()akceptacja INTparametru wejściowego i COLLATEklauzula nie są poprawne w tym kontekście (dlategoNCHAR() obsługuje tylko znaki uzupełniające, gdy bieżąca baza danych ma domyślne sortowanie, które jest dodatkowym znakiem rozpoznającym; ale jest to niepotrzebne niedogodności, które można zmienić, dlatego proszę głosować na moją sugestię:Funkcja NCHAR () powinna zawsze zwracać znak dodatkowy dla wartości 0x10000 - 0x10FFFF niezależnie od domyślnego sortowania aktywnej bazy danych ).
Czy istnieje wyjaśnienie, dlaczego, niezależnie od sortowania, SQL Server może zrozumieć i radzić sobie z rozszerzonymi znakami, z wyjątkiem perspektywy NCHAR?
Jak SQL Server może przechowywać i odzyskiwać dodatkowe znaki bez utraty danych, wyjaśniono w górnej części tej odpowiedzi. Ale nie jest prawdą, że NCHARjest to jedyna wbudowana funkcja, która ma problemy ze znakami uzupełniającymi (gdy nie używa się sortowania SCA). Na przykład LEN(N'😀' COLLATE SQL_Latin1_General_CP1_CI_AS)zwraca wartość 2, a LEN(N'😀' COLLATE Latin1_General_100_CI_AS_SC)zwraca wartość 1.
Jeśli przejdziesz do drugiego linku zamieszczonego w pytaniu (tj. „Informacje o sortowaniu znaków uzupełniających Microsoftu”) i przewiniesz trochę w dół, zobaczysz tabelę wbudowanych funkcji i ich zachowania w oparciu o efektywne sortowanie.
Jak znaleźć zestawienie z flagą „znak uzupełniający”?
W wersji SQL Server sprzed 2012 roku nie możesz. Ale począwszy od SQL Server 2012 można użyć następującego zapytania:
SELECT col.*
FROM sys.fn_helpcollations() col
WHERE col.[name] LIKE N'%[_]SC'
OR col.[name] LIKE N'%[_]SC[_]%'
OR (COLLATIONPROPERTY(col.[name], 'Version') = 3
AND col.[name] NOT LIKE N'%[_]BIN%');
Twoje zapytanie było bliskie, ale wzorzec zaczął się od, SQLa SQL Server Collations (tj. Te zaczynające się od SQL_) zostały na jakiś czas przestarzałe na korzyść Windows Collations (te, które nie zaczynają się od SQL_). Tak więc sortowaniaSQL_ nie są aktualizowane, a zatem nie mają nowszych wersji, które zawierałyby tę _SCopcję (i począwszy od SQL Server 2017, wszystkie nowe sortowania automatycznie obsługują dodatkowe znaki i nie wymagają lub nie mają _SCflagi; i tak, zapytanie pokazane bezpośrednio powyżej tego konta, a także pobieranie _UTF8zestawień dodanych w SQL Server 2019).
Czy możesz zainstalować sortowanie w starszych instancjach?
Nie, nie można zainstalować Collations w poprzedniej wersji SQL Server.
Jak ustawić zmienną łańcuchową Unicode (np. Nvarchar) na znak uzupełniający za pomocą kodu (bez użycia rzeczywistego znaku uzupełniającego) w bazie danych, w której zestawienie „nie zawiera flagi znaku uzupełniającego (SC)”?
...
Chociaż serwerem jest SQL Server 2008 R2, jestem również ciekawy rozwiązań dla późniejszych wersji.
Gdy nie używasz sortowania SCA, możesz wstrzykiwać punkty kodowe powyżej 65535 / U + FFFF na dwa sposoby:
- Podaj
NCHAR()parę zastępczą pod względem dwóch wywołań funkcji, każde z jedną częścią pary
- Podaj parę zastępczą pod względem konwersji
VARBINARYpostaci sekwencji bajtów Little Endian (tj. Odwróconej).
Te dwie metody wstawiania par znaków uzupełniających / zastępczych będą działać, nawet jeśli skuteczne sortowanie jest uzupełniające z uwzględnieniem znaków i powinno działać tak samo we wszystkich wersjach programu SQL Server, przynajmniej w 2005 r. (Choć prawdopodobnie działałoby również w SQL Server 2000).
Przykład:
- Postać:
💩
- Nazwa: Pile of Poo
- Dziesiętny: 128169
- Punkt kodowy: U + 1F4A9
- Para zastępcza: U + D83D i U + DF21
SELECT N'💩', -- 💩
UNICODE(N'💩' COLLATE Latin1_General_100_CI_AS), -- 55357
UNICODE(N'💩' COLLATE Latin1_General_100_CI_AS_SC), -- 128169
NCHAR(128169), -- 💩 in DB with _SC Collation, else NULL
NCHAR(0x1F4A9), -- 💩 in DB with _SC Collation, else NULL
CONVERT(VARBINARY(4), 128169), -- 0x0001F4A9
CONVERT(VARBINARY(4), N'💩'), -- 0x3DD8A9DC
CONVERT(NVARCHAR(10), 0x3DD8A9DC), -- 💩 (regardless of DB Collation)
NCHAR(0xD83D) + NCHAR(0xDCA9) -- 💩 (regardless of DB Collation)
AKTUALIZACJA
Możesz użyć następującego iTVF, aby uzyskać wartości pary zastępczej (zarówno w formie, jak INTi BINARYpostaci) z dowolnego punktu kodowego między 65536 - 1114111 (0x010000 - 0x10FFFF). I chociaż parametr wejściowy jest typu INT, możesz przekazać w postaci binarnej / szesnastkowej punktu kodowego, a on domyślnie przekonwertuje na prawidłową wartość całkowitą.
CREATE FUNCTION dbo.GetSupplementaryCharacterInfo(@CodePoint INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
WITH calc AS
(
SELECT 55232 + (@CodePoint / 1024) AS [HighSurrogateINT],
56320 + (@CodePoint % 1024) AS [LowSurrogateINT]
WHERE @CodePoint BETWEEN 65536 AND 1114111
)
SELECT @CodePoint AS [CodePointINT],
HighSurrogateINT,
LowSurrogateINT,
CONVERT(VARBINARY(3), @CodePoint) AS [CodePointBIN],
CONVERT(BINARY(2), HighSurrogateINT) AS [HighSurrogateBIN],
CONVERT(BINARY(2), LowSurrogateINT) AS [LowSurrogateBIN],
CONVERT(binary(4), NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT)) AS [UTF-16LE],
NCHAR(HighSurrogateINT) + NCHAR(LowSurrogateINT) AS [Character]
FROM calc;
GO
Korzystając z powyższej funkcji, następujące dwa zapytania:
SELECT * FROM dbo.GetSupplementaryCharacterInfo(128169);
SELECT * FROM dbo.GetSupplementaryCharacterInfo(0x01F4A9);
oba zwracają:
CodePoint HighSurrogate LowSurrgate CodePoint HighSurrgate LowSurrgate UTF-16LE Char
INT INT INT BIN BIN BIN actr
128169 55357 56489 0x01F4A9 0xD83D 0xDCA9 0x3DD8A9DC 💩
AKTUALIZACJA 2: Jeszcze lepsza aktualizacja!
Zaadaptowałem pokazany powyżej iTVF, aby teraz zwracał 188,657 punktów kodowych, więc nie musisz dopasowywać żadnej konkretnej wartości. Oczywiście będąc TVF, możesz dodać WHEREklauzulę do filtrowania określonego punktu kodowego lub zakresu punktów kodowych lub „podobnych znaków” itp. I zawiera dodatkowe kolumny ze wstępnie sformatowanymi sekwencjami ucieczki, aby zbudować każdy kod punkt (zarówno BMP, jak i znaki uzupełniające) w stylu T-SQL, HTML i C (tj \xHHHH.). Przeczytaj o tym tutaj:
Wskazówka SSMS nr 3: Łatwy dostęp / badanie WSZYSTKICH znaków Unicode (tak, w tym emotikonów 😸)