Spis treści:

Kategoria:Optymalizacja SQLSQL Server


Varchar i nvarchar - różnice

Czy to w ogóle ma znaczenie?

Przyznam się, że z lekkim niedowierzaniem przyglądałem się kilku projektom baz danych i sposobowi traktowania w nich wartości tekstowych. SQL Server dopuszcza kilka typów tekstowych - char, nchar, varchar, nvarchar, a także, oznaczone od wersji SQL Server 2005 jako przestarzałe, zachowane w celu zapewnienia zgodności wstecz typy text i ntext. Łatwo spośród tej grupy dobrać pary posiadające wspólny trzon, z czego jeden składnik posiada przedrostek n, a drugi nie. Jest char i nchar, text i ntext, ale nimi nie będę się zajmował. Zajmę się najpopularniejszą parą - varchar i nvarchar.

W projektach baz danych zauważam dwa, według mnie, nieprawidłowe trendy. Pierwszy polega na stosowaniu wszędzie typu varchar, a drugi - wszędzie nvarchar. O ile ten drugi trend nie był dla mnie niespodzianką, o tyle ten pierwszy nieco mnie zaskoczył. Dlaczego oba uważam za błędne? Postaram się wyjaśnić. Przykłady mogą się wydać nieco podkoloryzowane, ale dzięki temu lepiej pokazać potencjalne zagrożenia.

Tabele z varchar i nvarchar

W kolejnych punktach postaram się przedstawić wszystkie wady i zalety typów varchar i nvarchar. Wbrew niektórym opiniom to nie jest to samo. Każdą różnicę postaram się pokazać na konkretnym przykładzie. Przyjrzyjmy się zatem pierwszemu przykładowi - będzie to tabela reprezentująca polskie lotniska wraz z ich kodami IATAIATA - trzyliterowy kod alfanumeryczny, służący do oznaczania portów lotniczych na całym świecie..

--Tabela nvarchar(x)
CREATE TABLE NV_Airport
(
  Code nvarchar(3) NOT NULL,
  Name nvarchar(50) NOT NULL
)

INSERT INTO NV_Airport VALUES
(N'WAW', N'Warsaw-Chopin Airport'),
(N'KRK', N'John Paul II International Airport Kraków–Balice'),
(N'GDN', N'Gdańsk Lech Wałęsa Airport'),
(N'KTW', N'Katowice International Airport'),
(N'WRO', N'Wrocław-Copernicus Airport'),
(N'POZ', N'Poznań–Ławica Henryk Wieniawski Airport'),
(N'WMI', N'Warsaw-Modlin Airport'),
(N'RZE', N'Rzeszów-Jasionka Airport'),
(N'LCJ', N'Łódź Władysław Reymont Airport'),
(N'BZG', N'Bydgoszcz Ignacy Jan Paderewski Airport'),
(N'SZZ', N'"Solidarity" Szczecin-Goleniów Airport'),
(N'IEG', N'Zielona Góra-Babimost Airport'),
(N'LUZ', N'Lublin Airport'),
(N'QYD', N'Gdynia-Babie Doły Airport'),
(N'RDO', N'Radom-Sadków Airport'),
(N'SZY', N'Szczytno-Szymany International Airport'),
(N'OSZ', N'Koszalin-Zegrze Pomorskie Airport')

--Tabela varchar(x)
CREATE TABLE V_Airport
(
  Code varchar(3) NOT NULL,
  Name varchar(50) NOT NULL
)

INSERT INTO V_Airport VALUES
('WAW''Warsaw-Chopin Airport'),
('KRK''John Paul II International Airport Kraków–Balice'),
('GDN''Gdańsk Lech Wałęsa Airport'),
('KTW''Katowice International Airport'),
('WRO''Wrocław-Copernicus Airport'),
('POZ''Poznań–Ławica Henryk Wieniawski Airport'),
('WMI''Warsaw-Modlin Airport'),
('RZE''Rzeszów-Jasionka Airport'),
('LCJ''Łódź Władysław Reymont Airport'),
('BZG''Bydgoszcz Ignacy Jan Paderewski Airport'),
('SZZ''"Solidarity" Szczecin-Goleniów Airport'),
('IEG''Zielona Góra-Babimost Airport'),
('LUZ''Lublin Airport'),
('QYD''Gdynia-Babie Doły Airport'),
('RDO''Radom-Sadków Airport'),
('SZY''Szczytno-Szymany International Airport'),
('OSZ''Koszalin-Zegrze Pomorskie Airport')

Jakie, oprócz typu, są różnice? Na tym etapie można dostrzec jedną - stałe tekstowe poprzedzone są znakiem N. To instrukcja dla parsera SQL definiująca sposób interpretacji znaków - N oznacza typ nvarchar (nchar, ntext). Brak znaku N oznacza typ varchar (char, text). Nieprawidłowa specyfikacja łańcucha znaków może doprowadzić do niejawnych konwersji i należy tego unikać. Więcej, konwersja w jedną ze stron może spowodować utratę danych! O tym za chwilę.

Varchar? Nvarchar? Jeden czort!

Przeprowadźmy sobie prosty test logiczny - pobierzmy dane z obu tabel.

SELECT TOP 2 * FROM NV_Airport
SELECT TOP 2 * FROM V_Airport

Otrzymamy dwa razy takie same wynikiNiektórzy już na tym etapie mogą dostrzec różnice. Zakładam, że SQL Server zainstalowany jest na polskim systemie operacyjnym i zastosowano domyślne ustawienia językowe lub podczas instalacji wybrano jedną z polskich stron kodowych (Zob. Lista trybów sortowania (COLLATION)). W przykładzie użyto ustawienia Polish_CI_AS.:

CodeName
WAWWarsaw-Chopin Airport
KRKJohn Paul II International Airport Kraków–Balice

Mamy potwierdzenie! Jeden pies! Przypadek?

To właśnie takie przypadki przyczyniają się do powstania trendu "wszystko varchar". O co dokładniej chodzi?

Jak wsadzić arbuza do... kieszeni

Z polskimi znaczkami nie ma problemu. Przypuśćmy, że chcemy rozszerzyć zakres naszych usług i wysłać turystów na lotnisko Moskwa-Szeremietiewo. Moskwa jest ładna, z pewnością znajdą się chętni. Dodajmy zatem jeszcze po jednym rekordzie:

INSERT INTO NV_Airport
VALUES (N'SVO', N'Аэропорт Шереметьево')

INSERT INTO V_Airport
VALUES ('SVO''Аэропорт Шереметьево')

SELECTFROM NV_Airport WHERE Code=N'SVO'
SELECTFROM V_Airport WHERE Code='SVO'

Tym razem efekty powinny być widoczne od razu, wyniki będą się różnić. Dla tabeli nvarchar będzie to:

CodeName
SVOАэропорт Шереметьево

Dla tabeli varchar będzie to:

CodeName
SVO???????? ???????????

To bardzo duży błąd, bo błąd logiczny. Cała reszta to przy tym drobiazgi. W tym miejscu można już przedstawić pierwszy wniosek: jeżeli w kolumnie będą się pojawiać obcojęzyczne nazwy, takie jak nazwy lotnisk, nazwy pracowników (do handlu z rosją przydałby się jakiś Алекса́ндр), jakiś klient z zagranicy. W takim przypadku wręcz trzeba zastosować typ nvarchar. Takie myślenie prowadzi do powstania trendu "wszystko nvarchar". Wspomniałem, że on także jest zły. O co tym razem chodzi?

Ciężarówką na zakupy

Dlaczego nvarchar nie zawsze jest dobry? Mówiąc prostym językiem - jest większy. Typ varchar przeznacza jeden bajt na każdy znak, podczas gdy typ nvarchar na każdy znak potrzebuje dwóch bajtów. Jeden bajt to 256 kombinacji, czyli 256 różnych symboli. Wśród tych symboli pojawiają się jednak nie tylko litery. So to też cyfry, symbole arytmetyczne, znaki sterujące. Nie ma fizycznej możliwości reprezentacji wszystkich znaków z różnych alfabetów. Nie da się wsadzić arbuza do zwykłej kieszeni. Chyba, że pod pewnymi warunkami. Tak samo test z łańcuchami znaków - pod pewnymi warunkami da się je wcisnąć do varchar. Nvarchar przeznacza na każdy znak 2 bajty, co daje 65536 kombinacji - znacznie więcej. Czy zawsze tyle potrzebujemy? Jak nietrudno się domyślić, dane nvarchar będą zajmowały dwa razy więcej miejsca niż varchar. Przyjrzyjmy się następującemu zapytaniu:

SELECT 'NV_Airport' NameSUM(DATALENGTH(Code)) Size
FROM NV_Airport
UNION ALL
SELECT 'V_Airport'SUM(DATALENGTH(Code))
FROM V_Airport

Otrzymamy następujące wyniki:

NameSize
NV_Airport108
V_Airport54

Tabela jest mała, ale tabele nie zawsze takie są. Przypuśćmy, że chcemy wyświetlać nazwy w alfabecie łacińskim lub po angielsku (w praktyce tak się robi). Czy przeciętnemu Polakowi więcej powie nazwa Аэропорт Шереметьево czy Port lotniczy Moskwa-Szeremietiewo? Przeciętnemu turyście zagranicznemu na przeciętnym zagranicznym lotnisku raczej pokazuje sie nazwę Lech Walesa Airport niż Lech Wałęsa Airport. Takie założenia pozwalają ograniczyć rozmiar jednego znaku dwukrotnie. Dla miliona rekordów, po 50 znaków każdy, otrzymujemy 50 MB przestrzeni, której SQL Server nie musi przetwarzać! Nie musi tych danych czytać z dysku, przesyłać do aplikacji klienckiej, zapisywać. Lotnisk tyle nie będzie, ale już rejestracji samochodów - według danych GUS w 2010 było ponad 23.000.000 zarejestrowanych pojazdów!

Klucz, indeks i kolejne problemy

Im bardziej zaczniemy się zagłębiać w wewnętrzną strukturę bazy danych, tym więcej dostrzeżemy wad pól typu nvarchar. Co się dzieje, gdy taka kolumna tekstowa trafia do indeksu? Kod lotniska jest naturalnym kandydatem do klucza głównego (tablica rejetracyjne też). Jak nie do klucza głównego (być może klastrowanego), to do klucza unikalnego, jak nie do unikalnego, do chociaż do jakiegoś indeksuStworzenie klucza głównego skutkuje pojawieniem się indeksu - dzieje się to automatycznie. Podobnie w przypadku klucza unikalnego.. Co się wtedy dzieje? Każdy taki indeks, na każdym poziomie, przechowuje kopięWyjątek stanowi klucz/indeks klastrowany, który określa fizyczne położenie danych, dane nie są powielane - są tylko inaczej układane. danych będących częścią klucza. Jeżeli kolumna z tekstem jest kolumną dołączoną (opcja INCLUDE indeksu), wtedy kopia występuje tylko na poziomie liścia. Jeżeli kolumna z tekstem jest częścią indeksu klastrowanego, wtedy dołączana jest do każdego liścia każdego indeksu! Takie niejawne dołączanie wartości nvarchar zamiast varchar możne mocno utuczyć serwer.

Przyjrzyjmy się przykładowej tabeli i zajmowanemu przez nią rozmiaru:

CREATE TABLE TextIndex
(
  A nvarchar(4) NOT NULL,
  B nvarchar(4),
  C nvarchar(4)
)

SET NOCOUNT ON
BEGIN TRANSACTION
  DECLARE @i int = 0
  DECLARE @letters intASCII('Z')-ASCII('A')+1
  WHILE @i<100000
  BEGIN
    INSERT INTO TextIndex
    VALUES (
      CHAR(ASCII('A') + @i/@letters/@letters/@letters % @letters) +
      CHAR(ASCII('A') + @i/@letters/@letters % @letters) +
      CHAR(ASCII('A') + @i/@letters % @letters) +
      CHAR(ASCII('A') + @i % @letters),
      N'BBBB',
      N'CCCC')
    SET @i += 1
  END
COMMIT

--Indeks klastrowany
ALTER TABLE TextIndex
ADD CONSTRAINT PK_A
PRIMARY KEY CLUSTERED (A)

--Dwa indeksy
CREATE INDEX I_B ON TextIndex(B)
CREATE INDEX I_C ON TextIndex(C)

--Pobierz informacje o rozmiarze
SELECT CASE WHEN GROUPING(i.name)=1 THEN 'Total' ELSE i.name END AS [Index],
  SUM(s.used_page_count) [Index size in pages]
FROM sys.dm_db_partition_stats s
JOIN sys.indexesON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('TextIndex')
GROUP BY i.name WITH ROLLUP

Warto zwrócić uwagę na sposób generowania wartości klucza. Wstawiane są do niego kolejne unikalne wartości tekstowe AAAA, AAAB, ..., AAAZ, AABA, i tak dalej. Na końcu wykonywane jest zapytanie pobierające informację o rozmiarach indeksów (więcej szczegółów tutaj: Pobieranie informacji o rozmiarach indeksów). Po wykonaniu skryptu otrzymamy następujący wynik:

IndexIndex size in pages
I_B351
I_C351
PK_A529
Total1231

Czy potrafimy oszacować rozmiar podobnych indeksów dla tabeli z typem varchar? Czy będzie on dwa razy mniejszy? Popatrzmy na wyniki dla podobnej tabeli, z typem zamienionym na varchar:

IndexIndex size in pages
I_B250
I_C250
PK_A382
Total882

Taki rezultat pojawił się na stanowisku testowym. Kto był blisko? Okazuje się, że wyliczenie rozmiaru struktur nie jest takie oczywiste. Co ciekawe, to tak przy okazji, zmiana typu na char(4) pozwoli uzyskać jeszcze mniejsze rozmiary:

IndexIndex size in pages
I_B176
I_C176
PK_A280
Total632

Typy varchar i nvarchar mają pewne zalety w stosunku do typów char i nchar - pozwalają dostosować swój rozmiar do rozmiaru danych. Ta zdolność kosztuje - po pierwsze, trzeba jakoś oznaczyć bieżący rozmiar, a po drugie, zwiększenie rozmiaru może powodować podziały stron. Znów - nie da się arbuza wcisnąć do kieszeni. Podziały stron to koszt podziału, a także koszt zapytań związany z fragmentacją. Typy char i nchar zawsze zajmują tyle samo miejsca, niezależnie od wartości. Typ char(10) to zawsze 10 bajtów, nawet, jeżeli w środku jest tylko jedna litera lub nawet NULL! Coś za coś.

Pomijając typ char, o którym miałem nie mówić - im mniej danych, tym mniejsze struktury tabel/kluczy/indeksów. Im mniejsze struktury, tym mniejszy koszt różnych operacji. Mniejsze indeksy do przeszukiwania, mniejsze indeksy do przebudowy (zmiana jednej wartości może powodować zmianę w wielu większych indeksach), mniejsza zawartość pamięci podręcznej (cache), mniejsza ilość bajtów do przesłania przez sieć, mniejsze pliki z kopiami zapasowymi, mniejsze logi. Trzeba też wiedzieć, że algorytmy mogą mieć złożoność gorszą niż liniowa (przykład: sortowanie). Pamięć dyskowa jest raczej tania, ale dysk to nie wszystko. Czy łatwiej posortować obszary 10 bajtowe, czy 20 bajtowe? Czy łatwiej porównać obszary 10 bajtowe, czy 20 bajtowe?

Wnioski

Jakie są wnioski? Po pierwsze, dla kolumny Code naszego lotniska najlepiej zastosować... char(3). Wracając jednak do pojedynku varchar-nvarchar moja reguła jest następująca: jeżeli nie zamierzamy przechowywać żadnych obcych znaczków, zdecydowanie lepszym rozwiązaniem jest typ varchar. Jeżeli w kolumnie będą się pojawiać znaki z wielu języków - nie ma wyjścia - trzeba użyć nvarchar. Rozmiar ma znaczenie i lepiej stosować najmniejszy z możliwych typów, który pozwala nam na realizację pewnej funkcjonalności. Jeżeli przewidujemy w najbliższej przyszłości jakieś mocno prawdopodobne zmiany, przy okazji korzystamy z jakiegoś narzędzia OR/M, które wymaga dokładnej specyfikacji typu, można sobie typ nieco zwiększyćADO.NET tak samo obsługuje varchar i nvarchar dokonując konwersji po stronie klienta bazy danych na typ string.. Ci, którzy pracują bądź pracowali przy większych projektach z pewnością spotkali się z sytuacją, którą nazywam "Poprawi się, jak będzie czas". Doświadczenie pokazuje, że tego czasu jakoś nie przybywa, a zadania ciągle odkładane są na bliżej nieokreśloną przyszłość. Rozwiązania tymczasowe stają się obowiązującymi, pojawiają się nowe zadania, a stare - skoro jakoś to działa, to nie ruszajmy. Czy kupując pierwszy samochód idziemy po ciężarówkę, bo może gdzieś, kiedyś w przyszłości, zechcemy wybudować dom i przewieźć piasek i cegły? Czy kupujemy autobus, bo może w przyszłości założymy tak dużą rodzinę, że do zwykłego samochodu osobowego ich nie wciśniemy? Zmiana rozszerzająca, od typu mniej pojemnego do typu bardziej pojemnego nie jest problemem. Gorzej w drugą stronę, bo istnieje ryzyko utraty danych. Czy to z nvarchar na varchar, czy z int na tinyint. Czy potrzebujemy typu nvarchar i znaków Unicode dla symboli lotnisk, tablic rejestracyjnych, numerów faktur, kodów wykorzystywane wewnętrznie w systemie, przyjaznych dla wyszukiwarek adresów URL, nazw typów/struktur związanych z językiem programowania? Czy gdzieś tam może się pojawić jakiś dziwny symbol? Jeżeli nie - lepszy wydaje się varchar. Wszelkie rozważania mogą być bezcelowe, gdy baza jest mała, a sam system stosunkowo prosty. Problemy zaczynają się pojawiać, gdy ilość danych rośnie, a nasza baza, testowana dotychczas na 50 rekordach, nagle musi obsłużyć tych rekordów 1.000.000. Ktoś może powiedzieć - a gdybyśmy tak zechcieli wejść ze sprzedażą do innego kraju? Wydaje mi się, że baza danych to najczęściej małe piwo. Większe zagrożenia dostrzegam w interfejsie użytkownika, tłumaczeniach komunikatów, napisów na kontrolkach. Zdarza się, że ten sam tekst w innym języku jest dłuższy i... nie mieści się na przycisku. Jeżeli system nie jest zaprojektowany międzynarodowo, przebudowa wiąże się z dużymi kosztami. A jeżeli jest - wtedy wskazanie międzynarodowych kolumn w bazie jest znacznie łatwiejsze.

To, co napisałem, to nie są żelazne zasady - to raczej zalecenia. I tak, co wielokrotnie powtarzam, wszystko zależy od konkretnej sytuacji i indywidualnej decyzji. Warto jednak pamiętać o wszystkich konsekwencjach swojego wyboru. Świadomego.

Kategoria:Optymalizacja SQLSQL Server

, 2013-12-20

Komentarze:

SQL Develop (2015-04-08 11:43:56)
Bardzo dobry artykuł.
W sposób obrazowy i rzeczowy pokazuje podstawowe błędy w zarządzaniu typami tekstowymi. Dzięki takim rozwiązaniom tymczasowym na "odpierd0l" bazy ważą o wiele więcej, a naprawianie błędów spędza sen z powiek nie jednemu programiście/administratorowi.
rudatwarz (2015-09-24 10:58:21)
Dzięki,
bardzo ładnie napisany artykuł.
Heimdall_PL (2017-08-28 14:39:54)
5+. Nie jednej osobie uratuje życie.
Janek (2017-10-25 08:23:41)
Tego wlasnie potrzebowalem
Bernard (2018-08-22 09:28:47)
Wielkie dzieki za solidne wyjasnienia tematu.
Dodaj komentarz
Wyślij
Ostatnie komentarze
bardo ciekawe , można dzięki takim wpisom dostrzec wędkę..
Bardzo dziękuję za jasne tłumaczenie z dobrze dobranym przykładem!
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !