Jakie są główne różnice wydajności między typami danych varchar i nvarchar SQL Server?

Miejsce na dysku nie jest problemem... ale pamięć i wydajność będą. Podwojenie odczytu strony, podwójny rozmiar indeksu, dziwne jak i = stałe zachowanie itp

Czy trzeba przechowywać Chiński skrypt etc? Tak czy nie...

I z MS BOL " Storage and Performance Effects of Unicode "

Edit :

Ostatnie tak pytanie podkreślające, jak zła może być wydajność nvarchar...

SQL Server używa wysokiego procesora podczas wyszukiwania wewnątrz nvarchar strings

Bądź konsekwentny! Dołączenie VARCHAR do NVARCHAR ma wielki hit wydajności.

Nvarchar będzie miał znaczny narzut w pamięci, przechowywaniu, zestawie roboczym i indeksowaniu, więc jeśli specyfikacje dyktują, że naprawdę nie będzie to konieczne, nie przejmuj się.

Nie miałbym twardej i szybkiej zasady "zawsze nvarchar", ponieważ może to być kompletna strata w wielu sytuacjach - szczególnie ETL z ASCII / EBCDIC lub identyfikatorów i kolumn kodu, które często są kluczami i kluczami obcymi.

Z drugiej strony, jest wiele przypadków kolumn, w których chciałbym pamiętaj, aby zadać to pytanie wcześnie i jeśli nie dostać twardą i szybką odpowiedź natychmiast, chciałbym nvarchar kolumna.

Dla Twojej aplikacji, nvarchar jest w porządku, ponieważ rozmiar bazy danych jest mały. Powiedzenie "Zawsze używaj nvarchar" jest ogromnym uproszczeniem. Jeśli nie musisz przechowywać takich rzeczy, jak Kanji lub inne szalone znaki, użyj VARCHAR, będzie to wymagało dużo mniej miejsca. Mój poprzednik w mojej obecnej pracy zaprojektował coś przy użyciu NVARCHAR, gdy nie było to potrzebne. Niedawno przełączyliśmy go na VARCHAR i zapisaliśmy 15 GB tylko na tym stole(był bardzo napisany). Ponadto, jeśli masz wtedy indeks na ten tabela i chcesz dołączyć tę kolumnę lub utworzyć indeks złożony, po prostu zwiększyłeś Rozmiar pliku indeksu.

Bądź rozważny w swojej decyzji; w rozwoju SQL i definicjach danych rzadko występuje "domyślna odpowiedź" (oczywiście poza unikaniem kursorów za wszelką cenę).

Waham się dodać jeszcze jedną odpowiedź tutaj, ponieważ jest już sporo, ale kilka punktów trzeba zrobić, które nie zostały lub nie zostały przedstawione jasno.

Po pierwsze: czy nie Zawsze używaj NVARCHAR. Jest to bardzo niebezpieczne i często kosztowne podejście. I nie jest lepiej powiedzieć "nigdy nie używaj kursorów", ponieważ są one czasami najbardziej skutecznym sposobem rozwiązania konkretnego problemu i powszechnym obejściem wykonywania pętli WHILE prawie zawsze będzie wolniejszy od prawidłowo wykonanego kursora.

Jedynym czasem, kiedy powinieneś używać terminu "zawsze", jest poradzenie "zawsze rób to, co najlepsze dla sytuacji". Przyznam, że jest to często trudne do określenia, szczególnie podczas próby zrównoważenia krótkoterminowych zysków w czasie rozwoju (menedżer: "potrzebujemy tej funkcji - o której nie wiedziałeś przed chwilą-tydzień temu!") z długoterminowymi kosztami utrzymania (kierownik, który początkowo naciskał na zespół, aby ukończyć 3-miesięczny projekt w 3-tygodniowym sprincie: "dlaczego mamy te problemy z wydajnością? Jak moglibyśmy zrobić X, który nie ma elastyczności? Nie stać nas na sprint czy dwa, żeby to naprawić. Co możemy zrobić w ciągu tygodnia, żebyśmy mogli wrócić do naszych priorytetowych rzeczy? I zdecydowanie musimy spędzać więcej czasu na projektowaniu, aby tak się nie działo!").

Po Drugie: odpowiedź @gbn dotyka kilku bardzo ważnych punktów, które należy wziąć pod uwagę przy podejmowaniu pewnych decyzji dotyczących modelowania danych, gdy ścieżka nie jest w 100% Jasne. Ale jest jeszcze więcej do rozważenia: {]}

• Rozmiar plików dziennika transakcji
• Czas potrzebny na replikację (jeśli używasz replikacji)
• Czas potrzebny na ETL (jeśli ETLing)
W 2006 roku firma została założona przez firmę Garmin, która od 2007 roku zajmuje się produkcją i dystrybucją sprzętu komputerowego.]}
• Rozmiar kopii zapasowych
• Czas potrzebny na wykonanie kopii zapasowej
[[56]}Czas potrzebny na wykonanie przywracania (może to być kiedyś ważne; -)
• Rozmiar potrzebne do tempdb
[56]} wydajność wyzwalaczy (dla wstawianych i usuwanych tabel, które są przechowywane w tempdb) W przeciwieństwie do poprzednich wersji, nie jest to możliwe.]} Możliwość uzyskania nowego miejsca na dysku, gdy dyrektor finansowy mówi, że w zeszłym roku wydał milion dolarów na SAN, więc nie zezwoli na kolejne 250 tysięcy dolarów na dodatkową przestrzeń dyskową]}
• Czas potrzebny do wykonania operacji wstawiania i aktualizacji
• Długość czas potrzebny na utrzymanie indeksu
• etc, etc, etc.

Marnowanie przestrzeni ma ogromny efekt kaskady na całym systemie. Napisałem artykuł na ten temat: dysk jest tani! ORLY? (wymagana darmowa rejestracja; przepraszam, że nie kontroluję tej polityki).

Po Trzecie: podczas gdy niektóre odpowiedzi nieprawidłowo koncentrują się na aspekcie "to jest mała aplikacja" , a niektóre prawidłowo sugerują ,aby "używać tego, co jest odpowiednie", Żadna odpowiedzi dostarczyły prawdziwych wskazówek O. P. ważnym szczegółem wspomnianym w pytaniu jest to, że jest to strona internetowa dla ich szkoły. Świetnie! Możemy więc zasugerować, że:

• Fields for student and / or Faculty names should prawdopodobnie be NVARCHAR ponieważ, z czasem, staje się coraz bardziej prawdopodobne, że nazwiska z innych kultur pojawią się w tych miejscach.
• ale dla Adresów Ulic i nazw miast? Cel aplikacji nie został określony (byłoby to pomocny), ale zakładając, że rekordy adresowe, jeśli istnieją, odnoszą się tylko do określonego regionu geograficznego( tj. pojedynczego języka / kultury), użyj VARCHAR z odpowiednią stroną kodową (która jest określona na podstawie zestawiania pola).
• W przypadku przechowywania kodów ISO stanu i/lub kraju (nie ma potrzeby przechowywania INT / TINYINT ponieważ kody ISO są stałej długości, czytelne dla człowieka i dobrze, standard :) użyj {[8] } dla kodów dwuliterowych i CHAR(3) Jeśli używasz kodów trzyliterowych. I rozważ użycie binarnego Zestawienie takie jak Latin1_General_100_BIN2.
• jeśli przechowujesz kody pocztowe (np. kody pocztowe), używaj VARCHAR ponieważ jest to międzynarodowy standard, aby nigdy nie używać żadnej litery poza A-Z. i tak, nadal używaj VARCHAR nawet jeśli przechowujesz tylko kody pocztowe USA, a nie INT, ponieważ kody pocztowe nie są numerami, są ciągami znaków, a niektóre z nich mają wiodące "0". I rozważ użycie binarnego zestawienia, takiego jak Latin1_General_100_BIN2.
• jeśli przechowujesz adresy e-mail i / lub adresy URL, użyj NVARCHAR, ponieważ oba mogą teraz zawierać Unicode postaci.
• i tak dalej....

Po czwarte: teraz, gdy masz NVARCHAR Dane zajmujące dwa razy więcej miejsca niż potrzeba dla danych, które ładnie pasują do VARCHAR ("pasuje ładnie" = nie zmienia się w"?") i jakoś, jakby za pomocą magii, aplikacja rozrosła się i teraz są miliony rekordów w co najmniej jednym z tych pól, gdzie większość wierszy jest standardowymi ASCII, ale niektóre zawierają znaki Unicode, więc musisz zachować NVARCHAR, rozważ po:

1. Jeśli używasz SQL Server 2008-2016 RTM i są w wersji Enterprise lub jeśli używasz SQL Server 2016 SP1 (który udostępnił kompresję danych we wszystkich wersjach) lub nowszych, możesz włączyć kompresję danych . Kompresja danych może (ale nie zawsze) kompresować dane Unicode w polach NCHAR i NVARCHAR. Czynniki determinujące to:

   1. NCHAR(1 - 4000) i NVARCHAR(1 - 4000) użyj standardowego schematu kompresji dla Unicode , ale tylko począwszy od SQL Server 2008 R2 i tylko dla danych w wierszu, a nie przepełnienia! Wydaje się to być lepsze niż zwykły algorytm kompresji wiersza / strony.
   2. NVARCHAR(MAX) i XML (i chyba też VARBINARY(MAX), TEXT, i NTEXT) Dane znajdujące się w wierszu (Nie poza wierszem w LOB lub stronach przepełnienia) mogą być przynajmniej skompresowane, ale nie wiersz jest skompresowany. Oczywiście kompresja strony zależy od wielkości wartości w wierszu: testowałem z VARCHAR (MAX) i zobaczyłem, że 6000 znaków/bajtów wierszy będzie nie kompresować, ale 4000 znaków/bajtów.
   3. dowolne dane poza wierszem, LOB lub OVERLOW = brak kompresji dla Ciebie!

2. Jeśli używasz SQL Server 2005 lub 2008 - 2016 RTM i nie w wersji Enterprise Edition, możesz mieć dwa pola: jedno VARCHAR i jedno NVARCHAR. Na przykład, załóżmy, że przechowujesz adresy URL, które są w większości podstawowymi znakami ASCII (wartości 0 - 127) i dlatego pasują do VARCHAR, ale czasami mają znaki Unicode. Twój schemat może zawierać następujące elementy 3 pola:

     ...
     URLa VARCHAR(2048) NULL,
     URLu NVARCHAR(2048) NULL,
     URL AS (ISNULL(CONVERT(NVARCHAR([URLa])), [URLu])),
     CONSTRAINT [CK_TableName_OneUrlMax] CHECK (
                       ([URLa] IS NOT NULL OR [URLu] IS NOT NULL)
                   AND ([URLa] IS NULL OR [URLu] IS NULL))
   );
   

   W tym modelu można tylko wybrać z [URL] kolumny obliczeniowej. Aby wstawić i aktualizować, określ, którego pola użyć, sprawdzając, czy konwersja zmienia przychodzącą wartość, która musi być typu NVARCHAR:

   INSERT INTO TableName (..., URLa, URLu)
   VALUES (...,
           IIF (CONVERT(VARCHAR(2048), @URL) = @URL, @URL, NULL),
           IIF (CONVERT(VARCHAR(2048), @URL) <> @URL, NULL, @URL)
          );
   

3. Możesz odczytać przychodzące wartości do VARBINARY(MAX), a następnie rozpakować po wyjściu:

   • dla SQL Server 2005 - 2014: możesz użyć SQLCLR. SQL# (biblioteka SQLCLR, którą napisałem) pochodzi z Util_GZip i Util_GUnzip w wersji darmowej
   • dla SQL Server 2016 i nowszych: możesz użyć wbudowanych funkcji COMPRESS i DECOMPRESS, które są również GZip.

4. Jeśli używasz SQL Server 2017 lub nowszego, możesz przyjrzeć się uczynieniu tabeli zbiorczym indeksem magazynu kolumn.

5. Chociaż nie jest to jeszcze opłacalna opcja, SQL Server 2019 wprowadza natywne wsparcie dla UTF-8 W VARCHAR / CHAR typy danych. Obecnie jest zbyt wiele błędów z nim do użycia, ale jeśli są one stałe, to jest to opcja dla niektórych scenariuszy. Proszę zobaczyć mój post, " natywne wsparcie UTF-8 W Sql Server 2019: Zbawiciel czy fałszywy prorok?", do szczegółowej analizy tej nowej funkcji.

Ponieważ Twoja aplikacja jest mała, zasadniczo nie ma znaczącego wzrostu kosztów korzystania z nvarchar nad varchar, i oszczędzasz sobie potencjalne bóle głowy w dół drogi, jeśli masz potrzebę przechowywania danych unicode.

Przez ostatnie kilka lat wszystkie nasze projekty używały NVARCHAR do wszystkiego, ponieważ wszystkie te projekty są wielojęzyczne. Importowane dane z zewnętrznych źródeł (np. plik ASCII, itp.) jest konwertowany do Unicode przed włożeniem do bazy danych.

Nie spotkałem jeszcze żadnych problemów związanych z wydajnością z większych indeksów, itp. Indeksy zużywają więcej pamięci, ale pamięć jest tania.

Niezależnie od tego, czy używasz procedur składowanych, czy konstruujesz SQL w locie, upewnij się, że wszystkie stałe łańcuchowe są poprzedzone znakiem N (np. SET @foo = N ' Hello world.';) więc stała jest również Unicode. Pozwala to uniknąć konwersji typu string w czasie wykonywania.

YMMV.

Ogólnie rzecz biorąc; zacznij od najdroższego typu danych, który ma najmniejsze ograniczenia. Umieścić go w produkcji . Jeśli wydajność zaczyna być problemem, dowiedz się, co tak naprawdę jest przechowywane w tych kolumnach nvarchar. Czy są tam jakieś postacie, które nie pasowałyby do varchar? Jeśli nie, Przełącz na varchar. Nie próbuj pre-optymalizacji, zanim dowiesz się, gdzie jest ból. Domyślam się, że Wybór pomiędzy nvarchar/varchar nie jest tym, co spowolni Twoją aplikację w przewidywalnej przyszłości. Pojawią się inne części aplikacji, w których dostrajanie wydajności da ci znacznie więcej bang for the bucks .

Mogę mówić z doświadczenia na ten temat, strzeż się nvarchar. O ile nie jest to absolutnie wymagane, to typ pola danych niszczy wydajność w większej bazie danych. Odziedziczyłem bazę danych, która bolała pod względem wydajności i przestrzeni. Udało nam się zmniejszyć rozmiar bazy danych o 30GB o 70%! Było kilka innych modyfikacji, które miały pomóc w wydajności, ale jestem pewien, że varchar ' S znacznie pomogły również w tym. Jeśli twoja baza danych ma potencjał wzrostu tabel do miliona + zapisy za wszelką cenę trzymają się z dala od nvarchar.

Często zajmuję się tym pytaniem w pracy:

• Kanały FTP inwentaryzacji i cen - opisy przedmiotów i inne teksty były w nvarchar, gdy VARCHAR działał dobrze. Przekonwertowanie ich na varchar zmniejszyło Rozmiar pliku prawie o połowę i naprawdę pomogło przy przesyłaniu.

• Powyższy scenariusz działał dobrze, dopóki ktoś nie umieścił w opisie przedmiotu specjalnego znaku (może znaku towarowego, nie pamiętam)

Nadal nie używam nvarchar za każdym razem nad varchar. Jeśli są jakieś wątpliwości lub potencjał znaków specjalnych, używam nvarchar. Uważam, że używam varchar głównie wtedy, gdy jestem w 100% kontroli tego, co wypełnia pole.

Dlaczego w całej tej dyskusji nie było wzmianki o UTF-8? Możliwość przechowywania pełnego zakresu znaków unicode nie oznacza, że zawsze trzeba przydzielać dwa bajty na znak (lub "punkt kodowy", aby użyć terminu UNICODE). Wszystkie ASCII to UTF-8. Czy SQL Server sprawdza pola VARCHAR (), że tekst jest ściśle ASCII (tzn. górny bajt bit zero)? Mam nadzieję, że nie.

Jeśli następnie chcesz przechowywać unicode i chcesz kompatybilność ze starszymi aplikacjami tylko ASCII, I można by pomyśleć, że użycie varchar () i UTF-8 byłoby magiczną kulą: używa więcej miejsca tylko wtedy, gdy potrzebuje.

Dla tych z Was, którzy nie znają UTF-8, mogę polecić podkład .

Będą wyjątkowe przypadki, kiedy będziesz chciał celowo ograniczyć typ danych, aby upewnić się, że nie zawiera znaków z określonego zestawu. Na przykład miałem scenariusz, w którym musiałem przechowywać nazwę domeny w bazie danych. Internacjonalizacja nazw domen w tym czasie nie była wiarygodna, więc lepiej było ograniczyć wprowadzanie danych na poziomie podstawowym i pomóc uniknąć potencjalnych problemów.

Jeśli używasz NVARCHAR tylko dlatego, że wymaga tego systemowa procedura składowana, najczęściej występująca jest niewytłumaczalnie sp_executesql, A dynamiczny SQL jest bardzo długi, lepiej byłoby z punktu widzenia wydajności wykonywać wszystkie manipulacje łańcuchami (konkatenacja, wymiana itp.) w VARCHAR następnie konwersja wyniku końcowego na NVARCHAR i podanie go do parametru proc. Więc nie, nie zawsze używaj NVARCHAR!