w tym artykule omówimy typ danych SQL varchar, w tym podstawową definicję i przegląd, różnice w stosunku do varchar(n), obsługę UTF-8, zestawianie, kwestie wydajności i wiele innych.
dane odgrywają kluczową rolę w każdej organizacji, a atrybut, za pomocą którego są zdefiniowane, nazywa się jego typem danych. W prostych słowach typ danych określa, jakiego rodzaju dane może przechowywać dowolny obiekt, zmienna lub wyrażenie., Jako programista SQL, tworząc tabelę SQL, musimy zrozumieć i zdecydować, jaki typ danych będzie zawierał każda kolumna w tabeli. Podobnie jak każdy inny język programowania, SQL obsługuje również gamę typów danych, które mogą zawierać dane całkowite, dane daty i czasu, dane znaków itp. i umożliwia definiowanie własnych typów danych. SQL varchar jest jednym z najbardziej znanych i najczęściej używanych typów danych wśród partii. W tym artykule omówimy różne aspekty varchar SQL Server w SQL server.
Poniżej znajduje się kontur, który omówimy w tym bloku.,
- Wprowadzenie do typu danych SQL Server varchar w SQL Server
- zastosowanie varchar dla dużych bloków tekstu
- Co nowego w SQL Server 2019 preview dla typu danych varchar?,
- wpływ kolacji na varchar SQL w SQL Server
- obsługa UTF-8 z VARCHAR w SQL Server 2019 CTP
- SQL Server VARCHAR dla konwersji danych i wyświetlania danych
- rozważania dotyczące przechowywania i wydajności przy użyciu SQL Server varchar
- wpływ na długość ciągu SQL varchar z funkcjami CAST i CONVERT
przejdźmy do przodu i zobacz wyżej wymienione w akcji.
czym jest varchar w SQL?
jak sama nazwa wskazuje, varchar oznacza zmienne dane znaków., Znany również jako zmienny znak, jest to nieokreślony typ danych string długości. Może pomieścić cyfry, litery i znaki specjalne. Microsoft SQL Server 2008 (i wyżej) może przechowywać do 8000 znaków jako maksymalną długość łańcucha przy użyciu typu danych varchar. SQL varchar zwykle zawiera 1 bajt na znak i 2 więcej bajtów dla informacji o długości. Zaleca się stosowanie VARCHAR jako typu danych, gdy kolumny mają zmienną długość i rzeczywiste dane są o wiele mniejsze od podanej pojemności. Przejdźmy do SSMS i zobaczmy, jak działa varchar.,
poniższy przykład tworzy trzy zmienne (imię, płeć i wiek) z VARCHAR jako typem danych i przypisanymi do nich różnymi wartościami. Jak wynika z zestawów wynikowych pokazanych poniżej, domyślnie Długość łańcucha kolumn SQL varchar wynosi 1 i zwraca tylko pierwszą wartość zmiennych (reszta łańcucha jest obcięta), gdy dla typu danych varchar nie jest przekazywana Długość łańcucha. Funkcja len() służy do określenia liczby znaków przechowywanych w kolumnie varchar.
czym różni się SQL varchar(max) od varchar(n)?,
są sytuacje, w których programiści SQL (w tym Ja) zazwyczaj definiują typ danych varchar bez długości, a następnie nie mogą wstawiać rekordów łańcuchowych do tabeli SQL, dzieje się tak dlatego, że SQL Server przydziela 1 przestrzeń znakową jako wartość domyślną do kolumny varchar, która jest zdefiniowana bez długości. W praktycznych scenariuszach, varchar (n) jest używany do przechowywania wartości zmiennej długości jako łańcuch znaków, tutaj 'n' oznacza Długość łańcucha w bajtach i może wynosić do 8000 znaków., Teraz przejdźmy dalej i zobaczmy, jak możemy przechowywać dane SQL varchar o długości łańcucha znaków w kolumnie tabeli SQL. Poniższy skrypt wytworzy tabelę Demovarchar z niektórymi danymi w niej zawartymi. A ekran wyników pokazuje rekordy 7 pracowników na podstawie ich działów, wieku itp.
Załóżmy, że w organizacji pojawił się nowy pracownik i jako programiści danych SQL musielibyśmy wstawić ten nowy rekord do powyższej tabeli za pomocą polecenia INSERT SQL. Poniżej przedstawiono jeden z takich przykładów.,
|
1
|
INSERT INTO Demovarchar VALUES(’Newton Hamilton', 'Isaac',’M',’Design Head',69)
|
Oops, SQL Server encountered an error and terminated the statement saying string or binary data would be truncated., Stało się tak, ponieważ kolumna LastName varchar(10) może pomieścić do 10 znaków i tutaj próbujemy wstawić nowy rekord o długości łańcucha (’Newton Hamilton'), który jest wyraźnie większy niż 10 znaków. Jako szybką poprawkę możemy zmienić tabelę i zwiększyć typ danych kolumny SQL varchar, powiedzmy do varchar(50), aby wstawić nowy wiersz. Wykonaj poniższy skrypt, aby zmienić i wstawić nowy rekord do tabeli., Dodatkowo, można użyć funkcji len() i DATALENGTH() do określenia liczby znaków i rozmiaru pamięci odpowiednio w bajtach, które są przechowywane w kolumnie varchar.
zauważyliśmy powyżej, jak możemy ustawić lub zmienić długość łańcucha w kolumnie SQL varchar, aby spełnić potrzeby biznesowe. Rozważmy jednak scenariusz, w którym nie jesteśmy pewni rozmiaru danych, które zostaną załadowane do naszych tabel SQL, w takich okolicznościach sprawdzenie i zmiana rozmiaru typu danych dla każdej kolumny nie jest realnym wyborem., Jedną z opcji do obsługi tego może być ustawienie długości łańcucha na wyższym pasku w kolumnie SQL Server varchar(pod warunkiem, że masz przybliżone oszacowanie, jaka długość kolumny łańcuchowej będzie w przybliżeniu).
ważnym punktem, który należy wziąć pod uwagę, możemy użyć długości łańcucha do varchar(8000) tylko, ponieważ jest to maksymalna liczba znaków, które może pomieścić typ danych SQL varchar(n). Tak więc w przypadku, gdy istnieje szansa, że długość ciągu kolumny varchar może przekroczyć 8000 bajtów, użycie varchar (8001) lub cokolwiek wyższego spowoduje błąd., One short example demonstrating this fact is shown below.
|
1
2
|
DECLARE @name AS varchar(8001) = 'john parker d”souza';
SELECT @name Name
|
SQL Server 2005 got around this limitation of 8KB storage size and provided a workaround with varchar(max)., Jest to nie-Unicode duży typ danych znaków o zmiennej długości i może przechowywać maksymalnie 2^31-1 bajtów (2 GB) znaków nie-Unicode.
Kiedy po raz pierwszy zapoznałem się z pojęciami varchar(n) i SQL varchar, częstym pytaniem, jak każdy inny początkujący, którego miałem, było dlaczego nie możemy po prostu zadeklarować kolumny typu danych varchar(8500) lub wyższej, ponieważ mamy varchar (max), który zajmuje się pamięcią masową do 2GB i dlaczego mamy używać varchar (<=8000) lub VARCHAR (max)?, Otrzymałem odpowiedzi na małe badania, że SQL Server używa strony do przechowywania danych, a rozmiar każdej strony to 8KB (bez nagłówka strony, rozmiaru przesunięć wierszy). Jeśli dane, które mają być zapisane, są mniejsze lub równe 8000 bajtów, VARCHAR(n) lub VARCHAR (max) przechowuje je w wierszu. Jeśli jednak dane przekroczą rozmiar 8000 bajtów, to są traktowane jako duży obiekt(LOB) i nie są przechowywane w wierszu, ale w oddzielnych stronach LOB (LOB_DATA)., Row w takim przypadku będzie miał tylko wskaźnik do strony danych LOB, w której obecne są rzeczywiste dane, a SQL Server automatycznie przypisze do strony wskaźnik przekroczenia przepływu w celu manipulowania wierszami danych. W skrócie, jeśli wiesz, że dane mogą przekroczyć 8000 bajtów, lepszym rozwiązaniem jest użycie varchar (max) jako typu danych.
możemy odwołać się do DMV sys. dm_db_index_physical_stats, aby zobaczyć, jaki rodzaj alokacji stron (in_row_data data/LOB_DATA/ ROW_OVERFLOW_DATA) jest wykonywany., Możesz również sprawdzić ten link, jeśli chcesz uzyskać szczegółowe wyjaśnienie, w jaki sposób SQL Server ćwiczy limity wierszy i stron dla typów danych varchar(n) i VARCHAR(max).
przejdźmy szybko do SSMS i zobaczmy, jak możemy użyć varchar(max). Wykonaj następujący skrypt, aby wstawić 1 rekord, w którym wartość kolumny StringCol w każdym wierszu wynosi 15 000 znaków B (tj. 15 000 bajtów).
jednym z ograniczeń używania varchar(max) jest to, że nie możemy utworzyć indeksu, który ma varchar(max) jako kolumnę klucza, zamiast tego zaleca się wykonanie indeksu pełnotekstowego w tej kolumnie.,
a quick note to make – from here to the last leg of this article, we will mention varchar in place of VARCHAR(n). Nie traktuj go jako varchar z domyślną wartością = 1.
aby dowiedzieć się więcej ciekawych różnic między varchar(n) i VARCHAR(max) w SQL Server, rozważ przeczytanie tego artykułu, porównując typy danych VARCHAR(max) i VARCHAR(N) W Sql Server.
obsługa UTF-8 z SQL Server 2019 CTP
zanim przejdziemy do tego, co ma do zaoferowania funkcja SQL Server 2019 preview dla SQL varchar, szybko przyjrzyjmy się jeszcze jednemu interesującemu typowi danych – najpierw 'nvarchar'., Podobnie jak SQL Server varchar, mamy SQL nvarchar , prefiks n w nvarchar oznacza Unicode, tzn. przechowuje zarówno dane Unicode, jak i nie-Unicode. Kluczową różnicą między varchar i nvarchar jest sposób ich przechowywania, VARCHAR jest przechowywany jako zwykłe 8-bitowe dane (1 bajt na znak), a nvarchar przechowuje dane po 2 bajty na znak. Z tego powodu nvarchar może pomieścić do 4000 znaków i zajmuje podwójną przestrzeń jako SQL varchar. Możesz przejść przez ten link, aby dowiedzieć się więcej o nvarchar w SQL Server.,
wraz z publicznym podglądem SQL Server 2019 Microsoft ogłosił wsparcie dla kodowania znaków UTF-8 dla istniejących typów danych(varchar i char). Dla tych, którzy nie są świadomi UTF-8, to skrót od Unicode Transformation Format i jest kodowaniem opartym na Unicode, które obsługuje wiele języków. 8 w UTF-8 oznacza, że używa 1 bajtu (8-bitów) do reprezentowania znaku w pamięci. Podobnie, UTF-16 używa 16 bitów (2 bajty) do reprezentowania znaku. Ograniczymy zakres tego nowego rozszerzenia SQL Server 2019 CTP do „SQL varchar” tylko w tym artykule.,
To ulepszenie ma następujący wpływ na SQL Server: is
- Poprawia kompatybilność danych
dopóki SQL Server 2019 CTP, typ danych SQL varchar nie miał możliwości przechowywania tylko Danych innych niż Unicode i dzięki temu podglądowi możemy teraz utworzyć kolumnę varchar do przechowywania danych Unicode w ramach kolacji włączonych przez UTF-8 (_UTF8). UTF-8 jest dozwolony w typach danych varchar i jest włączony podczas tworzenia lub zmiany kolacji obiektu na kolację z przyrostkiem UTF8. Pomaga to zminimalizować problemy z konwersją postaci.,
- redukcja pamięci masowej i poprawa wydajności
obsługa UTF-8 dla typu danych varchar zapewnia znaczne oszczędności pamięci masowej w zależności od używanego zestawu znaków. Dla przykładu, przy użyciu kolacji z włączonym UTF-8, Zmiana typu danych kolumny z nvarchar(20) na varchar(20) oferuje znaczny spadek wymagań dotyczących przechowywania, ponieważ nvarchar(20) wymaga 40 bajtów do przechowywania, a varchar(20) potrzebuje 20 bajtów dla tego samego ciągu Unicode.
Ważna uwaga poboczna – ponieważ to ulepszenie jest nadal w wersji przedpremierowej, możemy spodziewać się więcej progresji na tym froncie w najbliższej przyszłości., Jednak istniejące typy danych Unicode (UTF-16) (nchar, nvarchar i ntext) pozostają niezmienione w SQL Server 2019 preview.
Collation with SQL varchar in SQL Server 2019 CTP
Collation in SQL Server definiuje konfiguracje określające różne reguły, takie jak rozróżnianie wielkości liter, czułość akcentu, sortowanie, typy znaków i szerokość itp. Zrozumienie wszystkich tych właściwości i ich pracy z danymi staje się bardzo ważne. Zestawienie może być ustawione na poziomie serwera, bazy danych, wyrażenia lub kolumny., UTF-8 obsługuje zestawianie na poziomie bazy danych lub kolumn w SQL Server 2019 CTP i jest włączone, gdy tworzysz lub zmieniasz zestawianie bazy danych lub kolumn na zestawianie z przyrostkiem UTF8.
jeśli wykonasz poniższe zapytanie przeciwko SQL Server 2019 CTP, będziesz mógł zobaczyć wszystkie kolacje obsługiwane przez UTF-8 na Twojej instancji SQL Server za pomocą funkcji (fn_helpcollations()).,d=”284f85b8b5″>
|
1
2
3
|
SELECT Name, Description
FROM fn_helpcollations()
WHERE Name like '%UTF8′;
|
With SQL Server 2019 preview version, we can assign Unicode collations (UTF-8 supported) as well for SQL varchar columns using the COLLATE clause while declaring the varchar column., W ten sposób do danych danej kolumny zostanie zastosowana specjalna klasyfikacja bez wpływu na resztę bazy danych.
ponieważ w tym poście mamy do czynienia z typem danych SQL Server varchar, zobaczmy, jak działa zestawianie kolumn z typem danych SQL varchar. Wykonaj poniższy kod, aby zmienić zestawianie kolumn SQL Server varchar z jednego typu zestawiania na przyrostek _UTF8. Możesz przeczytać więcej na temat zestawiania Baz Danych tutaj.,TE table demovarcharcollate
rola VARCHARA SQL w konwersji danych i wyświetlaniu danych
SQL Server VARCHAR jest szeroko stosowany w wyświetlaniu danych w pożądanych formatach za pomocą funkcji CONVERT i Cast w SQL Server., Real data zajmuje się mieszanką typów danych i musi być ze sobą kompatybilne (tzn. należą do tego samego typu danych), zanim dokonamy z nimi porównania. SQL Server obsługuje zarówno konwersje niejawne, jak i jawne.
- Uwaga: Sprawdź przegląd funkcji SQL Cast i SQL CONVERT, aby uzyskać więcej informacji o tym, jak możemy wykonać te konwersje w celu zapewnienia zgodności danych.
z nieustanną potrzebą formatowania i wyświetlania danych w wymaganym wyjściu, SQL varchar jest bardzo przydatny., Jako programista SQL uważam, że bardzo proste jest używanie konwertera / cast z typem danych varchar do wykonywania przypisań lub przekształceń na danych, szczególnie dla pól daty.
używam Table FactInternetSales z przykładowego DB AdventureWorksDW2017, aby pokazać, jak działa ta funkcja. Możesz odwołać się do dowolnej tabeli z polami datetime i money/float w celu praktyki. Poniższy skrypt konwertuje dwie kolumny datetime na typy SQL varchar w stylu 102 i 107, aby wyświetlić dane w formacie yyyy.mm.dd i Mon dd, yyyy., Ponadto kolumna SalesAmount z Money jako typem danych jest konwertowana na varchar, a styl 3 jest stosowany do wyświetlania kwoty z przecinkami, jak pokazano na zrzucie ekranu poniżej. Dodatkowo, powiedzmy, chcielibyśmy zobaczyć dane tylko dla zamówień złożonych w roku 2010, używając funkcji CAST do konwersji kolumny datetime na dane varchar, porównanie łańcuchów jest wykonywane w klauzuli WHERE. Możesz również przejść do daty konwersji SQL, aby znaleźć więcej informacji na temat formatów i stylów konwersji daty.,
wpływ na długość łańcuchów w SQL varchar z funkcjami CAST i CONVERT
SQL Server przechowuje dane o długich łańcuchach w powszechnie używanym typie danych varchar i pomocne staje się poznanie oczekiwanej i maksymalnej długości łańcuchów, aby wyświetlić wyniki w interfejsie użytkownika. Skopiuj i wykonaj poniższy kod, gdzie przekazujemy długi łańcuch w nieokreślonej długości zmiennej varchar (@demovarchar), a także w innej zmiennej o określonej długości varchar (@demovarcharwithcast)., Microsoft przyjmuje 30 jako domyślną długość dla SQL VARCHAR (z nieokreśloną długością varchar) w SQL Server, gdy jest używany z funkcjami CAST i CONVERT. W naszym przypadku, mimo że długość łańcucha wynosiła 52, zwraca on 30 jako długość pokazaną w ostatnim wyniku wyjściowym.
jedną z ważnych kwestii, którą należy tutaj zauważyć, jest to, że gdy zostanie utworzone nieokreślone pole VARCHAR o długości, domyślna długość takiego pola wynosi 1 (pokazana na Czerwono poniżej)., Gdy długość varchar jest nieokreślona i jest używana z funkcjami CAST lub CONVERT, CAST lub CONVERT zwraca n = 30 jako domyślną długość ciągu tej konwersji (zaznaczoną niebieskim kolorem poniżej).,le') jako 'LenOFStringPassed'
SELECT DATALENGtH(cast(@demovarcharwithcast AS varchar(60))) AS 'VarcharLengthSpecifiedWithCast'
rozważania dotyczące przechowywania i wydajności przy użyciu SQL varchar
typy danych takie jak VARCHAR, char i nvarchar są używane do przechowywania danych łańcuchowych w SQL Server., SQL VARCHAR przechowuje zmienną długość łańcucha, podczas gdy SQL char przechowuje stałą długość łańcucha. Oznacza to, że SQL Server VARCHAR przechowuje tylko te znaki, które mu przypisujemy, A char przechowuje maksymalną przestrzeń kolumn bez względu na łańcuch znaków, który przechowuje.
ze względu na stałą długość pól, dane są pobierane bezpośrednio z kolumny bez jakiejkolwiek manipulacji danymi, a Wyszukiwanie indeksów w warcharze jest wolniejsze niż w polach znaków. CHAR jest lepszy pod względem wydajności niż VARCHAR, jednak zajmuje niepotrzebne miejsce w pamięci, gdy dane nie mają stałej długości., Dlatego w przypadkach, gdy rozmiar dysku nie jest problemem, zaleca się użycie CHAR.
w prostych słowach powiedzmy, że mamy kolumnę z varchar(150) = 'sqlshack' – to zajmie 8 bajtów(sqlshack) + 2 bajty dla informacji o długości = 10 bajtów w rzeczywistości i dla kolumny z char (150) = 'sqlshack' – to pochłonie całe 150 bajtów na dysku, niezależnie od tego, co przekazujemy jako łańcuch. Poniższy przykład pokazuje, jak CHAR wykorzystuje maksymalną przydzieloną przestrzeń (150), aby zmieścić się w przekazanym łańcuchu znaków i jak Kolumna varchar wykorzystuje tylko potrzebną przestrzeń.,
najważniejsze jest użycie typu danych, który pasuje do naszych potrzeb. Możesz użyć SQL varchar, gdy rozmiary kolumny znacznie się różnią, użyj varchar (max), gdy istnieje prawdopodobieństwo, że długość ciągu znaków może przekroczyć 8000 bajtów, użyj char, gdy rozmiary kolumny są stałe i użyj nvarchar, jeśli istnieje wymóg przechowywania danych Unicode lub wielojęzycznych.
podsumowanie
typy danych odgrywają podstawową rolę w projektowaniu baz danych, ale są często pomijane., Dobre zrozumienie i dokładne wykorzystanie typów danych zapewnia prawidłowy charakter i długość danych jest wypełniana w tabelach. Celem tej porady jest pomoc w zrozumieniu podstawowych cech i funkcji SQL Server varchar wraz z jego aspektami wydajności i przechowywania w SQL Server. Omówiliśmy również ostatnie postępy w warcharze SQL w SQL Server 2019 Preview.
Zobacz również
Możesz sprawdzić te Inne artykuły, aby kontynuować naukę o typach danych SQL.,
- zrozumienie typu danych GUID w SQL Server
- przestrzenne typy danych w SQL Server
- Autor
- Ostatnie posty
ma głębokie doświadczenie w projektowaniu rozwiązań analitycznych i analitycznych oraz zapewnianiu ich stabilności, niezawodności i wydajności. Jest również certyfikowana w SQL Server i przeszła certyfikaty takie jak 70-463: Implementing Data Warehouse with Microsoft SQL Server.,
Zobacz wszystkie posty Gauri Mahajan
- poznaj model i dane usługi Azure Analysis Services – 4 lutego 2021 r.
- pierwsze kroki z usługą Azure usługi analizy – 13 stycznia 2021 r.
- połącz dane usługi Azure Databricks z pulpitem usługi Power BI – 1 czerwca 2020 r.
Dodaj komentarz