SQL-Ex blog

Но сначала давайте рассмотрим две вещи, которые не совсем параметры!

1. Локальные переменные


2. Небезопасный динамический SQL

Более подробная информация в посте, но локальные переменные - это любые переменные, которые вы объявляете внутри блока кода, например:

DECLARE

    @a_local_variable некоторый_тип_данных;

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

@sql += N'AND H.user_name = ''' + @injectable + ''';';

Обратите внимание на последовательность единичных кавычек и операторы + (хотя то же самое имело бы место, если вы будете использовать функцию CONCAT), и что только лишь квадратные скобки не спасут вас.

Теперь давайте поговорим о самой параметризации.

Та же концепция применима к запросам ORM, но я не могу написать этот тип кода, поэтому пройдите по ссылке, чтобы больше узнать об этом.

Хранимые процедуры

Наиболее очевидный способ - это использовать хранимую процедуру.

CREATE OR ALTER PROCEDURE

    dbo.Obvious

(

    @ParameterOne int

)

AS

BEGIN

SET NOCOUNT, XACT_ABORT ON;

    SELECT

        records = COUNT_BIG(*)

    FROM dbo.Users AS u

    WHERE u.Id = @ParameterOne;

END;

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

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

Разработчики без большого опыта в SQL могут наделать в них массу ошибок.

Встроенные (inline) табличные функции

Существуют другие виды функций в SQL Server, но эти, безусловно, наименее полны сюрпризов производительности.

CREATE OR ALTER FUNCTION

    dbo.TheOnlyGoodKindOfFunction

(

    @ParameterOne int

)

RETURNS table

AS

RETURN

    SELECT

        records = COUNT_BIG(*)

    FROM dbo.Users AS u

    WHERE u.Id = @ParameterOne;

GO

Как скалярная, так и многооператорная табличная функции могут вызвать множество проблем, и при возможности их обычно следует избегать.

Встроенная табличная функция плоха только тогда, когда плох встроенный в нее запрос.

Динамический SQL

Динамический SQL получает плохую репутацию от людей, которые:

1. Не имеют представления, о чем они говорят.


2. Делают с ним все неправильно.

DECLARE

    @sql nvarchar(MAX) = N'',

    @ParameterOne int;

SELECT

    @sql += N'

    SELECT

        records = COUNT_BIG(*)

    FROM dbo.Users AS u

    WHERE u.Id = @ParameterOne;    

    ';

EXEC sys.sp_executesql

    @sql,

  N'@ParameterOne int',

    @ParameterOne;

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

Обратите внимание, что хотя мы объявляем @ParameterOne как локальную переменную, мы передаем ее в блок динамического SQL как параметр, который делает ее поведение корректным. Это так же справедливо, если бы мы передал его в другую хранимую процедуру.

Динамический SQL плох только тогда, когда плох запрос, помещенный туда.

Принудительная параметризация

Принудительная параметризация - замечательная настройка. К сожалению, все думают, что они хотят включить оптимизацию для рабочих нагрузок adhoc, что является довольно бесполезным делом.

Вы можете включить ее, например, так:

ALTER DATABASE [YourDatabase] SET PARAMETERIZATION FORCED;

Принудительная параметризация возьмет запросы с литеральными значениями и заменит их параметрами для того, чтобы сделать план повторно используемым. Это имеет некоторые ограничения, но обычно это быстрое решение для постоянной компиляции и плановой очистки кэша от непараметризованных запросов.

Решение включать ли эту функцию может быть сложным, если вы не уверены, какую проблему собираетесь решить.

Другое

SQL Server может попытаться применить простую параметризацию в ряде случаев, но это не является гарантированным и надежным способом сделать большинство ваших рабочих запросов параметризованными.

В общем, основная тяжесть работы ложится на вас, чтобы правильно выполнить параметризацию. Параметры — прекрасные вещи, которые можно даже выводить и передавать между блоками кода. В настоящее время представления не принимают параметры как часть своих определений, поэтому они вам здесь не помогут.