Что выбрать, text или varchar (MAX)?

Более 10 лет специалисты задаются вопросами и ведут споры на специализированных форумах и в офисных кабинетах — «какой тип данных для текстовых полей в MS SQL лучше выбрать?», text или varchar(MAX).

Давайте разбираться.

text и varchar(MAX) – при максимальной ёмкости хранилища в 2 Гигабайта не-Юникодных символов, эти типы позволяют хранить символьные данные большой переменной длины. Использовать данные типы нужно осторожно, т.к. в части запросов есть ряд существенных ограничений, связанных в первую очередь, со скоростью обработки и извлечением данных. Опытный билдер БД 100 раз подумает, перед тем как присвоить текстовый тип данных столбцу.

Что же выбрать?

Этим вопросом задаются более трех лет. На официальном сайте Microsoft опубликовано сообщение от 2017 года о прекращении поддержки типов данных ntext, text и image. Площадки обсуждения этого вопроса можно назвать как классическими (форумы, бизнес-чаты специалистов) так и абсолютно новыми для подобных вопросов (институты, практические он-лайн курсы, переписка с технической поддержкой SSMS). Интересно так же то, что необходимость перехода на новый формат интерпретируется специалистами и как острая необходимость и срочный переход, так и абсолютно необязательная процедура.

Нам кажется, что золотой серединой данного вопроса будет как и во многих процессах администрирования БД — анализ инфраструктуры, а именно оценка объёма данных, изучения вероятностной производительности при возможном переходе и, конечно же, возможные неудобства, как на этапе перехода (например расходы на ребилдинг), так и на этапе дальнейшего обслуживания. Удивительно, но такой простой, казалось бы, вопрос, породил спрос на специалистов, которые были готовы прийти «на помощь» компаниям и провести быстрое и безопасное «обновление» БД нуждающихся компаний на новый тип данных за необъяснимо круглую сумму средств. Надо понимать, что это не очередное обновление телефона с новыми смайликами, а базовое изменение системы хранения важных данных и подход должен быть соответствующим.

В ряд или вне ряда?

Данные столбца типа varchar(MAX) хранятся в строке, если они не превышают или являются равными по своему объёму значений 8000 байт. Если значение столбца varchar(MAX) превышает значение в 8000 байт, то особенность хранения информации в таких столбцах будет схожей с типом данных text, т.е. данные будут хранятся out-of-row в отдельной странице данных LOB и строка на странице данных таблицы будет иметь только 16-байтовый указатель на страницу данных LOB, в разделе которого присутствуют фактические, исходные значения. Как итог, In-Row varchar(MAX) лучше подходит для поиска и извлечения.

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

Одно из самых главных нововведений – это, конечно же, более гибкая работа с данными по извлечению, с использованием операторов ‘=’и Group By. Но не надо забывать, что данные в этих полях не целочисленные, а значит при большом объёме данных, необходимо будет производить анализ ценовых потерь запроса, т.к. производительность неизбежно будет страдать, из-за более ёмкого алфавита возможных символов в отличие от других типов данных.

Не +10 к скорости

Также нужно видеть тонкую грань, между типами text и varchar(MAX), многие, на русскоязычных форумах свято верят, что при любых запросах новый тип работает безусловно лучше и производительнее, но это не так. Достаточно понять архитектуру хранения и объёма данных вашей СУБД, чтобы спрогнозировать рост производительности или практически полное её отсутствие. Другими словами, при некоторых ситуациях время обработки будет практически одинаковым, например, если большинство значений, хранящихся в столбце varchar(MAX), являются большими и хранятся в столбце out-of-row. Обратной стороной медали можно считать ситуацию, когда запрос Select включает столбец LOB — и для извлечения данных требуется меньшее количество страниц для чтения по сравнению со столбцами типа text.

Вывод

Сегодня вопрос уже не заключается в муках выбора. Varchar(MAX) производительнее и выбран как современный, полноценный аналог типу text. Вопрос больше лежит в плоскости текущего обслуживания СУБД, а именно — как поменять типы данных так, чтобы ничего не потерять и не форматировать структуру базы под грядущие изменения. Не будет лишним протестировать переход части данных на отдельной таблице, для исключения ошибок конверсии.