Ustawiłem sortowanie bazy danych na Latin1_General_BIN, aby porównania ciągów rozróżniały wielkość liter. Czy to wpłynie na wydajność? Czy będzie to miało wpływ na operacje DML lub DDL w bazie danych? Baza danych już istnieje z tabelami.

Sortowania w SQL Server określają zasady dopasowywania i sortowania danych znakowych. Zwykle najpierw należy wybrać sortowanie w oparciu o semantykę porównania i porządek sortowania wymagany przez konsumentów danych.

Ludzie na ogół nie uważają, że układy binarne wywołują zachowania sortowania i porównywania, których oczekują. Tak więc, mimo że oferują one najlepszą wydajność (zwłaszcza wersje BIN2 z czystym kodem), większość implementacji ich nie używa.

Kolejne pod względem surowej wydajności (ale tylko w przypadku ciągów znaków innych niż Unicode) są zestawienia SQL kompatybilne wstecz . Podczas pracy z danymi Unicode te sortowania używają sortowania w systemie Windows zamiast tego o takich samych parametrach wydajności. Istnieją tutaj subtelne pułapki, więc musisz mieć dobre powody, aby wybrać sortowanie SQL w tych dniach (chyba że pracujesz w systemie amerykańskim, gdzie nadal jest domyślny).

Sortowanie w systemie Windows jest na ogół najwolniejsze ze względu na skomplikowane zasady porównywania i sortowania w Unicode. Niemniej jednak oferują one pełną zgodność z systemem Windows w SQL Server i są regularnie konserwowane, aby nadążać za zmianami w standardzie Unicode. W nowoczesnym zastosowaniu, które obejmuje dane Unicode, ogólnie zaleca się sortowanie w systemie Windows.

TL; DR

Jeśli wszystko, czego potrzebujesz, to rozróżnianie wielkości liter i semantyka sortowania, powinieneś wybrać _CS_wariant (dla rozróżniania wielkości liter) dowolnej kombinacji bazowej zapewniającej oczekiwane zachowanie języka i kultury użytkowników. Na przykład w obu przypadkach są uwzględniane wielkości liter:

-- Latin1-General, case-sensitive, accent-sensitive
Latin1_General_CS_AS 

-- Latin1-General, case-sensitive, accent-sensitive for Unicode Data, 
-- SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_CP1_CS_AS

Możesz zobaczyć te definicje używając sys.fn_helpcollations

Przykłady

Cztery tabele, które są dokładnie takie same, z wyjątkiem zestawienia; jeden binarny, jeden z rozróżnianiem wielkości liter, jeden bez rozróżniania wielkości liter i jeden SQL rozróżniający wielkość liter:

CREATE TABLE #Example_BIN
(
    string nvarchar(50) 
        COLLATE Latin1_General_BIN
        NOT NULL
);

CREATE TABLE #Example_CS
(
    string nvarchar(50) 
        COLLATE Latin1_General_CS_AI
        NOT NULL
);

CREATE TABLE #Example_CI
(
    string nvarchar(50) 
        COLLATE Latin1_General_CI_AI
        NOT NULL
);

CREATE TABLE #Example_SQL
(
    string varchar(50) -- Note varchar
        COLLATE SQL_Latin1_General_CP1_CS_AS
        NOT NULL
);

Te same przykładowe dane dla każdej tabeli:

INSERT #Example_BIN
    (string)
VALUES
    (N'A'),
    (N'a'),
    (N'B'),
    (N'b'),
    (N'C'),
    (N'c');

INSERT #Example_CS
SELECT EB.string 
FROM #Example_BIN AS EB;

INSERT #Example_CI
SELECT EB.string 
FROM #Example_BIN AS EB;

INSERT #Example_SQL
SELECT EB.string 
FROM #Example_BIN AS EB;

Teraz chcemy znaleźć ciągi większe niż „a”:

SELECT EB.string AS BIN
FROM #Example_BIN AS EB
WHERE EB.string > N'a'
ORDER BY EB.string;

SELECT EC.string AS CS
FROM #Example_CS AS EC
WHERE EC.string > N'a'
ORDER BY EC.string;

SELECT EC2.string AS CI
FROM #Example_CI AS EC2
WHERE EC2.string > N'a'
ORDER BY EC2.string;

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > 'a' -- not Unicode
ORDER BY ES.string;

Wyniki:

╔═════╗
║ BIN ║
╠═════╣
║ b   ║
║ c   ║
╚═════╝

╔════╗
║ CS ║
╠════╣
║ A  ║
║ b  ║
║ B  ║
║ c  ║
║ C  ║
╚════╝

╔════╗
║ CI ║
╠════╣
║ B  ║
║ b  ║
║ C  ║
║ c  ║
╚════╝

╔═════╗
║ SQL ║
╠═════╣
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Wreszcie...

Należy jednak pamiętać, że jeśli używamy literału Unicode z sortowaniem SQL, niejawne reguły konwersji powodują porównanie sortowania w systemie Windows:

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > N'a'
ORDER BY ES.string;

... a wyniki sortowania SQL zmieniają się :

╔═════╗
║ SQL ║
╠═════╣
║ A   ║
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Biorąc pod uwagę, że jest to istniejąca baza danych, w której są już zdefiniowane tabele, istnieją pewne bardzo poważne implikacje dla działania zmiany sortowania bazy danych, wykraczające poza potencjalny wpływ wydajności na opcje DML (które faktycznie już tam były). Ma to bardzo realny wpływ na wydajność i funkcjonalność, a ta zmiana nie tylko nie osiągnęła zamierzonego celu (przynajmniej niekonsekwentnie), ale również prawdopodobnie zmieniła zachowanie (lub zmieni zachowanie po utworzeniu nowych tabel) pod względem jak dane są uporządkowane i zrównane.

W swojej odpowiedzi Paul przedstawił już dobre wyjaśnienie i przykłady różnic w wydajności i zachowaniu między różnymi typami zestawień, więc nie powtórzę tego tutaj. Kilka punktów wymaga jednak dodatkowych szczegółów i istnieje kilka innych punktów, które należy dodać w związku z bieżącym scenariuszem zmiany sortowania istniejącej bazy danych, w przeciwieństwie do ustawiania sortowania nowej bazy danych.

1. W zestawieniach binarnych rozróżniana jest nie tylko wielkość liter: wszystko rozróżnia! Tak więc, używając sortowania binarnego (kończącego się na _BINlub _BIN2), twoje porównania są teraz również wrażliwe na akcent, wrażliwe na kana, wrażliwe na szerokość i potencjalnie wrażliwe na gluten (przynajmniej wydaje się, że jest to obecnie trend ;-)). Czy był to pożądany efekt wprowadzenia tej zmiany? Czy użytkownicy końcowi oczekują tej zmiany zachowania?

2. Zestawienia wpływają nie tylko na porównania, ale także na sortowanie. Sortowanie binarne będzie sortowane na podstawie wartości ASCIIlub UNICODEbajtu ( odpowiednio odpowiednio VARCHARlub NVARCHAR) każdego bajtu . Dlatego wybierając sortowanie binarne, rezygnujesz z reguł ważenia specyficznych dla języka / kultury, które porządkują każdy znak (nawet znaki w pewnym języku, na przykład węgierskim, składające się z 2 liter) zgodnie z alfabetem tej kultury. Tak więc, jeśli „ch” powinno naturalnie występować po „k”, cóż, nie stanie się to przy użyciu sortowania binarnego. Ponownie, czy był to pożądany efekt wprowadzenia tej zmiany? Czy użytkownicy końcowi oczekują tej zmiany zachowania?

3. O ile nie masz określonych wymagań dotyczących zgodności z poprzednimi wersjami dla swojej aplikacji, powinieneś używać BIN2zamiast BINsortowania, zakładając oczywiście, że chcesz przede wszystkim sortowanie binarne. Zestawienia BIN2zostały wprowadzone w SQL Server 2005 i zgodnie ze stroną MSDN zawierającą wytyczne dotyczące korzystania z zestawień BIN i BIN2 :

   Poprzednie zestawienia binarne w SQL Server, kończące się na „_BIN”, przeprowadzały niepełne porównanie kod-punkt-punkt dla danych Unicode. Starsze sortowanie binarne programu SQL Server porównywało pierwszy znak jako WCHAR, a następnie porównanie bajt po bajcie.

   ...

   Możesz przeprowadzić migrację do [_BIN2] zestawień binarnych, aby skorzystać z prawdziwych porównań kod-punkt, i powinieneś użyć nowych zestawień binarnych do tworzenia nowych aplikacji.

   Należy również zauważyć, że _BIN2zestawienia dogodnie pasują do zachowania Ordinalopcji wyliczenia StringComparison , dzięki czemu porównania i sortowanie w kodzie .NET przy użyciu tej opcji przyniosą takie same wyniki, jak te same operacje wykonywane w SQL Server (przy użyciu z _BIN2sortowania, oczywiście).

4. Z podobnych powodów do tego, co właśnie powiedziano o _BIN2zestawieniach, chyba że masz określone wymagania dotyczące zachowania zgodności z poprzednimi wersjami, powinieneś skłaniać się do używania zestawień Windows, a nie zestawień specyficznych dla SQL Server (tzn. Te zaczynające się od SQL_teraz są rozważane trochę „sucky” ;-)).

5. Podczas korzystania z danych Unicode (tj. Ciąg poprzedzony Nlub przychodzący do SQL Server z kodu aplikacji, w której typ danych został określony jako NCharlubNVarChar ), nie widzę, w jaki sposób użycie jednego zestawienia w porównaniu z innym miałoby wpływ na wstawienie lub aktualizację pola NCHARlub NVARCHARłańcucha .

   W przypadku korzystania z danych nieobsługujących kodu Unicode lub wstawiania lub aktualizowania pola nieobsługującego kodu Unicode, wówczas dane sortowanie (baza danych lub pole) może odgrywać niewielką rolę, jeśli wstawiane / aktualizowane znaki muszą zostać przetłumaczone lub nie można ich odwzorować (jest to nawet słowo?), zgodnie ze stroną kodową zdefiniowaną przez sortowanie. Oczywiście ten potencjalny problem występuje za każdym razem, gdy używa się danych lub typów danych innych niż Unicode, i nie jest on specyficzny dla tego scenariusza zmiany sortowania DB. Ta zmiana wpłynie na literały łańcuchowe (co może już być problemem, jeśli sortowanie DB było inne niż sortowanie pola). Ale nawet jeśli nie zostaną wprowadzone żadne zmiany w zestawieniu DB, dane przychodzące z innych DB lub spoza SQL Server (dowolny kod klienta) mogą zawierać dowolne znaki i mieć określone kodowanie.

6. BARDZO WAŻNE!!! Podczas zmiany domyślnego sortowania bazy danych sortowanie określone dla dowolnych istniejących łańcuchów pól w istniejących tabelach nie ulegnie zmianie, ale wszelkie nowe pola będą miały sortowanie domyślnych baz danych (chyba że zostaną zastąpione COLLATEklauzulą). Wpłynie to na twoje zapytania na trzy sposoby:

   1) Jeśli jakiekolwiek zapytanie DOŁĄCZ do któregokolwiek z istniejących pól do któregokolwiek z nowych pól, pojawi się błąd niedopasowania sortowania:

   USE [master];
   GO
   
   IF (DB_ID(N'ChangeCollationTest') IS NOT NULL)
   BEGIN
       PRINT 'Dropping [ChangeCollationTest] DB...';
       ALTER DATABASE [ChangeCollationTest]
           SET SINGLE_USER
           WITH ROLLBACK IMMEDIATE;
   
       DROP DATABASE [ChangeCollationTest];
   END;
   GO
   
   PRINT 'Creating [ChangeCollationTest] DB...';
   CREATE DATABASE [ChangeCollationTest]
       COLLATE SQL_Latin1_General_CP1_CI_AS;
   GO
   
   USE [ChangeCollationTest];
   GO
   
   CREATE TABLE [CollateTest-SQL_Latin1_General_CP1_CI_AS]
                (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
   SELECT *
   FROM   sys.columns sc
   WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-SQL_Latin1_General_CP1_CI_AS]');
   -- "collation_name" for both fields shows: SQL_Latin1_General_CP1_CI_AS
   GO
   
   USE [master];
   GO
   ALTER DATABASE [ChangeCollationTest]
       COLLATE Latin1_General_BIN2;
   GO
   USE [ChangeCollationTest];
   GO
   
   CREATE TABLE [CollateTest-Latin1_General_BIN2]
                (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
   SELECT *
   FROM   sys.columns sc
   WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-Latin1_General_BIN2]');
   -- "collation_name" for both fields shows: Latin1_General_BIN2
   GO
   
   
   SELECT *
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   INNER JOIN  dbo.[CollateTest-Latin1_General_BIN2] ctWIN
           ON  ctWIN.Col1 = ctSQL.Col1;

   Zwroty:

   Msg 468, Level 16, State 9, Line 4
   Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
   "Latin1_General_BIN2" in the equal to operation.

   2) Predykaty / filtry w istniejących polach istniejących tabel (ustawione na wcześniejsze domyślne sortowanie), które porównują z literałami ciągów lub zmiennymi, nie spowodują błędów, ale z pewnością mogą mieć wpływ na wydajność ze względu na konieczność SQL Server zrównania sortowania obie strony i automatyczne konwertowanie literału lub zmiennej ciągu na sortowanie pola. Włącz „Uwzględnij aktualny plan wykonania” (Control-M), a następnie wykonaj następujące czynności (zakładając, że uruchomiłeś już zapytania pokazane powyżej):

   SELECT *
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   WHERE  ctSQL.Col1 = N'a';
   -- Unspecified collations on string literals and variables assume the database default
   -- collation. This mismatch doesn't cause an error because SQL Server adds a
   -- "[Col1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)" but it can hurt performance.
   
   SELECT *
   FROM   dbo.[CollateTest-Latin1_General_BIN2] ctWIN
   WHERE  ctWIN.Col1 = N'a';
   -- No CONVERT_IMPLICIT; plan shows "[Col1]=[@1]".

   3) ORAZ, mówiąc o niejawnych konwersjach, zwróć uwagę, że jest to dosłowny ciąg znaków (z domniemanym zestawieniem domyślnego sortowania bazy danych:) Latin1_General_BIN2, a nie polem w tabeli. Czy są jakieś przypuszczenia, czy w filtrze tym nie będzie rozróżniana wielkość liter (stare sortowanie) czy rozróżniane są małe i wielkie litery (nowe sortowanie)? Uruchom następujące polecenie, aby zobaczyć:

   INSERT INTO dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] (Col1)
   VALUES (N'a'), (N'A');
   
   SELECT ctSQL.Col1
   FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
   WHERE  ctSQL.Col1 = N'a';

   Zwroty:

   Col1
   ----
   a
   A

   Nie! Nie tylko występuje nieznaczne (a może bardziej znaczące?) Obniżenie wydajności dla tego zapytania z powodu CONVERT_IMPLICIT(), ale nie zachowuje się nawet w pożądany sposób, z uwzględnieniem wielkości liter.

   Ergo, jeśli sortowanie zostanie zmienione w DB, która już ma tabele, to tak, wpłynie to zarówno na wydajność ORAZ funkcjonalność.

   Jeśli zestawianie jest ustawiane na nowym DB, to Paul już to omówił, wyjaśniając, w jaki sposób zestawienie binarne, chociaż szybkie, prawdopodobnie nie posortuje w sposób, jakiego można by oczekiwać lub czego można by oczekiwać.

Należy również zauważyć, że zawsze można określić sortowania według warunku. UKŁADAJ punkt może być dodany do WHEREwarunkach ORDER BY, a najkorzystniej w dowolnym miejscu, które przyjmuje się łańcuch.

Przykład 1 (warunek GDZIE):

SELECT tmp.col AS [SQL-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE SQL_Latin1_General_CP1_CS_AS;

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE Latin1_General_CS_AI;

Zwroty:

SQL-CaseSensitive
-----------------
b
B

Windows-CaseSensitive
-----------------
A
b
B

Przykład 2 (ORDER BY):

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_CS_AI;

SELECT tmp.col AS [Windows-Binary]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_BIN2;

Zwroty:

Windows-CaseSensitive
-----------------
a
A
b
B

Windows-Binary
-----------------
A
B
a
b

Przykład 3 (instrukcja IF):

IF ('A' = 'a') SELECT 1 AS [DatabaseDefault-CaseInsensitive?];
-- if the DB is not case-sensitive or binary, returns 1

IF ('A' = 'a' COLLATE Latin1_General_BIN2) SELECT 2 AS [Windows-Binary];

Zwroty:

DatabaseDefault-CaseInsensitive?
--------------------------------
1

{nothing}

Przykład 4 (skojarzenie z parametrem wejściowym funkcji):

SELECT  UNICODE(N'🂡') AS [UCS-2],
        UNICODE(N'🂡' COLLATE Latin1_General_100_CI_AS_SC) AS [UTF-16];
-- This character is a Unicode supplemental character and is not part of the
-- default UCS-2 encoding. In order for built-in functions to handle these
-- characters correctly, either the DB default collation needs to end in
-- "_SC" (available as of SQL Server 2012), or use as shown here.
-- See the character in more detail here: http://unicode-table.com/en/1F0A1/

Zwroty:

UCS-2    UTF-16
------   -------
55356    127137

Wartość UCS-2 wynosząca 55 356 jest częściowo poprawna, ponieważ jest pierwszą z dwóch wartości w „parze zastępczej”. Ale chyba, że ​​wyraźnie podano _SCsortowanie, UNICODE()funkcja może postrzegać każdy znak tylko jako wartość dwubajtową i nie wie, jak poprawnie obsługiwać dwubajtową parę zastępczą.

AKTUALIZACJA

Nawet we wszystkich powyższych przykładach jednym aspektem porównań rozróżniających wielkość liter, który jest zwykle pomijany i jest negowany przez binarne porównania / zestawienia, jest normalizacja (skład i rozkład), która jest częścią Unicode.

Przykład 5 (gdy porównanie binarne nie rozróżnia wielkości liter):

Prawdziwe porównania z rozróżnianiem wielkości liter pozwalają na łączenie znaków, które w połączeniu z innym znakiem tworzą jeszcze jeden znak, który już istnieje jako kolejny punkt kodowy Unicode. Porównania z rozróżnianiem wielkości liter dbają o wyświetlany znak, a nie o punkty kodowe użyte do jego utworzenia.

SELECT 'Equal' AS [Binary],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_BIN2
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_BIN2
-- No result as they are a different number of code points,
-- as well as being different code points.

SELECT 'Equal' AS [Case-Sensitive],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_CS_AS -- ü
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS -- u + combining diaeresis
-- Result set returned, even being a different number of code points AND Accent Sensitive,
-- due to normalization

Zwroty:

Binary            ü     u + combining diaeresis
-------          ---   -------------------------
{nothing}

Case-Sensitive    ü     u + combining diaeresis
---------------  ---   -------------------------
Equal             ü     ü

Prawdziwe porównania z rozróżnianiem wielkości liter pozwalają również, aby szerokie znaki były równe ich nieszerokim odpowiednikom.

IF (N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_100_BIN2)
  SELECT 'Values are the same' AS [Binary]
ELSE
  SELECT 'Values are different' AS [Binary];


IF (N'ｓｏｆｉａ' = N'sofia' COLLATE Latin1_General_100_CS_AS)
  SELECT 'Values are the same' AS [Case-Sensitive]
ELSE
  SELECT 'Values are different' AS [Case-Sensitive];

Zwroty:

Binary
---------------
Values are different


Case-Sensitive
---------------
Values are the same

Ergo:

BINARY ( _BINi _BIN2) sortowania są nie wielkość liter!