Сравнение типов данных VARCHAR(max) и VARCHAR(n) в SQL Server

[za2li]

Еще пара моментов которые надо учитывать.

1. При использовании типа varchar(n) / nvarchar(n), sql server предполагает что средний размет строки будет n/2, и исходя из этого выделяет память для выполнения запросов. Поэтому при выборе значения n идеальным будет удвоенное значение средней длины строк хранящихся в этом столбце.
   Для varcha(max) / nvarchar(max) он предполагает что средняя длина равна 4000 символам, т.е. если вы скажем будете хранить строки размером в 100 символов в таком столбце, то памяти будет выделяться в 40 раз больше чем нужно для запроса, что в свою очередь плохо скажется на производительности.

2. Скажем у вас в таблице есть столбцы с данными, и один или несколько столбцов varcha(max) / nvarchar(max). По умолчанию данные varcha(max) / nvarchar(max) хранятся IN_ROW если объем их меньше 8000 байт.
   Иногда имеет смысл поменять это поведение, и сразу хранить все эти данные как LOB_DATA. Это улучшит производительность запросов которые не читают LOB столбцы из таблицы.
   Такое поведение можно включить через опцию large value types out of row

   exec sys.sp_tableoption
   	  @TableNamePattern = 'dbo.TableName'
     , @OptionName = 'large value types out of row'
     , @OptionValue = '1'

[yurakmv26]

Интересная опция во втором пункте, а начиная с какого размера строки он будет в ЛОБ хранить данные? Это как-то настраивается?

[za2li]

Данные сразу будут записаны в LOB хранилище. И это правильно, т.к. в большинстве случаев если мы читаем var...(max) поле, то мы читаем его полностью, значит нет смысла часть его хранить в таблице а часть в LOB. И второе, достаточно часто в запросах читаются все поля кроме var...(max), т.е. больше поля читаются как правило точеными запросами, типа получения объекта по его идентификатору. А значит большинство запросов выигрывают в случае включения этой опции, т.к. читая страницы таблицы мы не читаем багаж в виже части LOB полей.

Из доков:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-tableoption-transact-sql?view=sql-server-ver15#arguments

large value types out of row

1 = varchar(max), nvarchar(max), varbinary(max), xml and large user-defined type (UDT) columns in the table are stored out of row, with a 16-byte pointer to the root.

0 = varchar(max), nvarchar(max), varbinary(max), xml and large UDT values are stored directly in the data row, up to a limit of 8000 bytes and as long as the value can fit in the record. If the value does not fit in the record, a pointer is stored in-row and the rest is stored out of row in the LOB storage space. 0 is the default value.

[FireShock]

После этой статьи многим, наверное, было бы интересно, как поменять все nvarchar(max) на nvarchar(n) с освобождением занятого места в базе.