Jak używać sortowania UTF-8 w bazie danych SQL Server?

Przeprowadziłem migrację bazy danych z mysql do SQL Server (polityka), oryginalnej bazy danych mysql używającej UTF8.

Teraz przeczytałem /dba/7346/sql-server-2005-2008-utf-8-collation-charset, że SQL Server 2008 nie obsługuje utf8, czy to żart?

SQL Server obsługuje wiele baz danych, w większości zakodowanych w alfabecie łacińskim. Ponieważ migrowana baza danych jest przeznaczona do publikowania w Internecie, chcę zachować kodowanie utf8. Czy coś przeoczyłem lub czy muszę kodować / dekodować na poziomie aplikacji?

Nie! To nie żart.

Spójrz tutaj: http://msdn.microsoft.com/en-us/library/ms186939.aspx

Typy danych znakowych, które mają stałą długość, nchar lub zmienną długość, nvarchar, dane Unicode i używają zestawu znaków UNICODE UCS-2.

A także tutaj: http://en.wikipedia.org/wiki/UTF-16

Starszy UCS-2 (2-bajtowy uniwersalny zestaw znaków) to podobne kodowanie znaków, które zostało zastąpione przez UTF-16 w wersji 2.0 standardu Unicode w lipcu 1996 roku.

UTF-8 nie jest zestawem znaków, to kodowanie. Zestaw znaków dla UTF-8 to Unicode. Jeśli chcesz przechowywać tekst Unicode, użyj nvarchartypu danych.

Gdyby baza danych używała UTF-8 do przechowywania tekstu, nadal nie wydobywałbyś tekstu jako zakodowanych danych UTF-8, ale jako tekst zdekodowany.

Możesz łatwo przechowywać tekst zakodowany w UTF-8 w bazie danych, ale wtedy nie przechowujesz go jako tekstu, przechowujesz go jako dane binarne ( varbinary).

Wygląda na to, że będzie to wreszcie obsługiwane w SQL Server 2019! SQL Server 2019 - co nowego?

Od BOL:

Obsługa UTF-8

Pełna obsługa szeroko stosowanego kodowania znaków UTF-8 jako kodowania importu lub eksportu lub jako sortowanie danych tekstowych na poziomie bazy danych lub kolumny. UTF-8 jest dozwolone w CHARi VARCHARtypów danych, a jest włączona podczas tworzenia lub zmiany sortowania obiektu, do zestawień z UTF8przyrostkiem.

Na przykład LATIN1_GENERAL_100_CI_AS_SCto LATIN1_GENERAL_100_CI_AS_SC_UTF8. UTF-8 jest dostępny tylko dla sortowań systemu Windows, które obsługują znaki uzupełniające, zgodnie z wprowadzeniem w SQL Server 2012. NCHARi NVARCHARpozwalają tylko na kodowanie UTF-16 i pozostają niezmienione.

Ta funkcja może zapewnić znaczne oszczędności pamięci, w zależności od używanego zestawu znaków. Na przykład zmiana istniejącego typu danych kolumny z ciągami znaków ASCII z NCHAR(10)na CHAR(10)używanie sortowania z włączoną obsługą UTF-8 przekłada się na prawie 50% zmniejszenie wymagań dotyczących pamięci. Ta redukcja jest spowodowana tym, że NCHAR(10)wymaga 22 bajtów do przechowywania, podczas gdy CHAR(10)wymaga 12 bajtów dla tego samego ciągu Unicode.

Aktualizacja 2019-05-14:

Wydaje się, że dokumentacja jest teraz zaktualizowana i wyjaśnia nasze opcje pojawiające się w MSSQL 2019 w sekcji „ Sortowanie i obsługa Unicode ”.

Aktualizacja 2019-07-24:

Artykuł Pedro Lopes - Senior Program Manager @ Microsoft na temat wprowadzenia obsługi UTF-8 dla Azure SQL Database

Zauważ, że jak Microsoft SQL Server 2016, UTF-8 jest obsługiwane przez bcp, BULK_INSERTi OPENROWSET.

Dodatek 2016-12-21: SQL Server 2016 z dodatkiem SP1 umożliwia teraz kompresję Unicode (i większość innych funkcji, które wcześniej były dostępne tylko dla przedsiębiorstw) dla wszystkich wersji MS SQL, w tym Standard i Express. To nie to samo, co obsługa UTF-8, ale daje podobne korzyści, jeśli celem jest redukcja miejsca na dysku dla zachodnich alfabetów.

Dwa UDF do obsługi UTF-8 w T-SQL:

CREATE Function UcsToUtf8(@src nvarchar(MAX)) returns varchar(MAX) as
begin
    declare @res varchar(MAX)='', @pi char(8)='%[^'+char(0)+'-'+char(127)+']%', @i int, @j int
    select @i=patindex(@pi,@src collate Latin1_General_BIN)
    while @i>0
    begin
        select @j=unicode(substring(@src,@i,1))
        if @j<0x800     select @res=@res+left(@src,@i-1)+char((@j&1984)/64+192)+char((@j&63)+128)
        else            select @res=@res+left(@src,@i-1)+char((@j&61440)/4096+224)+char((@j&4032)/64+128)+char((@j&63)+128)
        select @src=substring(@src,@i+1,datalength(@src)-1), @i=patindex(@pi,@src collate Latin1_General_BIN)
    end
    select @res=@res+@src
    return @res
end

CREATE Function Utf8ToUcs(@src varchar(MAX)) returns nvarchar(MAX) as
begin
    declare @i int, @res nvarchar(MAX)=@src, @pi varchar(18)
    select @pi='%[à-ï][€-¿][€-¿]%',@i=patindex(@pi,@src collate Latin1_General_BIN)
    while @i>0 select @res=stuff(@res,@i,3,nchar(((ascii(substring(@src,@i,1))&31)*4096)+((ascii(substring(@src,@i+1,1))&63)*64)+(ascii(substring(@src,@i+2,1))&63))), @src=stuff(@src,@i,3,'.'), @i=patindex(@pi,@src collate Latin1_General_BIN)
    select @pi='%[Â-ß][€-¿]%',@i=patindex(@pi,@src collate Latin1_General_BIN)
    while @i>0 select @res=stuff(@res,@i,2,nchar(((ascii(substring(@src,@i,1))&31)*64)+(ascii(substring(@src,@i+1,1))&63))), @src=stuff(@src,@i,2,'.'),@i=patindex(@pi,@src collate Latin1_General_BIN)
    return @res
end