Varchar что это

Varchar что это

Типы данных CHAR и VARCHAR очень схожи между собой, но различаются по способам их хранения и извлечения.

В столбце типа CHAR длина поля постоянна и задается при создании таблицы. Эта длина может принимать любое значение между 1 и 255 (что же касается версии MySQL 3.23, то в ней длина столбца CHAR может быть от 0 до 255 ). Величины типа CHAR при хранении дополняются справа пробелами до заданной длины. Эти концевые пробелы удаляются при извлечении хранимых величин.

Величины в столбцах VARCHAR представляют собой строки переменной длины. Так же как и для столбцов CHAR , можно задать столбец VARCHAR любой длины между 1 и 255 . Однако, в противоположность CHAR , при хранении величин типа VARCHAR используется только то количество символов, которое необходимо, плюс один байт для записи длины. Хранимые величины пробелами не дополняются, наоборот, концевые пробелы при хранении удаляются (описанный процесс удаления пробелов отличается от предусмотренного спецификацией ANSI SQL).

Если задаваемая в столбце CHAR или VARCHAR величина превосходит максимально допустимую длину столбца, то эта величина соответствующим образом усекается.

Различие между этими двумя типами столбцов в представлении результата хранения величин с разной длиной строки в столбцах CHAR(4) и VARCHAR(4) проиллюстрировано следующей таблицей:

Величина	CHAR(4)	Требуемая память	VARCHAR(4)	Требуемая память
»	‘ ‘	4 байта	»	1 байт
‘ab’	‘ab ‘	4 байта	‘ab’	3 байта
‘abcd’	‘abcd’	4 байта	‘abcd’	5 байтов
‘abcdefgh’	‘abcd’	4 байта	‘abcd’	5 байтов

Извлеченные из столбцов CHAR(4) и VARCHAR(4) величины в каждом случае будут одними и теми же, поскольку при извлечении концевые пробелы из столбца CHAR удаляются.

Если при создании таблицы не был задан атрибут BINARY для столбцов, то величины в столбцах типа CHAR и VARCHAR сортируются и сравниваются без учета регистра. При задании атрибута BINARY величины в столбце сортируются и сравниваются с учетом регистра в соответствии с порядком таблицы ASCII на том компьютере, где работает сервер MySQL. Атрибут BINARY не влияет на процессы хранения или извлечения данных из столбца.

Атрибут BINARY является «прилипчивым». Это значит, что, если в каком-либо выражении использовать столбец, помеченный как BINARY , то сравнение всего выражения будет выполняться как сравнение величины типа BINARY .

MySQL может без предупреждения изменить тип столбца CHAR или VARCHAR во время создания таблицы. See section 6.5.3.1 Молчаливые изменения определений столбцов.

SQL-Ex blog

Когда использовать CHAR, VARCHAR или VARCHAR(MAX)

Добавил Sergey Moiseenko on Четверг, 21 июля. 2022

В каждой базе данных имеются различные виды данных, которые нужно хранить. Некоторые данные строго числовые, в то время как другие данные состоят только из букв или комбинации букв, чисел и даже специальных символов. Даже при простом хранении данных в памяти или на диске требуется, чтобы каждая часть данных имела тип. Выбор правильного типа зависит от характеристик сохраняемых данных. В этой статье объясняется разница между CHAR, VARCHAR и VARCHAR(MAX).

При выборе типа данных столбца необходимо подумать о характеристиках данных, чтобы назначить правильный тип данных. Будет ли каждое значение иметь одну и ту же длину, или размер будет сильно различаться от значения к значению? Как часто будут меняться данные? Будет ли длина столбца меняться со временем? Могут быть и другие факторы, подобные эффективному использованию пространства и производительности, которые могут привести вас к принятию того или иного типа данных.

Типы данных CHAR, VARCHAR и VARCHAR(MAX) могут хранить символьные данные. В этой статье будут обсуждаться и сравниваться эти три различных типа символьных данных. Приведенная информация призвана помочь вам выбрать подходящий среди этих трех типов данных.

Символьный тип данных фиксированной длины CHAR

Тип данных CHAR является типом данных фиксированной длины. Он может хранить буквы, числа и специальные символы в строках размером до 8000 байт. Тип данных CHAR наилучшим образом используется для хранения данных, которые имеют сопоставимую длину. Например, двухсимвольные коды штатов США, односимвольные коды половой принадлежности, номера телефонов, почтовые коды и т.п. Столбец CHAR является не лучшим выбором для хранения данных, у которых существенно варьируется длина. Столбцы, хранящие данные типа адресов или мемо-полей не подходят для столбцов с типом данных CHAR.

Это не означает, что столбец CHAR не может содержать значения, которые варьируются по размеру. Когда в столбец CHAR заносятся строки, которые короче, чем длина столбца, справа будут добавляться пробелы. Число этих пробелов определяется разностью между размером столбца и длиной сохраняемых символов. Поскольку столбцы CHAR при необходимости полностью добиваются пробелами, каждый столбец занимает одно и то же пространство на диске или в памяти. Концевые пробелы также играют роль при поиске в столбцах типа CHAR. Подробнее об этом несколько позже.

Символьный тип данных переменной длины VARCHAR

Столбцы VARCHAR, как подразумевает название, хранят данные переменной длины. Они могут хранить буквы, числа и специальные символы, как и столбец CHAR, и поддерживают строки размером до 8000 байт. Столбец переменной длины занимает только то место, которое требуется для хранения строки символов, и не дополняются никакими пробелами. По этой причине столбцы VARCHAR отлично подходят для хранения строк, которые сильно варьируются по размеру.

Для поддержки столбцов переменной длины необходимо, помимо самих данных, хранить их длину. Поскольку длина необходима для вычислений и используется ядром базы данных при чтении и сохранении столбцов переменной длины, считается, что они несколько менее производительны по сравнению со столбцами CHAR. Однако, если учесть, что они используют только то пространство, которое им необходимо, экономия места на диске сама по себе может компенсировать потери производительности при использовании типа VARCHAR.

Различия типов данных CHAR и VARCHAR

Фундаментально отличие CHAR от VARCHAR состоит в том, что тип данных CHAR имеет фиксированную длину, в то время как тип данных VARCHAR поддерживает столбцы данных переменной длины. Но он и похожи. Оба предназначены для хранения алфавитно-цифровых данных. Для лучшего понимания разницы между этими двумя типами, посмотрите таблицу 1, где сделан обзор их подобия и отличий.

Таблица 1: сравнение типов CHAR и VARCHAR

Что означает «N» в CHAR(N) или VARCHAR(N)

«N» означает не максимальное число символов, которое может храниться в столбце CHAR или VARCHAR, а максимальное число байтов, которое займет тип данных. SQL Server имеет различные коллации для хранения символов. Некоторые наборы символов, подобные Latin, хранят каждый символ и одном байте пространства. В то время как другие наборы символов, например, японский, требуют нескольких байтов на символ.

Столбцы CHAR и VARCHAR могут хранить до 8000 байтов. Если используется односимвольный набор, то столбец CHAR или VARCHAR может хранить до 8000 символов. Если используется мультибайтовая коллация, максимальное число символов, которое может хранить CHAR или VARCHAR, будет меньше 8000. Обсуждение коллации выходит за рамки этой статьи, но если вы хотите больше узнать об однобайтовом и многобайтовыми наборами символов, обратитесь к документации.

Ошибка усечения

Если столбец определен как CHAR(N) или VARCHAR(N), «N» представляет число байтов, которое может храниться в столбце. При заполнении столбца CHAR(N) или VARCHAR(N) символьной строкой может возникнуть подобная ошибка усечения, показанная на рисунке 1.

Рис.1 Ошибка усечения

Эта ошибка возникает при попытке сохранить строку, размер которой превышает максимальную длину столбца
CHAR или VARCHAR. Когда возникает подобная ошибка усечения, код TSQL прерывается, и последующий код не выполняется. Это можно продемонстрировать следующим кодом в листинге 1.

Листинг 1: код, приводящий к ошибке усечения

USE tempdb; 
GO 
CREATE TABLE MyTable (A VARCHAR(10)); 
INSERT INTO MyTable VALUES ('This String'); 
-- Продолжение 
SELECT COUNT(*) FROM MyTable; 
GO

Код в листинге 1 вызывает ошибку, показанную на рисунке 1, при выполнении оператора INSERT. Оператор SELECT, следующий за оператором INSERT, не был выполнен из-за ошибки усечения. Ошибка усечения и прерывание выполнения скрипта могут давать вам желаемую функциональность, но иногда вы не хотите получать ошибку усечения, прерывающую ваш код.

Предположим, что необходимо перенести данные из старой системы в новую. В старой системе есть таблица MyOldData, которая содержит данные, созданные с помощью скрипта в листинге 2.

Листинг 2: таблица в старой системе

USE tempdb; 
GO 
CREATE TABLE MyOldData (Name VARCHAR(20), ItemDesc VARCHAR(45)); 
INSERT INTO MyOldData 
VALUES ('Widget', 'This item does everything you would ever want'), 
('Thing A Ma Jig', 'A thing that dances the jig'); 
GO

Планируется перенести данные из таблицы MyOldData в таблицу MyNewTable, которая имеет меньший размер столбца ItemDesc. Код в листинге 3 используется для создания новой таблицы и переноса данных.

Листинг 3: перенос данных в новую таблицу

USE tempdb; 
GO 
CREATE TABLE MyNewData (Name VARCHAR(20), ItemDesc VARCHAR(40)); 
INSERT INTO MyNewData SELECT * FROM MyOldData; 
SELECT * FROM MyNewData; 
GO

При выполнении кода в листинге 3 вы получите ошибку усечения, подобную ошибке на рис.1, и никакие данные перенесены не будут.

Для успешного переноса данных необходимо определиться с тем, что делать с усечением, чтобы гарантировать перенос всех строк. Одним из методов является усечение описания элемента (ItemDesc) с помощью функции SUBSTRING при выполнении кода в листинге 4.

Листинг 4: Устранение ошибки усечения с помощью SUBSTRING

DROP Table MyNewData 
GO 
USE tempdb; 
GO 
CREATE TABLE MyNewData (Name VARCHAR(20), ItemDesc VARCHAR(40)); 
INSERT INTO MyNewData SELECT Name, substring(ItemDesc,1,40) 
FROM MyOldData; 
SELECT * FROM MyNewData; 
GO

При выполнении кода в листинге 4 все записи переносятся. При этом ItemDesc превышающая 40 будет усекаться с помощью функции SUBSTRING, но есть и другой способ.

Если вы хотите избежать ошибки усечения без написания специального кода усечения столбцов, длина которых слишком велика, можно выключить параметр ANSI_WARNINGS, как показано в листнге 5.

Листинг 5: устранение ошибки усечения при выключении ANSI_WARNINGS.

DROP Table MyNewData 
GO 
USE tempdb; 
GO 
CREATE TABLE MyNewData (Name VARCHAR(20), ItemDesc VARCHAR(40)); 
SET ANSI_WARNINGS OFF; 
INSERT INTO MyNewData SELECT * FROM MyOldData; 
SET ANSI_WARNINGS ON; 
SELECT * FROM MyNewData; 
GO

При выключении параметра ANSI_WARNINGS ядро SQL Server не следует стандарту ISO для некоторых состояний ошибок, одним из которых является состояние ошибки усечения. При отключении этого параметра SQL Server автоматически усекает исходный столбец для соответствия его целевым столбцам без возвращения ошибки. Следует осторожно использовать выключение параметра ANSI_WARNINGS, поскольку при этом могут также остаться незамеченными другие ошибки. Поэтому изменение параметра ANSI_WARNINGS следует использовать ситуативно.

VARCHAR(MAX)

Тип данных VARCHAR(MAX) подобен типу данных VARCHAR в том, что он поддерживает символьные данные переменной длины. VARCHAR(MAX) отличается от VARCHAR тем, что он поддерживает строки символов длиной вплоть до 2 Гб (2,147,483,647 байтов). Вам следует рассмотреть использование VARCHAR(MAX) только тогда, когда каждая строка, сохраняемая в этом типе данных существенно варьируется по длине, и значение может превышать 8000 байтов.

Вы можете спросить себя, почему бы не использовать VARCHAR(MAX) везде вместо использования VARCHAR(N)? Вы можете, но имеется несколько причин, почему этого делать не стоит:

столбцы VARCHAR(MAX) не могут быть включены в ключевые столбцы индекса;

столбцы VARCHAR(MAX) не позволяют ограничить длину столбца;

для хранения больших строк столбцы VARCHAR(MAX) используют единицы распределения LOB_DATA. Хранилище LOB_DATA существенней медленней, чем использование единиц распределения хранилища IN_ROW_DATA;

хранилище LOB_DATA не поддерживает сжатие страниц и строк.

Можно подумать, что столбцы VARCHAR(MAX) будут устранять ошибку усечения, которую мы наблюдали ранее. Это частично верно при условии, что вы не пытаетесь сохранить строку со значением длинее, чем 2,147,483,647 байтов. Если вы попытаетесь записать строку, размер которой превышает 2,147,483,647 байтов, вы получите ошибку, показанную на рисунке 2.

Рис.2: ошибка, когда размер строки превышает 2 Гб

Столбцы VARCHAR(MAX) следует использовать только тогда, когда вы знаете, что некоторые сохраняемые данные будут ожидаемо превосходить 8000-байтовый предел для столбца VARCHAR(N), и все данные будут короче предела 2 Гб для типа данных VARCHAR(MAX).

Проблемы конкатенации со столбцами CHAR

Когда столбец CHAR не полностью заполнен строкой символов, неиспользованные символы замещаются пробелами. Когда столбец CHAR дополняется пробелами, это может вызвать некоторые проблемы при конкатенации столбцов CHAR. Для лучшего понимания рассматрим несколько примеров, которые используют таблицу, созданную в листинге 6.

Листинг 6: таблица для примеров Sample

USE tempdb; 
GO 
CREATE TABLE Sample ( 
ID int identity, 
FirstNameChar CHAR(20), 
LastNameChar CHAR(20), 
FirstNameVarChar VARCHAR(20), 
LastNameVarChar VARCHAR(20)); 
INSERT INTO Sample VALUES ('Greg', 'Larsen', 'Greg', 'Larsen');

Таблица Sample, созданная в листинге 6, содержит 4 столбца. Первые два определены как CHAR(20), а вторые два — VARCHAR(20). Эти столбцы будут использоваться для хранения моего имени и фамилии.

Для демонстрации проблем конкатенации, связанной с дополняемыми столбцами CHAR, выполните код в листинге 7.

Листинг 7: демонстрация проблемы конкатенации

SELECT FirstNameChar + LastNameChar AS FullNameChar, 
FirstNameVarChar + LastNameVarChar AS FullNameVarChar FROM Sample;

Результат выполнения кода в листнге 7

Здесь столбец FirstNameCHAR содержит несколько пробелов между именем и фамилией. Эти пробелы являются пробелами, дополненными в столбце FirstNameCHAR при сохранении имени в столбце типа CHAR. Столбец FullNameVARCHAR не содержит пробелов между именем и фамилией. Если длина записываемого значения меньше длины столбца VARCHAR, пробелы не добавляются.

При конкатенации столбцов CHAR вам может понадобиться удалить концевые пробелы, чтобы получить желаемый результат. Вы можете использовать функцию RTRIM для удаления пробелов, как показано в листинге 8.

Листинг 8: удаление концевых пробелов с помощью функции RTRIM

SELECT RTRIM(FirstNameChar) + RTRIM(LastNameChar) AS FullNameChar, 
FirstNameVarChar + LastNameVarChar AS FullNameVarchar 
FROM Sample;

Результат выполнения скрипта показан на рисунке ниже.

Пр использовани функции RTRIM все дополнительные пробелы, добавленные к столбцам FirstNameCHAR и LastNameCHAR удаляются перед выполнением конкатенации.

Проблемы с поиском пробелов в столбцах CHAR

Поскольку столбцы CHAR могут дополняться пробелами, поиск пробела может стать проблемой.

Предположим, что имеется таблица, содержащая фразы, подобные создаваемым в листинге 9.

Листинг 9: создание таблицы Phrase

USE tempdb; 
GO 
CREATE TABLE Phrase (PhraseChar CHAR(100)); 
INSERT INTO Phrase VALUES ('Worry Less'), 
('Oops'), 
('Think Twice'), 
('Smile');

Некоторые фразы в таблице Phrase состоят из одного слова, а другие содержать два. Для поиска в таблице Phrase всех фраз, которые содержат два слова, воспользуемся кодом в листинге 10.

Листинг 10: попытка найти фразы из двух слов

SELECT PhraseChar FROM Phrase WHERE PhraseChar like '% %';

Результат выполнения скрипта показан ниже.

Почему были возвращены все фразы из таблицы Phrase, хотя имеется только две строки, состоящие из двух слов? Поисковая строка % % также находит пробелы, которые были добавлены в конце значения столбца. И опять, функция RTRIM может использоваться, чтобы гарантировать, что дополненные пробелы не будут включены в результаты поиска при выполнении кода в листинге 11.

Листинг 11: удаление концевых пробелов

SELECT PhraseChar FROM Phrase 
WHERE RTRIM(PhraseChar) like '% %';

Вы можете сами проверить, что будут возвращены только фразы из двух слов.

Сравнение производительности VARCHAR и CHAR

Количество работы, которое выполняет движок базы данных при сохранении и извлечения столбцов VARCHAR, больше, чем для столбца CHAR. При каждом извлечении информации из столбца VARCHAR движок базы данных должен использовать информацию о длине, хранящуюся вместе с данными в столбце VARCHAR.
Использование информации о длине вызывает лишние циклы работы ЦП. В то же время фиксированная длина столбца CHAR позволяет SQL Server более легко выполнять навигацию по записям столбца CHAR, благодаря его фиксированной длине.

При работе со столбцами CHAR и VARCHAR проблемой может стать дисковое пространство. Поскольку столбец типа CHAR имеет фиксированную длину, он всегда будут занимать одинаковое пространство диска. Столбцы VARCHAR изменяются по размеру, поэтому необходимое пространство основывается на размере хранимых строк, а не на размере в определении столбца. Когда подавляющее большинство значений, хранимых в столбце CHAR, меньше заданного размера, то использование столбца VARCHAR может использовать меньше дискового пространства. Когда используется меньше дискового пространства, требуется меньше операций ввода/вывода при работе с данными столбца, что означает улучшение производительности. Эти два соображения определяют выбор между CHAR и VARCHAR.

CHAR, VARCHAR и VARCHAR(MAX)

Столбцы CHAR фиксированы по размеру, в то время как столбцы VARCHAR и VARCHAR(MAX) поддерживают данные переменной длины. Столбцы CHAR следует использовать для столбцов, длина которых меняется незначительно. Строковые значения, которые значительно варьируются по длине и не превышают 8000 байтов, следует хранить в столбце VARCHAR. Если у вас огромные строки (свыше 8000 байтов), то следует использовать VARCHAR(MAX). При использовании столбцов VARCHAR вместе с данными хранится информация о длине строки. Вычисление и хранение значения длины для столбца VARCHAR означает, что SQL Server должен выполнить немного больше работы для записи и извлечения столбцов VARCHAR по сравнению типом данных CHAR.

Когда вам предстоит решить, должен ли новый столбец иметь тип CHAR, VARCHAR или VARCHAR(MAX), задайте себе несколько вопросов, чтобы выбрать подходящий тип. Все ли сохраняемые строковые значения близки по размеру? Если да, то следует выбрать CHAR. Если сохраняемые строки значительно варьируются по размеру, и их размер не превышает 8000, используйте VARCHAR. В противном случае следует использовать VARCHAR(MAX).

SQL-Ex blog

Что можно и чего нельзя делать с помощью SQL VARCHAR для более быстрых баз данных

Добавил Sergey Moiseenko on Среда, 9 марта. 2022

Мы собираемся глубоко изучить SQL VARCHAR, тип данных, который имеет дело со строками.
VARCHAR является лишь одним из строковых типов в SQL. Чем он отличается от остальных?

Что такое SQL VARCHAR? (с примерами)

VARCHAR — это строковый или символьный тип данных переменного размера. Вы можете хранить тут буквы, числа и символы. Начиная с SQL Server 2019, вы можете использовать полный диапазон символов Unicode при использовании коллации с поддержкой UTF-8.

Вы можете объявить или переменные этого типа, используя VARCHAR[(n)], где n обозначает размер строки в байтах. n меняется в диапазоне от 1 до 8000. Это множество символьных данных. Более того, вы можете объявить тип, используя VARCHAR(MAX), если вам требуются гигантские строки до 2Гб. Этого достаточно, чтобы сохранить ваш список секретов и личных вещей в дневнике! Однако следует отметить, что этот тип можно объявить без указания размера, и тогда по умолчанию принимается 1.

Давайте возьмем пример.

DECLARE @actor VARCHAR(20) = 'Robert Downey Jr.'; 
DECLARE @movieCharacter VARCHAR(10) = 'Iron Man'; 
DECLARE @movie VARCHAR = 'Avengers'; 
SELECT @actor, @movieCharacter, @movie

На рисунке первые два столбца имеют заданный размер. Для третьего столбца размер не указан. Поэтому слово “Avengers” усекается, поскольку в этом случае по умолчанию принимается 1 символ.

Теперь давайте попробуем что-нибудь огромное. Но отметьте, что выполнение этого запроса займет некоторое время — 23 секунды на моем ноутбуке.

-- Это займет время 
DECLARE @giganticString VARCHAR(MAX); 
SET @giganticString = REPLICATE(CAST('kage bunshin no jutsu' AS VARCHAR(MAX)),100000000) 
SELECT DATALENGTH(@giganticString)

Для генерации огромных строк мы реплицировали kage bunshin no jutsu 100 миллионов раз. Обратите внимание на CAST внутри REPLICATE. Если вы не преобразуете строковое выражение к VARCHAR(MAX), результат будет усечен только до 8000 символов.

Но что представляет собой SQL VARCHAR в сравнении с другими строковыми типами данных?

CHAR против VARCHAR

В отличие от VARCHAR, CHAR является символьным типом данных фиксированной длины. Вне зависимости от того, большое или малое значение вы поместите в переменную типа CHAR, окончательный размер будет равен размеру переменной. Проверьте следующие сравнения.

DECLARE @tvSeriesTitle1 VARCHAR(20) = 'The Mandalorian'; 
DECLARE @tvSeriesTitle2 CHAR(20) = 'The Mandalorian'; 
SELECT DATALENGTH(@tvSeriesTitle1) AS VarcharValue, 
DATALENGTH(@tvSeriesTitle2) AS CharValue

Размер строки “The Mandalorian” — 15 символов. Поэтому столбец VarcharValue правильно отображает его. Но CharValue сохраняет размер 20, добавляя 5 пробелов справа.

NVARCHAR против VARCHAR

Две основные вещи приходят на ум, когда сравниваются эти типы данных.

Во-первых, это размер в байтах. Каждый символ в NVARCHAR имеет удвоенный размер по сравнению с VARCHAR. Диапазон значений NVARCHAR(n) — от 1 только до 4000.

Второе, это символы, которые могут тут храниться. NVARCHAR может хранить мультиязычные символы, например, корейские, японские, арабские и т.д. Если вы планируете хранить корейский K-Pop в своей базе данных, этот тип данных вам подойдет.

Рассмотрим пример. Мы собираемся использовать группу K-Pop 세븐틴 или Seventeen (17) по-английски.

DECLARE @kpopGroupKorean NVARCHAR(5) = N'세븐틴'; 
 
SELECT @kpopGroupKorean AS KPopGroup, 
DATALENGTH(@kpopGroupKorean) AS SizeInBytes, 
LEN(@kpopGroupKorean) AS [NoOfChars]

Вышеприведенный код выведет строковое значение, его размер в байтах и число символов. Если эти символы не являются Юникодом, число символов равно размеру в байтах. Но не в этом случае. Посмотрите рисунок ниже.

Видите? Если NVARCHAR содержит 3 символа, размер в байтах вдвое больше. Это также справедливо, если вы используете английские символы.

А как насчет NCHAR? NCHAR является альтернативой CHAR для символов Юникод.

SQL VARCHAR с поддержкой UTF-8

VARCHAR с поддержкой UTF-8 возможна на уровне сервера, уровне базы данных или уровне столбца таблицы при изменении информации о коллации. Используемая коллация должна поддерживать UTF-8.

Коллация сервера

На рис.5 представлено окно SQL Server Management Studio, где показана коллация сервера.

Коллация базы данных

А на рис.6 показаны коллация базы данных AdventureWorks.

Коллация столбца таблицы

Как серверная, так и коллация базы данных показали, что UTF-8 не поддерживается. Строка коллации должна иметь суффикс _UTF8 для поддержки UTF-8. Но, тем не менее, вы можете использовать поддержку UTF-8 на уровне столбца таблицы. Посмотрите пример.

CREATE TABLE SeventeenMemberList 
( 
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
KoreanName VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL, 
EnglishName VARCHAR(20) NOT NULL 
)

Код выше применяет коллацию Latin1_General_100_BIN2_UTF8 для столбца KoreanName. Хотя это VARCHAR, а не NVARCHAR, этот столбец будет принимать символы корейского языка. Давайте вставим несколько записей и просмотрим их.

INSERT INTO SeventeenMemberList 
(KoreanName, EnglishName) 
VALUES 
(N'에스쿱스','S.Coups') 
,(N'원우','Wonwoo') 
,(N'민규','Mingyu') 
,(N'버논','Vernon') 
,(N'우지','Woozi') 
,(N'정한','Jeonghan') 
,(N'조슈아','Joshua') 
,(N'도겸','DK') 
,(N'승관','Seungkwan') 
,(N'호시','Hoshi') 
,(N'준','Jun') 
,(N'디에잇','The8') 
,(N'디노','Dino') 
SELECT * FROM SeventeenMemberList 
ORDER BY KoreanName 
COLLATE Latin1_General_100_BIN2_UTF8

Мы используем имена из K-pop группы SEVENTEEN, используя корейские и английские варианты. Обратите внимание, что для корейских символов вы все же должны использовать префикс значения N, как и для значений NVARCHAR.

Затем, используя SELECT с ORDER BY вы также можете использовать коллацию. Вы можете это видеть на примере выше. Результат будет следовать правилам сортировки указанной коллации.

Хранение VARCHAR с поддержкой UTF-8

Но как хранятся эти символы? Если вы ожидаете 2 байта на символ, вас ждет сюрприз. Посмотрите рисунок 8.

Поэтому, если для вас большое значение имеет хранилище, рассмотрите таблицу ниже, когда используется VARCHAR с поддержкой UTF-8.

Таблица 1. Размер в байтах символов в VARCHAR с поддержкой UTF-8.

Наш корейский пример является восточно-азиатским скриптом, поэтому он содержит 3 байта на символ.

Теперь, когда мы описали и сравнили VARCHAR с другими строковыми типами данных, рассмотрим, что следует и чего не следует делать.

Делать при использовании SQL VARCHAR

1. Задавать размер

Что может пойти не так, если не указывать размер?

Усечение строки
Если вы ленитесь указывать размер, может произойти усечение строки. Вы уже видели пример этого выше.

Влияние на хранение и производительность
Другой вопрос — это хранение и производительность. Вам нужно устанавливать правильный размер для ваших данных, не больше. Но как это узнать? Чтобы избежать усечения в будущем, вы могли бы просто установить наибольший размер. Это VARCHAR(8000) или даже VARCHAR(MAX). И 2 байта будут сохранены как есть. То же самое с 2Гб. Это имеет значение?

Ответ на этот вопрос приводит нас к концепции хранения данных в SQL Server. У меня есть другая статья, подробно объясняющая это с примерами и иллюстрациями.

Вкратце, данные хранятся на 8-килобайтных страницах. Когда строка данных превышает этот размер, SQL Server перемещает её на другую единицу распределения страниц, называемую ROW_OVERFLOW_DATA.

Предположим, что у нас есть 2-х байтовые данные типа VARCHAR, которые могут поместиться на исходной единице распределения страниц. Когда вы сохраняете строку свыше 8000 байт, данные будут перемещаться на страницу переполнения строк. Затем снова уменьшите её размер, и она будет перемещена обратно на исходную страницу. Перемещение взад и вперед вызывает множество операций ввода/вывода, что становится узким местом в производительности. Извлечение её из двух страниц вместо одной также требует лишних операций ввода/вывода.

Другая причина — индексирование. VARCHAR(MAX) — это большое «НЕТ» в качестве ключа индекса. Между тем, VARCHAR(8000) превышает максимальный размер ключа индекса. Это 1700 байт для некластеризованных индексов и 900 байт — для кластеризованных индексов.

Влияние на преобразование данных
Есть еще один момент: преобразование данных. Попробуйте применить CAST без размера, как показано в коде ниже.

SELECT 
SYSDATETIMEOFFSET() AS DateTimeInput 
,CAST(SYSDATETIMEOFFSET() AS VARCHAR) AS ConvertedDateTime 
,DATALENGTH(CAST(SYSDATETIMEOFFSET() AS VARCHAR)) AS ConvertedLength

Этот код выполнит преобразование даты/времени с информацией часового пояса к VARCHAR.

Итак, если мы поленимся указать размер при использовании CAST или CONVERT, результат ограничивается только 30-ю символами.

Как насчет преобразования NVARCHAR к VARCHAR с поддержкой UTF-8? Ниже будет дано подробное объяснение, так что продолжайте чтение.

2. Используйте VARCHAR, если размер строки варьируется в широких пределах

Имена в базе данных AdventureWorks меняются по размеру. Одно из самых коротких имен — Min Su, в то время как самое длинное — Osarumwense Uwaifiokun Agbonile. т.е. между 6 и 31 символами, включая пробелы. Давайте импортируем эти имена в 2 таблицы и сравним VARCHAR и CHAR.

-- Таблица, использующая VARCHAR 
CREATE TABLE VarcharAsIndexKey 
( 
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
varcharName VARCHAR(50) NOT NULL 
) 
GO 
CREATE INDEX IX_VarcharAsIndexKey_varcharName ON VarcharAsIndexKey(varcharName) 
GO 
-- Таблица, использующая CHAR 
CREATE TABLE CharAsIndexKey 
( 
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY, 
charName CHAR(50) NOT NULL 
) 
GO 
CREATE INDEX IX_CharAsIndexKey_charName ON CharAsIndexKey(charName) 
GO 
INSERT INTO VarcharAsIndexKey (varcharName) 
SELECT DISTINCT 
LastName + ', ' + FirstName + ' ' + ISNULL(MiddleName,'') 
FROM AdventureWorks.Person.Person 
 
INSERT INTO CharAsIndexKey (charName) 
SELECT DISTINCT 
LastName + ', ' + FirstName + ' ' + ISNULL(MiddleName,'') 
FROM AdventureWorks.Person.Person 
GO

Какой вариант из 2 лучше? Давайте посмотрим на логические чтения с помощью нижеприведенного кода и проверим вывод STATISTICS IO.

SET NOCOUNT ON 
SET STATISTICS IO ON 
SELECT id, varcharName 
FROM VarcharAsIndexKey 
 
SELECT id, charName 
FROM CharAsIndexKey 
SET STATISTICS IO OFF

Чем меньше логических чтений, тем лучше. Здесь столбец CHAR использует их более чем в два раза больше по сравнению с VARCHAR. Таким образом, в этом примере побеждает VARCHAR.

3. Используйте VARCHAR в качестве ключа индекса вместо CHAR, когда значения варьируются по размеру

Что случится, если использовать их в качестве ключей индекса? Будет ли CHAR лучше, чем VARCHAR? Давайте использовать данные из предыдущего раздела, и ответим на этот вопрос.

Мы выполним запрос к тем же данным и проверим число логических чтений. В этом примере фильтр использует ключ индекса.

SET NOCOUNT ON 
SET STATISTICS IO ON 
SELECT varcharName FROM VarcharAsIndexKey 
WHERE varcharName = 'Sai, Adriana A' 
OR varcharName = 'Rogers, Caitlin D' 
SELECT charName FROM CharAsIndexKey 
WHERE charName = 'Sai, Adriana A' 
OR charName = 'Rogers, Caitlin D' 
SET STATISTICS IO OFF

Рис.11. Запрос к таблице, использующей ключ индекса типа CHAR требует больше логических чтений, чем при использовании VARCHAR

Следовательно, ключи индекса типа VARCHAR лучше, чем ключи индекса типа CHAR, когда ключ имеет переменный размер. А как для INSERT и UPDATE, которые будут изменять индексные записи?

При использовании INSERT и UPDATE

Давайте протестируем 2 случая, а затем проверим число логических чтений, как мы обычно делаем.

SET STATISTICS IO ON 
INSERT INTO VarcharAsIndexKey (varcharName) 
VALUES ('Ruffalo, Mark'), ('Johansson, Scarlett') 
INSERT INTO CharAsIndexKey (charName) 
VALUES ('Ruffalo, Mark'), ('Johansson, Scarlett') 
SET STATISTICS IO OFF

VARCHAR все еще лучше при вставке записей. А как для UPDATE?

SET STATISTICS IO ON 
UPDATE VarcharAsIndexKey 
SET varcharName = 'Hulk' 
WHERE varcharName = 'Ruffalo, Mark' 
UPDATE CharAsIndexKey 
SET charName = 'Hulk' 
WHERE charName = 'Ruffalo, Mark' 
SET STATISTICS IO OFF

Похоже, что VARCHAR опять побеждает.

В конце концов, он побеждает в нашем тесте, хотя и небольшого размера. У вас есть более крупный тестовый пример, который доказывает обратное?

4. Рассмотрите VARCHAR с поддержкой UTF-8 для мультиязычных данных (SQL Server 2019+)

Если в вашей таблице имеется смесь символов Юникод и не Юникод, вы можете рассмотреть использование VARCHAR с поддержкой UTF-8 вместо NVARCHAR Если бОльшая часть символов находится в диапазоне ASCII 0 — 127, это позволит сэкономить пространство по сравнению с использованием NVARCHAR.

Давайте выполним сравнение, чтобы увидеть что я имею в виду.

От NVARCHAR к VARCHAR с поддержкой UTF-8

Вы уже перевели свои базы данных на SQL Server 2019? Планируете ли вы перевести строковые данные на коллацию UTF-8? Наш пример будет использовать смесь японских и неяпонских символов, чтобы вы получили представление.

CREATE TABLE NVarcharToVarcharUTF8 
( 
NVarcharValue NVARCHAR(20) NOT NULL, 
VarcharUTF8 VARCHAR(45) COLLATE Latin1_General_100_BIN2_UTF8 NOT NULL 
) 
GO 
INSERT INTO NVarcharToVarcharUTF8 
(NVarcharValue, VarcharUTF8) 
VALUES 
(N'NARUTO-ナルト- 疾風伝',N'NARUTO-ナルト- 疾風伝'); -- NARUTO Shippûden 
SELECT 
NVarcharValue 
,LEN(NVarcharValue) AS nvarcharNoOfChars 
,DATALENGTH(NVarcharValue) AS nvarcharSizeInBytes 
,VarcharUTF8 
,LEN(VarcharUTF8) AS varcharNoOfChars 
,DATALENGTH(VarcharUTF8) AS varcharSizeInBytes 
FROM NVarcharToVarcharUTF8

Теперь проверим размер в байтах этих двух значений:

Сюрприз! При использовании NVARCHAR размер составляет 30 байт. Т.е. в 15 раз больше, чем 2 символа. Но при конвертациив в VARCHAR с поддержкой UTF-8 размер составляет только 27 байт. Почему 27? Посмотрите, как это вычисляется.

Таким образом, 9 символов имеют по одному байту каждый. Это интересно, поскольку при NVARCHAR английские буквы также занимают по 2 байта. Остальные японские символы занимают по 3 байта каждый.

Если бы все символы были японскими, то 15-символьная строка была бы 45 байтов длиной и так же занимала бы максимальный размер столбца VarcharUTF8. Обратите внимание, что размер столбца NVarcharValue меньше, чем размер VarcharUTF8.

Размеры могут быть не равны при преобразовании из NVARCHAR, или данные могут не поместиться. Вы можете обратиться к предыдущей таблице 1.

Рассмотрите влияние на размер при преобразовании NVARCHAR в VARCHAR с поддержкой UTF-8.

Что не следует делать при использовании SQL VARCHAR

1. Если размер строки фиксированный и не допускает NULL-значений, используйте CHAR вместо VARCHAR

Общее практическое правило гласит: когда требуется строка фиксированного размера, используйте CHAR. Я следую этому правилу, когда требованием к данным является дополнение строки пробелами справа. В противном случае, я использую VARCHAR. У меня есть несколько случаев применения, когда необходимо получить дамп строк фиксированной длины без разделителей в текстовом файле для передачи клиенту.

Кроме того, я использую столбцы типа CHAR, только если столбцы не будут содержать NULL. Почему? Поскольку размер столбцов CHAR при наличии NULL равен размеру столбца. Да, когда VARCHAR есть NULL, размер равен 1 вне зависимости от того, какой размер был определен. Выполните код ниже, чтобы убедиться в этом.

DECLARE @charValue CHAR(50) = NULL; 
DECLARE @varcharValue VARCHAR(1000) = NULL; 
 
SELECT 
DATALENGTH(ISNULL(@charvalue,0)) AS CharSize 
,DATALENGTH(ISNULL(@varcharvalue,0)) AS VarcharSize

2. Не используйте VARCHAR(n), если n будет превышать 8000 байт. Используйте вместо этого VARCHAR(MAX)

Есть ли у вас строка, которая превышает 8000 байт? Самое время использовать VARCHAR(MAX). Но для самых общих представлений данных, например, имен и адресов, VARCHAR(MAX) — это перебор и влияние на производительность. В моем профессиональной деятельности я не помню требования, которое привело бы к использованию VARCHAR(MAX).

3. При использовании мультиязычных символов в SQL Server 2017 и ниже. Используйте в этом случае NVARCHAR

Это очевидный выбор, если вы еще используете SQL Server 2017 и ниже.

Выводы

Тип данных VARCHAR хорошо послужил нам во многих аспектах. Я использовал его, начиная с SQL Server 7. Увы, иногда мы все еще делаем плохой выбор. Здесь мы определили и сравнили SQL VARCHAR с другими строковыми типами данных на примерах. Вот что следует делать и чего следует избегать, чтобы сделать базы данных быстрей:

• Определять размер n в VARCHAR[(n)], хотя этот параметр не является обязательным.
• Используйте его, когда размер строки значительно варьируется.
• Рассмотрите столбцы VARCHAR в качестве ключей индекса вместо CHAR.
• Если вы уже используете SQL Server 2019, рассмотрите вариант VARCHAR для мультиязычных строк с поддержкой UTF-8.

• Не используйте VARCHAR, когда размер строки фиксирован и не допускает NULL-значений.
• Не используйте VARCHAR(n), когда размер строки будет превышать 8000 байт.
• Не используйте VARCHAR для мультиязычных данных, если используется SQL Server 2017 и ранее.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

тип данных VARCHAR

тип данных VARCHAR
Стандартный тип данных Oracle, используемый для хранения символьных (алфавитно-цифровых) данных, аналогичный типу данных CHAR. В СУБД Oracle V6 типы данных CHAR и VARCHAR полностью эквивалентны. В сервере Oracle тип данных CHAR имеет фиксированную длину, а VARCHAR — переменную длину.
[http://www.morepc.ru/dict/]

Тематики

• информационные технологии в целом

EN

• VARCHAR
• VARCHAR datatype

Справочник технического переводчика. – Интент . 2009-2013 .

Смотреть что такое «тип данных VARCHAR» в других словарях:

• CUBRID — Тип Реляционная СУБД Разработчик Search Solutions Написана на C, C++ Операционная система Кроссплатформенное программное обеспечение Язык интерфейса Ан … Википедия
• SQL-92 — SQL 92 третья версия языка запросов к базам данных SQL. В отличие от стандарта SQL 89, третья версия стала весьма существенным обновлением языка. За исключением некоторых мелких противоречий стандарт SQL 89 практически полностью совместим… … Википедия
• ЛИНТЕР — Тип СУБД Разработчик … Википедия