Рекомендации по ведению SQL-кода

Большинство из этого верно, но как всегда на почти каждый пункт существуют контрпримеры. Иначе бы нам, DBA, не платили бы)

4. явно нуждается в пояснении

5. Странная рекомендация. Ну понятно (надеюсь всем), что по индексированным полям обычно (кстати не в 100% но если не брать в расчёт затраты времени на индексирование то близко к 100%) фильтр выполняется быстрее. Вот только:

а. Всё не проиндексируешь - конечно можно но "цена" будет несоизмеримо более высокой, чем профит, а уж как запись будет проседать!

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

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

6. пункт нуждается в пояснениях насёт ветвления кода

7. пункт - плохо иллюстрирующий пример - тут бы что-то более конкретное и пактичное (менее абстрактное) и полностью определённое

9. Почти та же пустая рекомендация, что и 5. пункт. Но тут явно нужны пояснения - чем обусловлен такой порядок. Если говорить про MS SQL Server то, насколько я знаю, он уже давно научился сам правильно определять прядок фильтрации, не звисимо от порядка следования условий в SQL запросе. Вот другие СУБД да - не все так умеют. Иля я не правильно понял о каком порядке идёт речь?

11. пункт явно нуждается в пояснениях. Вообще надо не забывать - что формирование временных таблиц операция далеко не бесплатная - это важно и для малых и для больших выборок. И вообще - это оправдано только если имеется очень эффкективная фильтрация промежуточного результата для этих выборок (скажем в исходной таблице были миллиарды строк, в выбрали миллионы или хотя бы сотни) - в остальных случаях затраты формирования временной таблицы могут быть явно выше использования кеша уже прочитанных страниц.

12. хотелось бы пояснений для данной рекомендации - почему так?

22. Примерчики бы

30. Спорная рекомендация. Union достаточно частый оператор - и хорошая альтернатива менее прризводительным соединением. Но обычно да - используется "Union all" - Вы против уникального юниона или против всех?

31. Тоже очень спорная рекомендация. Зачастую проверки делаются в конце транзакции - и они влияют на её успех. Но, конечно, всё что можно проверить до транзакции надо проверить до. Но опять же - зачастую для целостности данных нужно соблюсти повторяемость чтения - чтобы, условно, к концу транзакции то, что было прочитано в начале соответствовало фиксируемой транзакции в её конце. И вот тут большинство (если не все) транзакционные СУБД сильно пасуют по своему функционалу!

34. пункт нуждается в пояснениях

45. пункт нуждается в пояснениях

49. Сложный и важный пункт. Явно не хватает примеров и альтернативных решений

В целом со всем согласен, но есть пара моментов

DROP TABLE IF EXISTS перед её созданием

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

CREATE PROC dbo.level_three
AS
BEGIN
    SELECT 'level_three' AS [level_three], OBJECT_ID('tempdb..#aaa', 'U') obj_id, * FROM #aaa
END;
GO

CREATE PROC dbo.level_two
AS
BEGIN
    SELECT 'level_two' AS [level_two], OBJECT_ID('tempdb..#aaa', 'U') obj_id

    DROP TABLE IF EXISTS #aaa;

    CREATE TABLE #aaa (a VARCHAR(10), b FLOAT);
    INSERT #aaa (a, b) VALUES ('22', 3.14);

    SELECT 'level_two' AS [level_two], OBJECT_ID('tempdb..#aaa', 'U') obj_id

    EXEC dbo.level_three;
END;
GO

CREATE PROC dbo.level_one
AS
BEGIN
    CREATE TABLE #aaa (a INT, b VARCHAR(10));

    INSERT #aaa (a, b) VALUES (1, 'b');

    SELECT 'level_one' AS [level_one], OBJECT_ID('tempdb..#aaa', 'U') obj_id

    EXEC dbo.level_two;

    SELECT 'level_one' AS [level_one], * FROM #aaa;
END;
GO

EXEC dbo.level_one;
GO

DROP PROC IF EXISTS dbo.level_one;
GO

DROP PROC IF EXISTS dbo.level_two;
GO

DROP PROC IF EXISTS dbo.level_three;
GO

А ещё лучше <запрос> выполнить до COALESCE

Предложил бы сразу рекомендацию и переписать, не предлагать то, что приносит в код новые потенциальные проблемы. Так-то и NULL может быть нужен, и не трогать переменную, если ничего не нашлось - по ситуации; рекомендация довольно абстрактная. И в SELECT-SET, и в SET-SELECT много чего нужно иметь ввиду и понимать, что ты делаешь.

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

13 При проектировании стараться делать так, чтобы первичные ключи и
кластерные индексы (да и некластерные тоже) наполнялись значениями не в
одном направлении (т е чтобы значения монотонно не возрастали и не
убывали). Случайное значение — плохо для перфоманса (хотя в случае
обновления 100% этого результата не достичь, но обычно PK не обновляют).
Также плохо монотонное возрастание/убывание значений ключей и индексов.

Несколько раз пытался понять, и не понял.

Auto increment PK ID - плохо?

GUID - тоже плохо?

14. Не индексировать немаленькие поля (например, строки, длина которых превышает 8 символов). В таком случае лучше определить вычисляемое сохраняемое поле, которое будет рассчитывать хэш этого поля и по нему создать индекс. 

Индекс используется не только для уникальности, но и для сортировки значений.
Совет "индекс по хешу" нужно принимать с оговоркой использования.

п. 45 - в чем проблема с SELECT INTO ? почему не использовать?

п. 50 - кем запрещено и почему?

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

6. Стараться в условиях не использовать оператор OR, а заменить его на IN или разбить на разные команды с помощью ветвления кода.
Это вы никогда не попадали на эти ошибки видно в операторе IN

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

2. Я бы не был столь категоричен. Порой нужны запросы между БД, и MERGE INTO. Да и параллелизм при вставке может перекрыть все выгоды от in memory OLTP. Бывают нужны geography и geometry типы. В общем, пока имеется масса ограничений для in memory OLTP, я бы рекомендовал пользоваться им с осторожностью.

Некоторые рекомендации, описанные в статье я использую, например не применяю курсоры, то есть применяю, но в виде агрегата min to min. Так как у меня не web приложения, то в основном в моих разработках использую хранимые процедуры, в которых делаю все обработки бизнес логики. Не использую временные таблицы, триггеры dml. Для хранения версий хранимых процедур и истории всего кода dml использую триггеры БД и таблицу а отдельной БД для хранения всей истории изменений северного кода. А статья полезная. Мои системы трудно назвать высоконагруженными, но в любом случае нужно по возможности придерживаться рекомендаций, связанных с быстродействием. Кстати, про быстроднйствие- пробовал использовать CTE в запросе вместо подзапросов, оказалось что с СТЕ запрос медленнее в 2 раза. То есть CTE тоже надо использовать с осторожностью, исходя из необходимости и требований к быстродействию.

Ну давайте по существу:

1. Вы не указали о каком типе функции идёт речь, будем считать, что это TVIF
В общем и целом это называется проблемой невозможности выполнить push-down предиката, (например при использовании в функции оконок), и в таком случае да - безусловно нужно вносить параметр явно и смотреть очень пристально на то, как оно компилируется.
НО в общем случае неприятности это может вам принести только в случае если вы используете функцию в качестве ограничивающего предиката с доп. условием в WHERE

2. Только ради этого не стоит заводит MOFG, к тому же: если вы создаетё HASH индекс, то зачем вам ещё и B-TREE? И почему HASH индекс не уникальный, если B-TREE уникальный?
В общем и целом могу порекомендовать следующее: если основной шаблон использования ID в типе - singleton lookup и вы знаете предполагаемый объем данных в этом конкретном TVP - создавайте HASH, иначе просто B-Tree и лишь в очень (ОЧЕНЬ) редких случаях нужны оба индекса.

3. Я перефразирую: нельзя использовать VIEW в качестве таблиц при написания запросов: оптимизатор гарантированно не будет счастлив, если вы сделаете соединение VIEW, которая ссылается на другое VIEW, которое содержит в определении APPLY на функцию и т.д. Т.е. суть кроется в следующем: VIEW это не таблица, при написании запросов к ней НУЖНО это учитывать.

4. Не понимаю при чём здесь "рекомендуется", речь идёт о SARGability, и в таком случае простой ссылки будет достаточно. п. 5 - то же самое

6. Нет ничего плохого в OR, просто надо знать меру (как и с IN btw), оценивайте приводит ли использование OR к излишним чтениям или проблемам с компиляцией запроса, если всё в порядке - оставляйте и даже не забивайте себе голову. Мы занимаемся разработкой, а не следованием мантрам в стиле "туда не ходи"

7. Это не совсем (и не всегда) верно, поскольку может ухудшать план исполнения, поскольку после вынесения в TVV этих значений план будет скомпилирован скорее всего как multiple seek of unknown value, это не столь критично при поиске уникальных значений, но может иметь сильнейший отридцательный эффект при поиске чего либо с cardinality > 1 строки.

9. За вас это делает оптимизатор (занимается оценкой), похоже на очередную мантру.

12. Если в ваших запросах DISTINCT SORT получается всегда дороже чем STREAM AGGREGATE, то значит оптимизатор ошибается в cardinality и вам нужно либо посмотреть что со статистикой, либо упростить запрос. (вообще к DISTINCT отношусь как жёлтому флагу, но кажется теперь начну так же относиться и к GROUP BY вместо DISTINCT).

13. Если изменить формулировку на "При проектировании таблицы в OLTP БД с сильной нагрузкой на вставку...", то это одно и допустимых решений, в остальном не очень, и да ссылки как всегда достаточно.

14. В качестве оптимизации или при проектировании seek-heavy таблицы - да, как исходное решение - нет. (у вас часто пользователи ищут строки по 50+ символов?)

17. Нет, оптимизатору в общем случае виднее, если есть проблемы - да, пробовать хинтовать, но НИКОГДА не делать этого "просто потому что принято", то же самое относится и к хинту NOLOCK.

18. Это не верно для NC модулей, поскольку может приводить к падению производительности.

23. Это просто разные функции и у каждой из них есть своя область применения:
COALESCE в T-SQL так вообще сахарок, она раскрывается:

SELECT COALESCE(a, b) FROM tbl;

SELECT
CASE
WHEN a IS NOT NULL THEN a
WHEN b IS NOT NULL THEN b
END
FROM
tbl;

соответственно вы не будете вычислять b, если у вас есть a (в случае если a и b подзапросы), НО вы вычислите a дважды (!!! это важно), один раз для выполнения assert, второй для непосредственно получения значения (таков уж T-SQL),

Проверьте то же самое для ISNULL() - все выражения внутри будут вычислены (но только и ВСЕГДА один раз)

25. И зачем вам это в таблице log'ов например?

26. Должно звучать как указывать длину при работе с любыми var* типами: [n]varchar(), varbinary(), дополню от себя: не каждая строка это "по-умолчанию" [n]varchar(255) (подставьте сюда своё любимое число).

29. А если я точно знаю, что выполнение будет только одно и мне не нужно кэшировать план?

30. Мантра, безусловно утюгом гвозди забивать неудобно, но разве плох для этого молоток? Другой разговор про цепочки из UNION (из-за возможных сложностей у оптимизатора при оценке требуемого memory grant'а), но опять же - нельзя что-то делать или не делать только потому что "так принято".

31. Краткая выжимка: Keep transactions as small and as short as possible. Ничего более тут не требуется разъяснять (это относится и к другим похожим пунктам)

32. Если речь об applock'ах, то это ооочень специфический инструмент и не стоит добавлять его как "generic use pattern", их как правило используют только в том случае, если не удаётся обеспечить достаточную изоляцию используя стандартные механизмы блокировки MS SQL Server, или если их использование слишком затратно для системы. (например сильно нагружает pool блокировок, или вываливается за thershold'ы и приводит к эскалации)

35. ... и на 100-й строчке кода искать определение TVV в начале модуля

36. Временные таблицы и TVV это отдельная и очень большая тема, в общем случае стоит всегда задавать себе вопрос: хорошо ли выпонимаете разницу в них, и почему именно в этом конкретном случае вам нужна именно временная таблица, а не TVV (помним про deferred compilation) в версиях 150+

38. Нельзя смешивать DML и DDL код в одном модуле, поскольку это приводит к фантомным перекомпиляциям (не столь актуально для 150+, но всё-же), это в принципе допустимо в скриптах, но никогда в модулях, создаетё времянки - делайте полное её определение, для индексов (почти всех типов) сейчас допускается inline определение прямо в конструкции
CREATE TABLE / DECLARE @tvar AS TABLE

39. А ещё лучше - прочтите статью о том, что такое эскалация блокировок

41. Если вам необходимо обеспечить уникальность - поднимите ограничения UNIQUE/PK, а не изобретайте велосипед (кроме того при patter'не UPSERT через MERGE указывать SERIALIAZABLE это строго говоря правило, от которого лишь в некоторых случаях можно отказаться (например OLTP бд))

44-45 - мантры, у TRY_CAST есть всего 2 адекватеных применения: проверка возможности приведения к XML и работа с sql_variant типами (у вас они есть?), select into в скриптах более чем допустим, в модулях нет - неявное создание временнной таблицы.

46. От использования SET ROWCOUNT стоит вообще отказываться, не только в DML. Были кстати кажется где-то тикеты про "а давайте просто объявим его как deprecated", но пока всё ограничилось следующей плашкой:

Important

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.
...
Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.

т.е. на лицо "скрытый контекст исполнения", почему отвалился триггер? - кто-то в scope'е выше поставил SET ROWCOUNT

50. Не запрещено, скорее "не нужно", любителям рассказов про "немедленное освобождение места в tempdb обратно", а вы часто создаёте террабайтные таблицы в tempdb? Если да, то зачем, для этого есть обычные БД? А вообще место для "маленьких" (несколько extent'ов, я не помню точно и не смог найти источник) таблиц высвобождается мгновенно при выходе за scope, для больших - фоновым процессом за 10-20 секунд (слишком от многого зависит)
Ручная очистка таких таблиц противоречит следующей парадигме: Keep your transactions as small and as short as possible.

Все фильтрации применять сразу в запросе, т е не рекомендуется сначала вызвать функцию, а потом применять фильтр

Зависит от функции. Например:

CREATE TABLE dbo.tmp_tmp (
  ID int identity(1,1) PRIMARY KEY CLUSTERED,
  IsActive bit NOT NULL
)
INSERT INTO dbo.tmp_tmp (IsActive) VALUES
  (1), (0), (0), (1), (0), (0), (1)
CREATE INDEX IsActive_Idx ON dbo.tmp_tmp(IsActive)

Создаем функцию:

CREATE FUNCTION dbo.fn_func_table()
RETURNS TABLE AS RETURN (
  SELECT ID, IsActive FROM dbo.tmp_tmp )

Проверяем:

SELECT t.ID FROM dbo.fn_func_table() AS t WHERE (t.IsActive = 1)

  |--Index Seek(OBJECT:([test].[dbo].[tmp_tmp].[IsActive_Idx]), SEEK:([test].[dbo].[tmp_tmp].[IsActive]=(1)) ORDERED FORWARD)
Table 'tmp_tmp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Для сравнения:

DROP FUNCTION IF EXISTS dbo.fn_func_table
GO
CREATE FUNCTION dbo.fn_func_table (@IsActive bit)
RETURNS TABLE AS RETURN (
  SELECT ID, IsActive
  FROM dbo.tmp_tmp
  WHERE IsActive=@IsActive )
GO
SELECT t.ID FROM dbo.fn_func_table(1) t

  |--Index Seek(OBJECT:([test].[dbo].[tmp_tmp].[IsActive_Idx]), SEEK:([test].[dbo].[tmp_tmp].[IsActive]=(1)) ORDERED FORWARD)
Table 'tmp_tmp'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Иными словами, для inline функций никакой разницы нет.

Разница возникнет, как только функция перестанет быть inline (в ней появится блок BEGIN ... END).

Рекомендуется в условиях фильтрации и соединении слева использовать только индексированные поля.

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

А вот использовать в в условиях фильтрации и соединении слева выражение, полностью совпадающие с выражением в WHERE частичного индекса - очень хорошо.

Так же, вместо шести индексов (a,b,c,d,e), (a,b,e,c,d), (a,b,d,e,c), (a,b,d,c,e), (a,b,c,e,d), (a,b,e,d,c) часто эффективней один (a,b) INCLUDE (c,d,e), или, иногда, (a,b,c) INCLUDE (d,e)