SARG предикаты в MS SQL Server

Довольно часто у начинающих разработчиков возникает вопрос почему оптимизатор запросов не использует поиск по нужному им индексу.
Работа оптимизатора тема очень не простая и под капотом у него куча нюансов. Но есть два момента на которые стоит обратить внимания в первую очередь, это селективность и какие предикаты поиска используются.
Про селективность, а конкретней про статистику в следующий раз, а сейчас немного про SARG предикаты.
SARG (Seekable/Searchble Arguments) предикаты позволяют сделать поиск по индексу.
Любые манипуляции над столбцами которые участвуют в поиске не позволяют использовать алгоритмы поиска по индексу.

Например нужно найти данные у которых значения в столбце меньше на единицу чем аргумент:

where col-1=@var

При таком поиске придется рассчитывать значение  col-1 для каждой строчки и поиск по индексу использован не будет.
Запрос нужно переписать так, что бы значения для предиката высчитывалось один раз и дальше использовалось для поиска.

where col=@var+1

Можно использовать почти все операторы < > <= >= = IN LIKE BETWEEN

LIKE будет работать только с точным значением первого символа
работает:

where co like 'Q%'

не работает:

where co like '%Q%'

Другие примеры:

--не работает
where ABS(col) = 1
--работает
where col in (-1, 1)


--не работает
where DATEPART(YEAR, col) =2017
--работает
where col >='20170101' and col <'20180101'


--не работает
where DATEADD(DAY, 7, col) > GETDATE()
--работает
where col > DATEADD(DAY, -7, GETDATE())


--не работает
where ISNULL(col, 1) = 1
--работает
where ((col = 1) OR (col IS NULL))


--не работает
where SUBSTRING(col, 4) = 'ABCD'
--работает
where col Like 'ABCD%'

--не работает
where SUBSTRING(col, 4) = 'ABCD'
--работает
where col Like 'ABCD%'

Это же относится и к приведению типов, особенно часто проблемы возникают с varchar.
Поиск по аргументу с типом nvarchar или N'привет ' работать не будет.
Если у вас в БД тип varchar и вы используете какую либо ORM, то нужно этот момент предусмотреть.

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