В чем разница между char, nchar, varchar и nvarchar в SQL Server? Ms sql получить размер строки varchar

sql - Рекомендации по длине столбца SQL varchar

Никаких СУБД, которые я знаю, не имеет никакой "оптимизации", которая сделает длину VARCHAR с длиной 2^n более эффективной, чем одна с длиной max, которая не является степенью 2.

Я думаю, что ранние версии SQL Server фактически обрабатывали VARCHAR длиной 255 по сравнению с версией с максимальной максимальной длиной. Я не знаю, все ли так.

Для почти всех СУБД фактическое требуемое хранилище определяется только количеством символов, которые вы вставляете в него, а не длиной max, которую вы определяете. Таким образом, с точки зрения хранения (и, скорее всего, и производительности), не имеет значения, объявляете ли вы столбец как VARCHAR(100) или VARCHAR(500).

Вы должны видеть длину max для столбца VARCHAR как своего рода ограничение (или бизнес-правило), а не техническую/физическую вещь.

Для PostgreSQL лучшей настройкой является использование text без ограничения длины и CHECK CONSTRAINT, который ограничивает количество символов в соответствии с тем, что требуется вашему бизнесу.

Если это требование изменится, изменение ограничения проверки выполняется намного быстрее, чем изменение таблицы (поскольку таблицу не нужно переписывать)

То же самое может быть применено для Oracle и других - в Oracle это будет VARCHAR(4000) вместо text.

Я не знаю, существует ли разница в физической памяти между VARCHAR(max) и, например, VARCHAR(500) в SQL Server. Но, по-видимому, есть влияние производительности при использовании VARCHAR(max) по сравнению с varchar(8000).

Смотрите эту ссылку (опубликовано Erwin Brandstetter в качестве комментария)

Редактировать 2013-09-22

Относительно комментария Bigown:

В версиях Postgres до 9.2 (которые не были доступны при написании исходного ответа) изменение в определении столбца переписало всю таблицу, см., например, здесь. С 9.2 это уже не так, и быстрый тест подтвердил, что увеличение размера столбца для таблицы с 1,2 миллионами строк действительно занимает всего 0,5 секунды.

Для Oracle это, похоже, также верно, судя по тому, как требуется изменить столбец большой таблицы VARCHAR. Но я не мог найти для этого никаких ссылок.

Для MySQL в руководстве сказано "В большинстве случаев ALTER TABLE создает временную копию исходной таблицы". И мои собственные тесты подтверждают, что: запуск ALTER TABLE в таблице с 1,2 миллионами строк (так же, как в моем тесте с Postgres), чтобы увеличить размер столбца, занял 1,5 минуты. Однако в MySQL вы не можете использовать "обходной путь" для использования ограничения проверки для ограничения количества символов в столбце.

Для SQL Server я не мог найти четкую инструкцию по этому поводу, но время выполнения для увеличения размера столбца VARCHAR (опять же таблица из 1,2 миллиона строк выше) указывает, что нет переписать имеет место.

Изменить 2017-01-24

Кажется, я был (по крайней мере частично) неправильным в отношении SQL Server. См. этот ответ от Aaron Bertrand, который показывает, что заявленная длина столбцов nvarchar или VARCHAR имеет огромное значение для производительности.

qaru.site

sql-server - Максимальный размер строки SQL Server Vs Varchar (Max)

В Microsoft SQL Server данные (включая индексы) хранятся в одной или нескольких 8k (8192 байтах) "страницах". Существуют различные типы страниц, которые могут использоваться для обработки различных ситуаций (например, данных, LOB, индекса, AllocationMap и т.д.). На каждой странице есть заголовок, который представляет собой метаданные об этой странице и том, что она содержит.

Большинство данных сохраняется в самой строке, и одна или несколько из этих строк, в свою очередь, хранятся на странице для "данных в строке". Из-за пространства, занимаемого заголовком строки, наибольшая строка может быть (для "in-row" данных) составляет 8060 байт.

Однако не все данные хранятся в строке. Для некоторых типов данных данные могут быть фактически сохранены на странице "Данные LOB", в то время как указатель остается в данных "in-row":

Устаревшие/устаревшие типы LOB, которые никто больше не должен использовать (TEXT, NTEXT и IMAGE), по умолчанию всегда сохраняют свои данные на страницах LOB и всегда используют 16-байтовый указатель на эту страницу LOB.
Новые типы LOB (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) и XML) по умолчанию будут пытаться подгонять данные непосредственно в строке, если они подойдут. Кроме того, он будет хранить данные на страницах LOB и использовать указатель 24 - 72 байта (в зависимости от размера данных LOB).

Так вы можете хранить до 78 ГБ + 4 байта (не можете забыть о INT Primary Key;-) в одной строке: максимальный размер строки будет находиться между 940 байтами ((39 * 24 ) + 4) и 2812 байтов ((39 * 72) + 4). Но опять же, это только максимальный диапазон; если данные в каждом из полей 39 VARCHAR(MAX) составляют всего 10 байт, тогда все данные будут сохранены в строке, а размер строки будет 394 байта ((39 * 10) + 4).

Учитывая, что у вас так много полей переменной длины (независимо от того, являются ли они MAX или нет), единственный способ оценить размер будущих строк - это иметь хорошее представление о том, какие данные вы будете хранить в этой таблице. Несмотря на то, что таблица со всеми типами данных MAX или даже в большинстве случаев подразумевает, что никто действительно не знает, что будет храниться в этой таблице.

Вдоль этих строк следует отметить, что это ужасно смоделированное табличное/ужасное использование полей типа MAX и должно быть реорганизовано.

Подробнее о том, как структурируются страницы данных, см. мой ответ на следующий вопрос DBA.StackExchange:

SUM of DATALENGTHs не соответствует размеру таблицы из sys.allocation_units

qaru.site

server - Максимальный размер строки SQL Server Vs Varchar (Max)

В Microsoft SQL Server данные (включая индексы) хранятся в одной или нескольких 8k (8192 байтах) «страницах». Существуют различные типы страниц, которые могут использоваться для обработки различных ситуаций (например, данных, LOB, индекса, AllocationMap и т. Д.). На каждой странице есть заголовок, который представляет собой метаданные об этой странице и том, что она содержит.

Большинство данных хранится в самой строке, и одна или несколько из этих строк, в свою очередь, хранятся на странице для «данных в строке». Из-за пространства, занимаемого заголовком строки, наибольшая строка может быть (для «in-row» данных) составляет 8060 байт.

Однако не все данные хранятся в строке. Для некоторых типов данных, данные на самом деле могут быть сохранены на странице «LOB данных», а указатель остается в данных «в-ряд»:

Устаревшие/устаревшие типы LOB, что никто не должен использовать больше (TEXT, NTEXT и IMAGE), по умолчанию всегда сохраняют свои данные на страницах LOB и всегда используют 16-байтовый указатель на эту страницу большого объекта.
Новейшие типы LOB (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX) и XML), по умолчанию будет пытаться соответствовать данным непосредственно в строке, если она будет соответствовать. Кроме того, он будет хранить данные на LOB-страницах и использовать указатель 24 - 72 байта (в зависимости от размера данных LOB).

Это, как вы могли бы хранить до 78 Гб + 4 байта (не может забыть о INT первичном ключе ;-) в одной строке: максимальный размер строки будет находиться в диапазоне от 940 байт ((39 * 24) + 4) и 2812 байтов ((39 * 72) + 4). Но опять же, это только максимальный диапазон; если данные в каждом из полей 39 VARCHAR(MAX) составляют всего 10 байтов, тогда все данные будут сохранены в строке, а размер строки будет 394 байта ((39 * 10) + 4).

Вдоль этих строк следует указать, что это ужасно смоделированное стол/ужасное использование полей типа данных MAX и должно быть реорганизовано.

Для получения более подробной информации о том, как данные страницы структурированы, смотрите мой ответ на следующий вопрос: DBA.StackExchange

SUM of DATALENGTHs not matching table size from sys.allocation_units

stackoverrun.com

performance - Использование varchar (MAX) и TEXT на SQL Server

Основное определение

TEXT и VarChar(MAX) - это несимвольные символы большого размера переменной длины, которые могут хранить максимум 2147483647. Символы, отличные от Unicode (т.е. максимальный объем памяти: 2 ГБ).

Какой из них использовать?

По ссылка MSDN Microfost предлагает избегать использования типа данных Text и будет удаляться в будущих версиях Sql Server. Varchar (Max) - это рекомендуемый тип данных для хранения больших строковых значений вместо типа текстовых данных.

Хранилище In-Row или Out-of-Row

Данные столбца типа TEXT сохраняются вне строки на отдельных страницах данных LOB. Строка на странице данных таблицы будет содержать только 16-байтовый указатель на страницу данных LOB, где присутствуют фактические данные. Хотя данные столбца типа VarChar(MAX) хранятся в строке, если оно меньше или равно 8000 байт. Если значение столбца Varchar (max) пересекает 8000 байт, значение столбца Varchar (max) сохраняется на отдельных страницах данных LOB, а строка будет содержать только 16-байтовый указатель на страницу данных LOB, где присутствуют фактические данные. Поэтому In-Row Varchar (Max) подходит для поиска и поиска.

Поддерживаемые/неподдерживаемые функции

Некоторые строковые функции, операторы или конструкции, которые не работают в столбце "Тип текста", но они работают в столбце типа "VarChar (Max)".

Как известно, значения столбцов типа VarChar (Max) хранятся вне строки только в том случае, если длина значения, которое должно быть записано в нем, больше 8000 байт или недостаточно места в строке, иначе он сохранит его в строке. Поэтому, если большинство значений, хранящихся в столбце VarChar (Max), являются большими и хранятся вне строки, поведение поиска данных будет почти таким же, как в столбце "Тип текста".

Но если большинство значений, хранящихся в столбцах типа VarChar (Max), достаточно малы для хранения строки. Затем извлечение данных, в которых столбцы LOB не включены, требует большего количества страниц данных для чтения, поскольку значение столбца LOB хранится в строке на той же странице данных, где хранятся значения столбца, отличного от LOB. Но если запрос выбора включает столбец LOB, для этого требуется меньшее количество страниц для чтения данных по сравнению с столбцами типа текста.

Заключение

Используйте VarChar(MAX) тип данных, а не TEXT для хорошей производительности.

Источник

qaru.site

sql - sql varchar (max) vs varchar (исправить)

сначала позвольте мне разницу между обоими

В столбце nvarchar могут храниться любые данные Unicode. Столбец varchar ограничен 8-разрядной кодовой страницей. Некоторые люди думают, что вархар следует использовать, потому что он занимает меньше места. Я считаю, что это не правильный ответ. Codepage incompatabilities - это боль, а Unicode - это средство для проблем с кодировкой. В настоящее время с дешевым диском и памятью нет причин для того, чтобы тратить время на работу с кодовыми страницами.

Все современные операционные системы и платформы разработки используют Unicode. Используя nvarchar, а не varchar, вы можете избегать конверсий при каждом чтении или записи в базу данных. Конверсии требуют времени и подвержены ошибкам. И восстановление от ошибок преобразования является нетривиальной проблемой.

Если вы взаимодействуете с приложением, использующим только ASCII, я бы по-прежнему рекомендовал использовать Unicode в базе данных. Алгоритмы сопоставления ОС и базы данных будут работать лучше с Unicode. Unicode избегает проблем с конверсией при взаимодействии с другими системами. И вы будете готовиться к будущему. И вы всегда можете подтвердить, что ваши данные ограничены 7-разрядным ASCII для любой прежней системы, которую вы должны поддерживать, даже наслаждаясь некоторыми преимуществами полного хранилища Unicode.

И если вы используете размер исправления, предположите 5000, то вы можете сохранить upto 5000 только в том случае, если длина текста увеличится, тогда вы получите сообщение об ошибке. поэтому [PageContent] [varchar](max) NOT NULL, лучше, но если вы уверены, что длина строки не будет больше больше 5000, тогда [PageContent] varchar NOT NULL лучше

nchar [ ( n ) ] Строковые данные Unicode с фиксированной длиной. n определяет длину строки и должен быть значением от 1 through 4,000. Размер хранилища равен двум байтам. Если на кодовой странице коллинга используются двухбайтовые символы, размер хранилища по-прежнему равен n байтам. В зависимости от строки размер хранилища в n байтах может быть меньше значения, указанного для n. Синонимы ISO для nchar являются национальными char и национальными символами..

nvarchar [ ( n | max ) ] Строковые данные Unicode с переменной длиной. n определяет длину строки и может быть значением от 1 до 4000. max указывает, что максимальный размер хранилища составляет 2 ^ 31-1 байта (2 ГБ). Размер памяти в байтах в два раза превышает фактическую длину введенных данных + 2 байта. Синонимы ISO для nvarchar являются национальными char различными и национальными символами.

По мне используют nvarchar [ ( n | max ) ], поэтому вы не будете зависеть от длины строки

ref: для получения дополнительной информации https://msdn.microsoft.com/en-IN/library/ms186939.aspx

qaru.site

[tsql] Каковы варианты использования CHAR над VARCHAR в SQL? [sql-server]

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

Он зависит от реализации БД, но, как правило, VARCHAR использует еще один или два байта хранения (для длины или завершения) в дополнение к фактическим данным. Итак (при условии, что вы используете набор символов с одним байтом), сохраняя слово «FooBar»,

CHAR (6) = 6 байтов (без накладных расходов)
VARCHAR (10) = 8 байт (2 байта служебных данных)
CHAR (10) = 10 байт (4 байта служебных данных)

Нижняя строка CHAR может быть быстрее и эффективнее для данных относительно одинаковой длины (в пределах разницы длин двух символов).

Примечание . Microsoft SQL имеет 2 байта служебных данных для VARCHAR. Это может варьироваться от БД к БД, но обычно имеется как минимум 1 байт служебных данных, необходимых для указания длины или EOL на VARCHAR.

Как было отмечено Gaven в комментариях, если вы используете многобайтовый символ переменной длины, такой как UTF8, тогда CHAR сохраняет максимальное количество байтов, необходимое для хранения количества символов. Поэтому, если для хранения символа UTF8 требуется не более 3 байтов, тогда CHAR (6) будет фиксирован в 18 байт, даже если он хранит только латинские символы. Поэтому в этом случае VARCHAR становится намного лучшим выбором.

В дополнение к преимуществам производительности CHAR может использоваться для обозначения того, что все значения должны иметь одинаковую длину, например, столбец аббревиатур состояния США.

Char немного быстрее, поэтому, если у вас есть столбец, который вы знаете, будет определенной длиной, используйте char. Например, сохранение (M) ale / (F) emale / (U) неизвестно для пола или 2 символа для американского штата.

Есть преимущества в производительности, но здесь не упоминается: перемещение строк. С char, вы резервируете все пространство заранее. Итак, давайте скажем, что у вас есть символ (1000), и вы сохраняете 10 символов, вы будете использовать все 1000 символов пространства. В varchar2 (1000) вы будете использовать только 10 символов. Проблема возникает, когда вы изменяете данные. Допустим, вы обновили столбец, чтобы теперь содержать 900 символов. Возможно, что пространство для расширения varchar недоступно в текущем блоке. В этом случае движок БД должен перенести строку в другой блок и сделать указатель в исходном блоке на новую строку в новом блоке. Чтобы прочитать эти данные, движок БД теперь должен будет прочитать 2 блока. Никто не может двусмысленно сказать, что варчар или шар лучше. Существует пространство для компромисса во времени и рассмотрение вопроса о том, будут ли данные обновляться, особенно если есть хороший шанс, что он будет расти.

Я бы выбрал varchar, если столбец не сохранил фиксированное значение, как код состояния штата США, который всегда имеет 2 символа, а список действительных кодов штатов США не меняется часто :).

В любом другом случае, даже если вы храните хешированный пароль (фиксированный размер), я бы выбрал varchar.

Почему столбец типа char всегда выполняется с пробелами, что делает для столбца my_column определенным как char (5) со значением «ABC» внутри сравнения:

my_column = 'ABC' -- my_column stores 'ABC ' value which is different then 'ABC'

ложный.

Эта функция может привести к появлению многих раздражающих ошибок во время разработки и более сложному тестированию.

Я поддерживаю комментарий Джима МакКета.

Кроме того, индексирование и полное сканирование таблицы быстрее, если ваша таблица имеет только столбцы CHAR. В основном оптимизатор сможет предсказать, насколько велика каждая запись, если она имеет только столбцы CHAR, в то время как она должна проверять значение размера для каждого столбца VARCHAR.

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

Но вы, вероятно, не будете заботиться о производительности, если ваша таблица не огромна.

Вспомните мудрые слова Джикстры. Ранняя оптимизация производительности - это корень всего зла.

При вычислении фактического необходимого размера для значения столбца есть небольшая накладная часть обработки, и выделение пространства для Varchar, поэтому, если вы определенно уверены, как долго значение будет всегда, лучше использовать Char и избежать попадания.

Многие люди указали, что если вы знаете, что точная длина значения, использующая CHAR, имеет некоторые преимущества. Но при сохранении штатов США как CHAR (2) сегодня здорово, когда вы получаете сообщение от продаж, что «Мы только что сделали нашу первую продажу Австралии», вы находитесь в мире боли. Я всегда присылаю, чтобы переоценить, как долго я думаю, что поля должны быть скорее, чем делать «точные» предположения для покрытия будущих событий. VARCHAR предоставит мне большую гибкость в этой области.

Фрагментация. Char оставляет пространство, а VarChar - нет. Для разделения обновлений на varchar может потребоваться разделение страниц.

Использование CHAR (NCHAR) и VARCHAR (NVARCHAR) приводит к различиям в том, как сервер базы данных хранит данные. Первый вводит конечные пробелы; Я столкнулся с проблемой при использовании его с LIKE-оператором в функциях SQL SERVER. Поэтому я должен сделать это безопасным, используя VARCHAR (NVARCHAR) все время.

Например, если у нас есть таблица TEST (ID INT, Status CHAR (1)) , и вы пишете функцию для перечисления всех записей с определенным значением, например:

CREATE FUNCTION List(@Status AS CHAR(1) = '') RETURNS TABLE AS RETURN SELECT * FROM TEST WHERE Status LIKE '%' + @Status '%'

В этой функции мы ожидаем, что когда мы поместим параметр по умолчанию, функция вернет все строки, но на самом деле это не так. Измените тип данных @Status на VARCHAR, исправив проблему.

code-examples.net

sql-server - В чем разница между char, nchar, varchar и nvarchar в SQL Server?

Моя попытка обобщить и исправить существующие ответы:

Во-первых, char и nchar всегда будут использовать фиксированный объем пространства для хранения, даже если строка, которая будет сохранена, меньше доступного пространства, тогда как varchar и nvarchar будут использовать только столько хранения пространство, необходимое для хранения этой строки (плюс два байта служебных данных, предположительно для хранения длины строки). Поэтому помните, что "var" означает "переменная", как в переменном пространстве.

Второй важный момент для понимания состоит в том, что nchar и nvarchar хранить строки, используя ровно два байта на символ, тогда как char и varchar используют кодировку, определенную на кодовой странице коллиляции, которая обычно будет ровно один байт на символ (хотя есть исключения, см. ниже). Используя два байта на символ, можно хранить очень широкий диапазон символов, поэтому основная информация, которую следует помнить здесь, состоит в том, что nchar и nvarchar имеют тенденцию быть гораздо лучшим выбором, если вы хотите поддержку интернационализации, которую вы, вероятно, делаете.

Теперь для некоторых более тонких точек.

Во-первых, столбцы nchar и nvarchar всегда хранят данные, используя UCS-2. Это означает, что будет использоваться ровно два байта на символ, а любой символ Юникода в базовой многоязычной плоскости (BMP) можно сохранить в поле nchar или nvarchar. Однако не все символы Юникода могут быть сохранены. Например, согласно Википедии, кодовые пункты для египетских иероглифов выходят за пределы БМП. Таким образом, строки Unicode могут быть представлены в UTF-8 и других истинных кодировках Unicode, которые не могут быть сохранены в поле SQL Server nchar или nvarchar, а строки, написанные в египетских иероглифах, будут среди них. К счастью, ваши пользователи, вероятно, не пишут в этом script, но это что-то нужно иметь в виду!

Еще одна запутанная, но интересная точка, которую выделяли другие плакаты, состоит в том, что поля char и varchar могут использовать два байта на символ для определенных символов, если это требует кодовая страница коллинга. (Мартин Смит дает отличный пример, в котором он показывает, как Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS демонстрирует это поведение. Проверьте это.)

ОБНОВЛЕНИЕ: В SQL Server 2012 есть, наконец, кодовые страницы для UTF-16, например Latin1_General_100_CI_AS_SC, которые действительно могут весь диапазон Unicode.

qaru.site