Есть ли преимущество varchar(500) перед varchar(8000)?
Я читал об этом на форумах MSDN и здесь, и мне все еще не ясно. Я думаю, что это правильно: Varchar(max) будет храниться как текстовый тип данных, поэтому у него есть недостатки. Допустим, ваше поле будет надежно содержать до 8000 символов. Как поле BusinessName в моей таблице базы данных. В действительности, название компании, вероятно, всегда будет меньше (вытаскивая число из моей шляпы) 500 символов. Кажется, что множество полей varchar, с которыми я сталкиваюсь, подпадают под число символов 8k.
Так я должен сделать это поле varchar(500) вместо varchar(8000)? Из того, что я понимаю в SQL, нет никакой разницы между этими двумя. Итак, чтобы облегчить жизнь, я бы хотел определить все свои поля varchar как varchar(8000). Есть ли у этого недостатки?
Связанный: Размер столбцов varchar (я не чувствовал, что этот ответил на мой вопрос).
5 ответов
С точки зрения обработки, не имеет значения использовать varchar(8000) против varchar(500). Это больше похоже на "хорошую практику", чтобы определить максимальную длину, которую должно содержать поле, и сделать ваш varchar такой длины. Это то, что может быть использовано для проверки данных. Например, сокращение штата должно состоять из 2 символов или почтового индекса, состоящего из 5 или 9 символов. Раньше это было более важным отличием, когда ваши данные взаимодействовали с другими системами или пользовательскими интерфейсами, где длина поля была критической (например, набор данных плоских файлов мэйнфреймов), но в настоящее время я думаю, что это больше привычка, чем что-либо еще.
Одним из примеров, где это может иметь значение, является то, что это может предотвратить оптимизацию производительности, которая позволяет избежать добавления информации о версиях строк в таблицы с триггерами после.
Фактический размер хранимых данных не имеет значения - важен потенциальный размер.
Аналогичным образом, при использовании таблиц, оптимизированных для памяти, с 2016 года стало возможным использовать столбцы больших объектов или комбинации ширины столбцов, которые потенциально могут превышать предел ввода, но со штрафом.
(Макс) столбцы всегда хранятся вне строки. Для других столбцов, если размер строки данных в определении таблицы может превышать 8 060 байт, SQL Server выталкивает самые большие столбцы переменной длины вне строки. Опять же, это не зависит от количества данных, которые вы там храните.
Это может оказать большое негативное влияние на потребление памяти и производительность
Другой случай, когда чрезмерное объявление ширины столбцов может иметь большое значение, - это если таблица будет когда-либо обрабатываться с использованием служб SSIS. Память, выделенная для столбцов переменной длины (не BLOB), фиксирована для каждой строки в дереве выполнения и соответствует объявленной максимальной длине столбцов, что может привести к неэффективному использованию буферов памяти (пример). Несмотря на то, что разработчик пакета служб SSIS может объявить столбец меньшего размера, чем источник, этот анализ лучше всего выполнить заранее и применять его там.
В самом механизме SQL Server аналогичный случай заключается в том, что при расчете предоставления памяти, выделяемой для SORT Операции SQL Server предполагают, что varchar(x) столбцы будут в среднем потреблять x/2 байт.
Если большинство ваших varchar столбцы полнее, чем это может привести к sort операции разлива в tempdb,
В вашем случае, если ваш varchar столбцы объявлены как 8000 байты, но на самом деле их содержимое намного меньше, чем для вашего запроса будет выделена память, которая ему не требуется, что, очевидно, неэффективно и может привести к ожиданию предоставления памяти.
Это описано в части 2 веб-трансляции по SQL Workshops 1, которую можно загрузить здесь или посмотреть ниже.
use tempdb;
CREATE TABLE T(
id INT IDENTITY(1,1) PRIMARY KEY,
number int,
name8000 VARCHAR(8000),
name500 VARCHAR(500))
INSERT INTO T
(number,name8000,name500)
SELECT number, name, name /*<--Same contents in both cols*/
FROM master..spt_values
SELECT id,name500
FROM T
ORDER BY number
SELECT id,name8000
FROM T
ORDER BY number
У больших столбцов есть некоторые недостатки, которые немного менее очевидны и могут заметить вас чуть позже:
- Все столбцы, которые вы используете в INDEX - не должны превышать 900 байт.
- Все столбцы в предложении ORDER BY не должны превышать 8060 байтов. Это немного сложно понять, так как это относится только к некоторым столбцам. См., Что предел размера строки SQL 2008 R2 превышен для деталей)
- Если общий размер строки превышает 8060 байт, вы получите "разлив страницы" для этой строки. Это может повлиять на производительность (страница является единицей выделения в SQLServer и имеет фиксированный размер 8000 байт + некоторые накладные расходы. Превышение этого значения не будет серьезным, но это заметно, и вам следует избегать его, если вы можете легко)
- Многие другие внутренние структуры данных, буферы и, что не менее важно, ваши собственные переменные и табличные переменные должны отражать эти размеры. При чрезмерных размерах избыточное распределение памяти может повлиять на производительность
Как правило, старайтесь быть консервативным с шириной столбца. Если это становится проблемой, вы можете легко расширить ее в соответствии с потребностями. Если вы заметите проблемы с памятью позже, сжатие широкого столбца позже может стать невозможным без потери данных, и вы не будете знать, с чего начать.
В своем примере названий компаний подумайте, где вы можете их отобразить. Есть ли место для 500 символов? Если нет, то нет смысла хранить их как таковые. http://en.wikipedia.org/wiki/List_of_companies_of_the_United_States перечисляет названия некоторых компаний, и максимальная длина составляет около 50 символов. Так что я бы использовал 100 для столбца макс. Может быть, больше похоже на 80.
Помимо лучших практик (ответ BBlake)
- Вы получаете предупреждения о максимальном размере строки (8060) байтов и ширине индекса (900 байтов) с DDL
- DML умрет, если вы превысите эти пределы
- ANSI PADDING ON - это значение по умолчанию, так что вы можете в конечном итоге хранить весь объем пробелов
В идеале вы хотели бы пойти меньше, чем это, до разумно измеренной длины (500 не имеет разумного размера) и убедиться, что проверка клиента перехватывает, когда данные будут слишком большими, и отправлять полезную ошибку.
Хотя varchar на самом деле не собирается резервировать пространство в базе данных для неиспользуемого пространства, я вспоминаю версии SQL Server, в которых количество строк в базе данных немного шире, чем некоторое количество байтов (не помню точное количество), и фактически выбрасывается какие бы данные не подходили. Определенное количество этих байтов было зарезервировано для вещей, встроенных в SQL Server.