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

[AI]

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

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

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

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


Такие константы имеют тип VARCHAR👎, где 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 может быть от 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👎, то результат тоже будет VARCHAR👎. Причем, результат будет усечен до длины 8000 байт. Никакого предупреждения при этом не выводится!
   
   
2. 
   Если в выражении есть строки NVARCHAR👎 и, опционально, VARCHAR👎, то результат будет NVARCHAR👎. Причем, результат будет усечен до длины 8000 байт. Никакого предупреждения при этом не выводится!
   
   
3. 
   Если в выражении есть строки VARCHAR(MAX) и, опционально, VARCHAR👎, то результат будет VARCHAR(MAX).
   
   
4. 
   Если в выражении есть строки NVARCHAR(MAX) и, опционально, VARCHAR👎 или VARCHAR(MAX), то результат тоже будет VARCHAR(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 символов.

В двух последних результат не усекался.

Выводы


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

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