• Articles
• admin

podczas tworzenia aplikacji lub pisania kodu w systemie bazodanowym SQL ważne jest zrozumienie, w jaki sposób dane będą sortowane i porównywane. Możesz przechowywać dane w określonym języku lub możesz chcieć, aby SQL Server traktował dane z rozróżnianiem wielkości liter i rozróżnianiem wielkości liter osobno. Firma Microsoft udostępniła ustawienie SQL Server o nazwie Collation w celu kontrolowania i rozwiązywania takich wymagań.

co to jest sortowanie w SQL Server?

możemy ustawić sortowanie na różnych poziomach w SQL Server, jak podano poniżej.

• poziom serwera
• poziom bazy danych
• poziom kolumn
• poziom wyrażeń

sortowanie na poziomie serwera może być czasami nazywane sortowaniem na poziomie instancji serwera SQL.

sortowanie na poziomie bazy danych zostanie odziedziczone po ustawieniu sortowania na poziomie serwera, jeśli nie wybierzesz żadnej konkretnej sortowania podczas tworzenia bazy danych. Można również zmienić sortowanie na poziomie bazy danych później. Zauważ, że zmiana sortowania bazy danych będzie stosowana tylko dla nadchodzących lub nowych obiektów, które zostaną utworzone po zmianie sortowania.

nowe sortowanie nie zmieni istniejących danych przechowywanych w tabelach posortowanych przy użyciu ostatniego typu sortowania. Zespół aplikacji potrzebuje dalszego planowania w celu obsługi tej konwersji zapisanych danych ze względu na nowe ustawienie sortowania.

istnieje kilka sposobów, aby to zrobić. Jednym z nich jest skopiowanie danych z istniejącej tabeli do nowej tabeli utworzonej z nowym zestawieniem, a następnie zastąpienie starej tabeli nową. Można również przenieść dane tabeli do nowej bazy danych poprzez nową sortowanie i zastąpić starą bazę danych nową.

Uwaga: Zmiana zestawienia jest złożonym zadaniem i powinieneś go unikać, chyba że masz obowiązkowy przypadek biznesowy.

jak znaleźć i zmienić zestawianie baz danych w SQL Server?

sprawdźmy zestawienie instancji SQL Server i wszystkich baz danych hostowanych na tej instancji. Możesz sprawdzić sortowanie, wchodząc do okna Właściwości bazy danych lub poziomu instancji za pomocą SQL Server Management Studio lub po prostu wykonując poniższą instrukcję T-SQL. Zestawienie dla każdej bazy danych jest przechowywane w obiekcie systemowym sys.bazy danych – uzyskamy do nich dostęp, aby uzyskać te informacje.

--Check Database CollationSELECT name, collation_name FROM sys.databases GO --Check Server or Instance level CollationSELECT SERVERPROPERTY('Collation') As 

wykonałem powyższe polecenie T-SQL i otrzymałem poniższe wyjście. Widzimy, że wszystkie bazy danych i sortowanie na poziomie serwera mają takie same ustawienia jak sql_latin1_general_cp1_ci_as. Oznacza to, że sortowanie baz danych zostało odziedziczone przez sortowanie na poziomie serwera podczas ich tworzenia, a wartość domyślna nie została zmieniona.

teraz pokażę Ci, jak sprawdzić sortowanie baz danych za pomocą GUI W Sql Server Management Studio.

najpierw połącz się z instancją SQL Server za pomocą SQL Server Management Studio. Rozwiń węzeł wystąpienia, a następnie folder bazy danych. Kliknij prawym przyciskiem myszy docelową bazę danych i wybierz Właściwości:

otrzymasz poniższe okno Właściwości bazy danych.

teraz kliknij kartę Opcje z panelu po lewej stronie. Otrzymasz wiele ustawień właściwości w panelu po prawej stronie. Collation jest pierwszą właściwością tej strony – widać, że jest taka sama jak w powyższym skrypcie T-SQL.

podobnie, możesz kliknąć węzeł wystąpienia SQL Server i kliknąć prawym przyciskiem myszy Właściwości poziomu wystąpienia, aby zobaczyć sortowanie na poziomie serwera.

jeśli chcesz zmienić to zestawienie na nowe, wystarczy kliknąć rozwijane Zestawienie i wybrać odpowiednią opcję. Upewnij się, że wykonałeś pełną kopię zapasową bazy danych przed zrobieniem tego.

wybrałem podobną kolację z rozróżnianiem wielkości liter SQL_Latin1_General_CP1_CS_AS dla tej bazy danych i kliknąłem OK, aby ją zastosować. Uwaga: Upewnij się, że nikt nie jest podłączony do docelowej bazy danych podczas tej procedury, w przeciwnym razie musisz przełączyć tryb na pojedynczy użytkownik I zmienić tę konfigurację.

można również zmienić to zestawienie baz danych za pomocą instrukcji T-SQL. W tym celu należy użyć klauzuli COLLATE instrukcji ALTER DATABASE.

najpierw przełączyliśmy bazę danych w tryb pojedynczego użytkownika, następnie zmieniliśmy sortowanie, a na koniec przenieśliśmy bazę danych do trybu wielu użytkowników.

--Change Database Collation using T-SQL USE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USER

Lista wszystkich obsługiwanych zestawień w SQL Server

Ta sekcja pokaże Ci, jak znaleźć wszystkie dostępne zestawienia w SQL Server. Najpierw pokażę Ci, jak uzyskać listę wszystkich obsługiwanych zestawień dla instancji SQL Server.

SQL Server posiada funkcję systemową o nazwie fn_helpcollations (), której możesz użyć do pobrania wszystkich zestawień.

uruchom poniższe polecenie, aby wyświetlić listę.

--Display the list of all collations SELECT name, description FROM fn_helpcollations()

możemy zobaczyć wszystkie 5508 wspieranych zestawień w sekcji Dane wyjściowe. Jeśli nie jesteś pewien, którą kolację wybrać, możesz użyć klauzuli WHERE w poniższym skrypcie, aby odfiltrować wszystkie możliwe kolacje, które można ustawić w bazie danych.

Załóżmy, że musisz przechowywać dane w języku angielskim w USA i chcesz, aby SQL Server obsługiwał je w formacie uwzględniającym wielkość liter. Możesz użyć poniższego polecenia, aby pobrać listę możliwych i obsługiwanych zestawień dla Twojego zapytania:

--Display the list of all collations with WHERE clause SELECT Name, Description FROM fn_helpcollations() WHERE Name like 'SQL_Latin1%' AND Description LIKE '%case-sensitive%'

wynik pokazuje tylko 10 zestawień spełniających Twoje zapytanie. Możesz użyć powyższego skryptu do odfiltrowania różnych zestawień.

wpływ zmiany zestawiania bazy danych na wynik zapytania

w tej sekcji pokażę Ci różnicę między dwoma wyjściami tego samego zapytania, gdy są one wykonywane z różnymi zestawieniami.

najpierw stworzę bazę danych o nazwie MSSQL z zestawieniem (SQL_Latin1_General_CP1_CS_AS). Następnie wykonam to samo zapytanie dwa razy, aby uzyskać wynik. Później zmienię zestawienie na sql_latin1_general_cp1_ci_as i ponownie uruchomię te same zapytania, aby uzyskać ich wynik. Możesz porównać oba wyjścia i zrozumieć wpływ zmiany sortowania bazy danych. Zacznijmy więc od tworzenia bazy danych.

Uruchom nowe okno tworzenia bazy danych, jak pokazano na poniższym obrazku. Możesz również utworzyć tę bazę danych za pomocą T-SQL. Następnie możesz zobaczyć nazwę bazy danych i jej Pliki Danych. Teraz kliknij drugą kartę na panelu po lewej stronie, aby przejść do okna właściwości sortowania.

możesz zobaczyć, że nazwa sortowania dla tej bazy danych jest domyślna. Oznacza to, że ta baza danych dziedziczy sortowanie z typu sortowania na poziomie serwera. Kliknij rozwijane Zestawienie, aby wybrać nowe zestawienie.

wybrałem poniższą kolację sql_latin1_general_cp1_cs_as dla tej bazy danych-nie domyślną. Kliknij OK, aby kontynuować tworzenie bazy danych.

teraz sprawdź zestawienie bazy danych dla nowo utworzonej bazy danych. Widzimy, że jest to SQL_Latin1_General_CP1_CS_AS, jak wybraliśmy w poprzednim kroku.

w Sql_latin1_general_cp1_cs_as CS oznacza tryb uwzględniający wielkość liter, A CI oznacza tryb niewrażliwy na wielkość liter. Teraz możesz uruchomić poniższy kod T-SQL lub dowolny kod, aby uzyskać wynik.

wykonałem to samo polecenie dwa razy. Pierwszy skrypt filtruje nazwy kolumn z wartością SYS wielkimi literami, podczas gdy drugi skrypt filtruje tę samą kolumnę z tą samą wartością sys małymi literami. Sekcja wyjście pokazuje, że pierwszy skrypt nie wyświetlił żadnego wyniku, podczas gdy drugi skrypt wyświetlił wynik ze względu na jego małe i duże litery.

Select * from sysusersWhere name='SYS'GoSelect * from sysusersWhere name='sys'GO

teraz zmienimy sortowanie tej bazy danych na sortowanie bez rozróżniania wielkości liter SQL_Latin1_General_CP1_CI_AS, wykonując poniższe polecenia T-SQL. Można go również zmienić za pomocą interfejsu GUI, wchodząc do okna Właściwości bazy danych w SQL Server Management Studio.

--Change database collation to SQL_Latin1_General_CP1_CI_ASUSE master; GO Alter DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATEGOALTER DATABASE COLLATE SQL_Latin1_General_CP1_CI_AS; GOAlter DATABASE SET MULTI_USER

wykonałem powyższy skrypt jednym strzałem, a sortowanie bazy danych zostało pomyślnie zmienione na nową, bez rozróżniania wielkości liter.

możesz zweryfikować tę zmianę, uruchamiając poniższe skrypty, aby sprawdzić zestawienie nowo utworzonej bazy danych MSSQL. Widzimy, że nowe zestawienie jest ustawione dla tej bazy danych na poniższym obrazku.

ponownie uruchomimy to samo polecenie T-SQL przed zmianą zestawienia, aby zobaczyć wpływ tej zmiany. Jak teraz widzimy, oba polecenia T-SQL są na wyjściu.

wniosek

mam nadzieję, że jest oczywiste, że zestawienie w SQL Server jest kluczowe. Zdefiniowaliśmy, jaki wpływ wywiera na Ciebie zmiana konfiguracji na dowolnym poziomie W SQL Server. Zawsze należy odpowiednio zaplanować i najpierw przetestować modyfikacje w środowisku o niższym cyklu życia.

czekajcie na mój następny artykuł, w którym pokażę wam krok po kroku metodę zmiany sortowania na poziomie serwera.