Строковые константы в MS SQL

Строковые константы в MS SQL кажутся очень простыми в использовании. Но эта простота не всегда очевидна и порой приводит к тяжело выявляемым ошибкам в коде.

По этой причине данная статья может оказаться полезной не только новичкам, но и тем, кто уже использует T-SQL в своей работе.

Документация явно описывает два типа констант: обычные строковые и юникодные. Но на самом деле ситуация несколько сложнее, что и будет рассмотрено ниже.

Обычные строковые константы

Такие константы имеют тип VARCHAR(n), где n - количество символов в константе. При этом n может быть от 1 до 8000. В памяти такая константа обычно занимает 8+n байт. За исключением случая пустой строки, тип которой VARCHAR(1), но занимает она только 8 байт, если не включен режим совместимости с MS SQL 6.x, в котором пустая строка хранится как одиночный пробел.

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

SELECT SQL_VARIANT_PROPERTY('', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY('', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY('', 'MaxLength') AS MaxLength
UNION ALL
SELECT SQL_VARIANT_PROPERTY('12345', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY('12345', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY('12345', 'MaxLength') AS MaxLength
UNION ALL
SELECT SQL_VARIANT_PROPERTY('𐐷𐐷𐐷𐐷𐐷', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY('𐐷𐐷𐐷𐐷𐐷', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY('𐐷𐐷𐐷𐐷𐐷', 'MaxLength') AS MaxLength;


BaseType	TotalBytes	MaxLength
varchar 	8       	1
varchar 	13      	5
varchar 	18      	10

Если длина константы превышает 8000 байт, то её тип будет уже VARCHAR(MAX). Почему это важно, будет рассмотрено ниже.

В базе данных поля типа VARCHAR занимает n+2 байта, так как тип и максимальная длина хранятся уже в метаданных.

Юникодные строковые константы

Такие константы имеют тип NVARCHAR(n), где n - количество символов в константе. При этом n может быть от 1 до 4000. Каждый символ в такой константе может занимать от двух до четырёх байт. Для юникодного типа, перед открывающей кавычкой нужно указывать символ N. В памяти такая константа занимает 8+2*n+2*k, где k - количество четырехбайтных (surrogates) символов в константе.

SELECT SQL_VARIANT_PROPERTY(N'', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY(N'', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY(N'', 'MaxLength') AS MaxLength
UNION ALL
SELECT SQL_VARIANT_PROPERTY('12345', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY(N'12345', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY(N'12345', 'MaxLength') AS MaxLength
UNION ALL
SELECT SQL_VARIANT_PROPERTY('𐐷𐐷𐐷𐐷𐐷', 'BaseType') AS BaseType,
  SQL_VARIANT_PROPERTY(N'𐐷𐐷𐐷𐐷𐐷', 'TotalBytes') AS TotalBytes,
  SQL_VARIANT_PROPERTY(N'𐐷𐐷𐐷𐐷𐐷', 'MaxLength') AS MaxLength;

BaseType	TotalBytes	MaxLength
nvarchar	8       	2
nvarchar	18      	10
nvarchar	28      	20

Если длина константы превышает 8000 байт, то её тип будет уже NVARCHAR(MAX). Почему это важно, будет рассмотрено ниже.

В базе данных поля типа NVARCHAR занимает 2+2*n+2*k байта, так как тип и максимальная длина хранятся уже в метаданных.

Начиная с MS SQL Server 2019 в БД могут храниться юникодные строки в формате UTF-8, однако юникодные константы по прежнему кодируются UTF-16.

Константы длиной больше 8000 байт

Как уже выше было указано, если константа требует более 8000 байт для данных, то она будет преобразована в VARCHAR(MAX) или NVARCHAR(MAX), допускающие длину констант до 2 ГБ.

При этом при конкатенации строк действуют следующие правила:

1. Если в выражении все строки VARCHAR(n), то результат тоже будет VARCHAR(n). Причем, результат будет усечен до длины 8000 байт. Никакого предупреждения при этом не выводится!

2. Если в выражении есть строки NVARCHAR(n) и, опционально, VARCHAR(n), то результат будет NVARCHAR(n). Причем, результат будет усечен до длины 8000 байт. Никакого предупреждения при этом не выводится!

3. Если в выражении есть строки VARCHAR(MAX) и, опционально, VARCHAR(n), то результат будет VARCHAR(MAX).

4. Если в выражении есть строки NVARCHAR(MAX) и, опционально, VARCHAR(n) или VARCHAR(MAX), то результат тоже будет NVARCHAR(MAX).

DECLARE
  @str2    NVARCHAR(MAX) = '''11''',
  @str4000 NVARCHAR(MAX) = '''' + CONVERT(NVARCHAR(MAX),REPLICATE('1', 4000)) + '''',
  @str8000 NVARCHAR(MAX) = '''' + CONVERT(NVARCHAR(MAX),REPLICATE('1', 8000)) + '''',
  @str8001 NVARCHAR(MAX) = '''' + CONVERT(NVARCHAR(MAX),REPLICATE('1', 8000)) + '1''',
  @sql_str NVARCHAR(MAX) = 'SELECT LEN(';
SELECT @sql_str = @sql_str + @str2 + ' + ' + @str4000 + ') AS StringLen, 4002 AS Assumed
UNION ALL
              ' + @sql_str + @str2 + ' + ' + @str8000 + ') AS StringLen, 8002 AS Assumed
UNION ALL
              ' + @sql_str + 'N' + @str2 + ' + ' + @str8000 + ') AS StringLen, 8002 AS Assumed
UNION ALL
              ' + @sql_str + 'N' + @str2 + ' + ' + @str8001 + ') AS StringLen, 8003 AS Assumed
UNION ALL
              ' + @sql_str + @str2 + ' + N' + @str8001 + ') AS StringLen, 8003 AS Assumed;'
EXECUTE (@sql_str);

StringLen	Assumed
4002    	4002
8000	    8002
4000    	8002
8003    	8003
8003    	8003

В первом случае результат конкатенации не превысил 8000 байт.

Во втором случае превысил и был усечен до 8000 байт и символов.

В третьем случае превысил и был усечен до 8000 байт и 4000 символов.

В двух последних результат не усекался потому что константы, сформированные при помощи переменной @str8001, сразу формировались с типами VARCHAR(MAX) и NVARCHAR(MAX) соответственно.

Выводы

Как видим, если при конкатенации констант и переменных ни одна из них не имеет тип VARCHAR(MAX) или NVARCHAR(MAX), то MS SQL может молча обрезать результат до 8000 байт.

Исходя из этого, в тех случаях, когда потенциально результат может превысить 8000 байт, следует явно выполнять преобразование хотя бы одного элемента выражения конкатенации в тип VARCHAR(MAX) или NVARCHAR(MAX) при помощи CAST или CONVERT.