SQL-Ex blog

Одной из моих любимых команд SQL являлась QUOTENAME. При программировании генерации кода обычно возникает необходимость заключать строковое значение в кавычки и экранировать любые символы, совпадающие с теми, которыми вы ограничиваете строку, их удвоением. (А если у вас пара таких символов в строке, вам их потребуется уже четыре.) Например, чтобы взять следующую строку в одинарные кавычки ('):

Mr. O'Malley

Чтобы иметь возможность использовать её в динамическом операторе или объявлении переменной, потребуется удвоить одинарную кавычку в строке:

'Mr. O''Malley'

Или, если вы Rob Volk (@sql_r на Twitter), и хотите создать раздражающую базу данных на вашем лучшем заклятом SQL Server, то, чтобы включить скобки в имя базы типа:

This [database] Is Awesome

вам придется сделать так:

CREATE DATABASE [This [database]] Is Awesome];

Удваивается закрывающая скобка, но не открывающая. Для экранирования можно использовать QUOTENAME. Параметрами этой функции являются строка и разделитель. По умолчанию удваивается скобка как у большинства имён SQL Server, хотя вы можете использовать любой символ для удвоения. Так для нашей строки:

DECLARE @Value nvarchar(15) = 'Mr. O''Malley';

SELECT @Value, QUOTENAME(@value) AS objectName, 

       QUOTENAME(@value,'''') AS string;

Этот код вернет

	objectName	string
Mr. O'Malley	[Mr. O'Malley]	'Mr. O''Malley'

Кажется, работает отлично, поэтому вы чувствуете, что, если вам потребуется нагенерировать некоторый код, вы сможете поступить так:

DECLARE @Value nvarchar(200) = 'Mr. O''Malley';

SELECT  CONCAT('SELECT ',QUOTENAME(@Value,''''));

Результат

SELECT 'Mr. O''Malley'

Выполните этот запрос в Management Studio. Вы увидите на вкладке результата строку, начинающуюся с приведенного выше текста. Проблема состоит в том, что QUOTENAME предназначена для закавычивания значений имен SQL Server, а поскольку в SQL Server имена не могут быть длиннее, чем 128 символов,... на входе должно быть не более 128 символов (ниже покажем, что может быть и больше). Итак:

SELECT QUOTENAME(REPLICATE('a',200));

Когда вход превышает 128 символов, возвращается NULL без каких либо предупреждений. Это, мягко говоря, не то, что вы хотели. В моем случае я разработал генератор скрипта расширенных свойств, который принимает значение типа sql_variant и преобразует его к nvarchar(max). Я не тестировал входы, превышающие 128 символов, но коллега любезно предоставил мне строку размером порядка 8000 символов. К счастью, это не был рабочий сервер, где бы безостановочно звонил телефон поддержки.

Итак, я могу предложить использование QUOTENAME в рабочем коде только для квотирования реальных имен операторов SQL, и использовать более неуклюжий метод для других целей:

DECLARE @Value nvarchar(15) = 'Mr. O''Malley'

SELECT @Value, 

       '''' + REPLACE(@value,'''','''''') + '''' AS AwkwardButRightWay, 

       QUOTENAME(@Value,'''') AS QUOTENAME;

Этот код демонстирует один и тот же результат для обоих вариантов.

Следует заметить, что это (как и любая простая повторно исполняемая скалярная функция) просится для использования в качестве пользовательской функции. В версиях, предшествующих 2019, всегда однозначно советовали избегать их, т.к. они, мягко говоря, ограничивали производительность. В одних случаях падение производительности было незначительным, в других - существенными провалами. В SQL Server 2019 Microsoft изменила механизм выполнения некоторых скалярных функций, который будет "встраивать" код функции в план запроса, использующего эту функцию. В результате производительность, полученная для перекодируемого оператора и при использовании функции соизмеримы (даже для довольно сложных функций!).

Построим, например, в базе данных WideWorldImporters следующую функцию:

USE WideWorldImporters

GO

CREATE SCHEMA Utility

GO

--всегда делаю одинарные кавычки для моей функции

CREATE OR ALTER FUNCTION Utility.EscapeString ( @string nvarchar(max) ) 

RETURNS nvarchar(max)

WITH RETURNS NULL ON NULL INPUT

AS

  BEGIN

     --если раскомментировать эту строку, функция не будет встраиваемой

	--DECLARE @datevalue date = GETDATE();    

     RETURN '''' + REPLACE(@string,'''','''''') + '''';

  END;

Выполнить функцию можно так:

SELECT Utility.EscapeString(CityName)

FROM   Application.Cities;

И вы можете проверить, встраивается ли она, таким образом:

SELECT is_inlineable

FROM   sys.sql_modules

WHERE OBJECT_ID = OBJECT_ID('Utility.EscapeString');

Этот запрос для нашей новой функции возвращает 1.

Встраиваемая или нет, вы не увидите каких либо изменений в этом простом примере - я просто хотел указать на это как на более ценную возможность в будущем. Подробнее о встраиваемых функциях смотрите в публикации Brent Ozar’а.

Наконец, давайте взглянем на немного туповатый трюк, который в целом полезен для определения того, наколько длинной может быть заковыченная строка. Меня интересует, какого максимального размера может быть вывод функции QUOTENAME. Если вы выполните следующий оператор:

SELECT QUOTENAME(']')

он вернет []]], а

SELECT QUOTENAME(']]')

вернет []]]]], что на 2 символа больше, чем исходная строка. Если у нас 128 символов ], мы должны получить SELECT 127*2+4 или 258 символов, которые должен вывести следующий оператор...

SELECT LEN(QUOTENAME(REPLICATE(']',128)))

Мы могли бы это просто прочитать в документации о QUOTENAME, где говорится о результате nvarchar(258)! Следовательно, вот какого размера должна быть переменная/столбец, чтобы ее можно было обработать с помощью функции EscapeString:

((максимальная длина вашего входа) - 1) * 2 + 4

Здесь максимальная длина - это не 2 миллиарда как у varchar(max), а максимальная длина, которую может иметь ваш источник данных. Конечно, когда вы используете это значение со всеми экранированными строками, оно будет выглядеть больше, чем результирующее значение, но просто потому, что нам требуется пространство для 258 символов на имя, что не означает, что нам требуется именно 258 символов. Это просто еще одно препятствие, с которым вы столкнетесь, строя пуленепробиваемый генератор кода.