Polecenie ALTER DATABASE COLLATE zmienia sortowanie znaków obowiązujące domyślnie w danej bazie danych.
Co dokładnie zmienia ALTER DATABASE COLLATE w SQL Server
Polecenie ALTER DATABASE ... COLLATE w Microsoft SQL Server ustawia domyślne sortowanie i porównywanie znaków dla wskazanej bazy danych. Przykładowa składnia ma postać ALTER DATABASE NazwaBazy COLLATE Latin1_General_100_CI_AS;. Nazwa po słowie COLLATE określa konkretną kolację, czyli zestaw reguł używanych do porządkowania tekstu, porównywania wartości znakowych oraz określania wrażliwości na wielkość liter i akcenty.
Kolacja Latin1_General_100_CI_AS jest przykładem kolacji niewrażliwej na wielkość liter i wrażliwej na akcenty: przyrostek CI oznacza case-insensitive, a AS oznacza accent-sensitive. Wybór kolacji wpływa więc między innymi na to, czy wartości abc i ABC są traktowane jako równe podczas porównania oraz jak układają się wyniki zapytania z klauzulą ORDER BY. Nie jest to ustawienie formatu wyświetlania tekstu w aplikacji.
Kolacja bazy a kolacje tabel, kolumn i wyrażeń
Zmiana domyślnej kolacji bazy nie przepisuje automatycznie kolacji istniejących kolumn tekstowych w tabelach. Kolumny typu char, varchar, text, nchar, nvarchar i ntext mogą mieć własną kolację zapisaną w definicji kolumny. Jeżeli taka kolacja została określona, ma pierwszeństwo przed domyślną kolacją bazy. Z tego powodu samo wykonanie ALTER DATABASE ... COLLATE nie jest pełną migracją istniejących danych do nowych reguł porównywania.
Domyślna kolacja bazy jest używana przy tworzeniu nowych obiektów, gdy kolacja nie zostanie podana jawnie. Dotyczy to na przykład nowych kolumn znakowych. Kolację można również określić bezpośrednio przy deklaracji kolumny, używając składni takiej jak nvarchar(100) COLLATE Latin1_General_100_CI_AS. W pojedynczym zapytaniu można wymusić sposób porównania wyrażenia przez operator COLLATE, na przykład WHERE Nazwa COLLATE Latin1_General_100_CI_AS = @SzukanaNazwa. Takie jawne ustawienie może być potrzebne przy porównywaniu kolumn mających różne kolacje.
Wpływ ustawienia na zapytania i zgodność danych
Kolacja może zmienić wyniki porównań, sortowania, grupowania i działania indeksów opartych na kolumnach tekstowych. Różnice dotyczą między innymi wielkości liter, akcentów, znaków kana japońskich, szerokości znaków oraz reguł językowych zależnych od wybranej kolacji. W praktyce zapytanie wyszukujące po nazwie użytkownika, symbolu produktu albo nazwie pliku może po zmianie kolacji uznać wcześniej różne wartości za równoważne albo odwrotnie.
Szczególnej uwagi wymagają zapytania łączące tabele lub bazy danych o różnych kolacjach. SQL Server może zgłosić błąd konfliktu kolacji, gdy porównywane wyrażenia znakowe nie mają zgodnych ustawień. Problem można rozwiązać przez jawne użycie operatora COLLATE w zapytaniu, ale takie rozwiązanie powinno odpowiadać wymaganej semantyce danych. Wymuszenie kolacji na kolumnie w warunku może także utrudnić optymalizatorowi wykorzystanie istniejącego indeksu. Przed zmianą należy sprawdzić definicje kolumn, indeksy, klucze, ograniczenia, widoki, procedury i zapytania aplikacji, w których tekst jest porównywany lub sortowany.
Bezpieczne wykonanie zmiany w środowisku SQL Server
Zmiana kolacji bazy jest operacją administracyjną i powinna być wykonana po potwierdzeniu, że wskazana nazwa kolacji istnieje w używanej wersji SQL Server. Dostępne kolacje można sprawdzić zapytaniem SELECT name, description FROM sys.fn_helpcollations();. Przed modyfikacją trzeba wykonać kopię zapasową oraz zaplanować okno serwisowe. SQL Server wymaga dostępu wyłącznego do bazy podczas zmiany jej domyślnej kolacji, dlatego aktywne połączenia aplikacji i użytkowników mogą uniemożliwić wykonanie polecenia.
Typowy schemat kontrolowany przez administratora może obejmować przełączenie bazy do trybu pojedynczego użytkownika, wykonanie zmiany i przywrócenie trybu wielu użytkowników: ALTER DATABASE NazwaBazy SET SINGLE_USER WITH ROLLBACK IMMEDIATE;, następnie ALTER DATABASE NazwaBazy COLLATE Latin1_General_100_CI_AS;, a na końcu ALTER DATABASE NazwaBazy SET MULTI_USER;. Opcja WITH ROLLBACK IMMEDIATE powoduje natychmiastowe wycofanie aktywnych transakcji, więc jej użycie może przerwać pracę aplikacji i utracić niezapisane operacje. Po zmianie należy sprawdzić kolację bazy w widoku sys.databases, przetestować wyszukiwanie, sortowanie i zapytania z łączeniem tabel oraz zweryfikować, czy istniejące kolumny nadal mają oczekiwane ustawienia.
