Надлежащий способ обработать 'дополнительный', где пункт просачивается SQL?

Если мы преобразуем SQL в строку, а затем вызовем для нее sp_ExecuteSQL, чтение будет почти нулевым ...

1. Поскольку ваш запрос больше не оценивает операцию ИЛИ, что, как вы можете см. kills sargability
2. План запроса кэшируется при использовании sp_executesql; SQL Server не должен выполнять жесткий синтаксический анализ ...

Превосходный ресурс: Проклятие и благословение динамического SQL

Пока вы используете параметризованные запросы, вы должны избегать SQL Инъекционные атаки .

Это еще одна разновидность техники необязательных параметров:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = COALESCE(@MyOptionalParam, t1.MyField)

Я почти уверен, что у нее будет такая же проблема с производительностью. Если производительность - №1, то вы, вероятно, застрянете с логикой разветвления и рядом с дублирующимися запросами или построением строк, что не менее болезненно в TSQL.

You're using "OR" clause (implicitly and explicitly) on the first two SQL statements. Last one is an "AND" criteria. "OR" is always more expensive than "AND" criteria. No you're not crazy, should be expected.

Измените использование синтаксиса «или» на подход с двумя запросами, вы увидите 2 разных плана, которые следует сохранить ваш логический счетчик чтения как можно меньше:

IF @MyOptionalParam is null
BEGIN

  SELECT *
  FROM dbo.MyTableName t1

END
ELSE
BEGIN

  SELECT *
  FROM dbo.MyTableName t1
  WHERE t1.MyField = @MyOptionalParam

END

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

РЕДАКТИРОВАТЬ: Добавление ссылка на аналогичный вопрос / ответ с контекстом о том, почему подход union / if ... else работает лучше, чем логика OR (FYI, Ремус, ответчик в этой ссылке, использовался для работы с командой разработчиков SQL Server, занимающейся разработкой сервис-брокера и другие технологии)

При переходе от использования синтаксиса «или» к подходу объединения вы увидите 2 поиска, которые должны поддерживать как можно меньшее количество логических считываний:

SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND @MyOptionalParam IS NULL 
union all
SELECT * FROM dbo.MyTableName t1
WHERE t1.ThisField = 'test'
AND t1.MyField = @MyOptionalParam

Если вы хотите исключить дублирование результатов используйте «union» вместо «union all».

EDIT: демонстрация, показывающая, что оптимизатор достаточно умен, чтобы исключить сканирование с нулевым значением переменной в UNION:

if object_id('tempdb..#data') > 0
    drop table #data
go

-- Put in some data
select  top 1000000
        cast(a.name as varchar(100)) as thisField, cast(newid() as varchar(50)) as myField
into    #data
from    sys.columns a
cross join sys.columns b
cross join sys.columns c;
go

-- Shwo count
select count(*) from #data;
go

-- Index on thisField
create clustered index ixc__blah__temp on #data (thisField);
go

set statistics io on;
go

-- Query with a null parameter value
declare @MyOptionalParam varchar(50);
select  *
from    #data d 
where   d.thisField = 'test'
and     @MyOptionalParam is null;
go

-- Union query
declare @MyOptionalParam varchar(50);
select  *
from    #data d 
where   d.thisField = 'test'
and     @MyOptionalParam is null
union all
select  *
from    #data d 
where   d.thisField = 'test'
and     d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E';
go

-- Union query with value
declare @MyOptionalParam varchar(50);
select @MyOptionalParam = '5D25E9F8-EA23-47EE-A954-9D290908EE3E'
select  *
from    #data d 
where   d.thisField = 'test'
and     @MyOptionalParam is null
union all
select  *
from    #data d 
where   d.thisField = 'test'
and     d.myField = '5D25E9F8-EA23-47EE-A954-9D290908EE3E';
go

if object_id('tempdb..#data') > 0
    drop table #data
go