SQL Server. Интересные советы

NOCOUNT

Если инструкция принимает значение ON, то количество строк (которые обработаны инструкцией Transact-SQL) не возвращается. Запрещает всем инструкциям хранимой процедуры отправлять клиенту сообщения DONE_IN_PROC. Если запросы выполняются из программы, то в результирующем наборе таких инструкций Transact-SQL как: SELECT, INSERT, UPDATE и DELETE значение: “nn rows affected” (строк обработано: nn) отображаться не будет. Для хранимых процедур с несколькими инструкциями, не возвращающих большое количество строк данных, это может значительно повысить производительность за счет существенного снижения объема сетевого трафика. Инструкция SET NOCOUNT устанавливается во время исполнения, а не на этапе синтаксического анализа.

QUOTED_IDENTIFIER

ON по умолчанию. Идентификаторы можно заключать в двойные кавычки, а литералы должны быть разделены одинарными кавычками. Все строки, разделенные двойными кавычками, рассматриваются как идентификаторы объектов. Если в именах объектов базы данных используются зарезервированные ключевые слова, то параметру SET QUOTED_IDENTIFIER должно быть присвоено значение ON. При создании или изменении индексов в вычисляемых столбцах или индексированных представлениях параметру SET QUOTED_IDENTIFIER должно быть присвоено значение OFF. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически присваивают параметру QUOTED_IDENTIFIER значение ON. По умолчанию параметр SET QUOTED_IDENTIFIER имеет значение OFF для соединений из приложений DB-Library. Когда создается хранимая процедура, параметры SET QUOTED_IDENTIFIER и SET ANSI_NULLS фиксируются и используются для последующих вызовов этой хранимой процедуры. При выполнении операций внутри хранимой процедуры значение SET QUOTED_IDENTIFIER не меняется. Если параметр SET ANSI_DEFAULTS имеет значение ON, параметр SET QUOTED_IDENTIFIER включается. Параметр SET QUOTED_IDENTIFIER устанавливается во время синтаксического анализа. Настройка на время синтаксического анализа означает, что если инструкция SET присутствует в пакете или хранимой процедуре, она выполняется вне зависимости от того, достигает ли выполнение кода фактически этой точки. Кроме того, инструкция SET выполняется до выполнения любых инструкций.

ANSI_NULLS

Стандарт SQL-92 требует, чтобы операторы “=” и “<>” при использовании со значениями NULL всегда возвращали FALSE. SQL Server интерпретирует пустую строку как один пробел или действительно пустую строку в зависимости от настройки уровня совместимости. Директива SET ANSI_NULLS ON влияет только на сравнения, где в качестве одного из операндов используется NULL в виде переменной или литеральной константы. Если оба операнда представляют собой столбцы или составные выражения, эта настройка не влияет на результат сравнения. Для хранимых процедур SQL Server использует значение настройки SET ANSI_NULLS, которое действовало в момент создания процедуры. Значение SET ANSI_NULLS должно быть равно ON при выполнении распределенных запросов. SET ANSI_NULLS также должно быть ON при создании или изменении индексов вычисляемых столбцов или индексированных представлений (это один из семи обязательных для этого параметров директивы SET: ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER и CONCAT_NULL_YIELDS_NULL должны иметь значение ON, а параметр NUMERIC_ROUNDABORT – значение OFF). Драйвер ODBC и поставщик OLE DB собственного клиента SQL Server при соединении автоматически устанавливают параметру ANSI_NULLS значение ON. Для соединений из приложений DB-Library значением по умолчанию для параметра SET ANSI_NULLS является OFF. Установка значения SET ANSI_NULLS происходит во время запуска или выполнения, но не во время синтаксического анализа.

ANSI_WARNINGS

Формирует предупреждающее сообщение, если значения NULL появляются в статистических функциях: SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP или COUNT. Инструкции INSERT или UPDATE, выполнение которой привело к ошибке деления на ноль или арифметического переполнения, в соответствии со стандартом SQL-92 будут откачены и сформировано сообщение об ошибке. Конечные пробелы игнорируются для символьных столбцов, а конечные значения NULL игнорируются для бинарных столбцов. Значение ANSI_WARNINGS игнорируется при передаче аргументов хранимой процедуре или пользовательской функции, а также при объявлении и настройке переменных в инструкции пакетных заданий. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок. Параметр SET ANSI_WARNINGS должен иметь значение ON при создании или изменении индексов, основанных на вычисляемых столбцах или индексированных представлениях. Параметр ANSI_WARNINGS должен быть установлен в ON для выполнения распределенных запросов. Драйвер ODBC собственного клиента и поставщик OLE DB для собственного клиента SQL для SQL Server при соединении автоматически устанавливает параметр ANSI_WARNINGS в значение ON. Параметр SET ANSI_WARNINGS устанавливается во время выполнения, а не во время синтаксического анализа. Если значение параметра SET ARITHABORT или SET ARITHIGNORE установлено в OFF, а значение параметра SET ANSI_WARNINGS установлено в ON, то SQL Server возвращает сообщение об ошибке при обнаружении ошибок деления на ноль и переполнения.

ANSI_PADDING

Контролирует способ хранения значений, которые короче, чем заданный размер поля, а также способ хранения в полях типов: char, varchar, binary и varbinary таких значений, которые имеют оконечные пробелы. Производитель рекомендует ON. Значение параметра инструкции SET ANSI_PADDING не оказывает влияния на значения типа nchar, nvarchar, ntext, text, image, а также на большие значения, для которых SET ANSI_PADDING всегда ON. Это означает, что оконечные пробелы и нули не отбрасываются. SET ANSI_PADDING ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям. Драйвер ODBC и поставщик OLE DB для собственного клиента SQL Server при соединении автоматически устанавливают параметр ANSI_WARNINGS в значение ON. Значение параметра SET ANSI_PADDING устанавливается во время выполнения или запуска, а не во время синтаксического анализа.

ARITHABORT

Ошибка в транзакции приведёт к её откату, а не к предупреждению. Если параметры SET ARITHABORT и SET ANSI WARNINGS установлены в ON, ошибка приведёт к завершению запроса. Если SET ARITHABORT ON а SET ANSI WARNINGS OFF, ошибка прервёт пакет. Установка параметра SET ARITHABORT происходит при запуске или во время исполнения, но не во время синтаксического анализа. SET ARITHABORT ON необходимо при создании или изменении индексов по вычисляемым столбцам или индексированным представлениям.

XACT_ABORT

Если произошла ошибка при исполнении инструкции Transact-SQL, транзакция будет откачена целиком. Инструкция SET XACT_ABORT не влияет на компиляцию ошибок (например, синтаксических). Параметр XACT_ABORT должен иметь значение ON для инструкций изменения данных в явных или неявных транзакциях, применяющихся к большинству поставщиков OLE DB, включая SQL Server. Единственным случаем, когда этот параметр не требуется, является поддержка поставщиком вложенных транзакций. Дополнительные сведения см. в разделе Распределенные запросы и распределенные транзакции. Значение параметра XACT_ABORT устанавливается во время выполнения, а не во время синтаксического анализа. Включение этого параметра позволяет не заботиться об обработке ошибок при вставках и изменениях.

CONCAT_NULL_YIELDS_NULL

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

NUMERIC_ROUNDABORT

Потеря точности не приводят к формированию сообщений об ошибках, а результат округляется с точностью столбца или переменной, в которых будет сохранен. Потеря точности происходит, когда выполняется попытка сохранения значения с фиксированной точностью в столбце или переменной с меньшей точностью. Если параметру SET NUMERIC_ROUNDABORT присвоено значение ON, параметр SET ARITHABORT определяет серьезность формируемой ошибки. В следующей таблице показано влияние этих двух параметров на сообщения об ошибках при потере точности. Значение параметра SET NUMERIC_ROUNDABORT задается на этапе выполнения или запуска, но не на этапе синтаксического анализа. При создании или изменении индексов вычисляемых столбцов или индексированных представлений параметр SET NUMERIC_ROUNDABORT должен принимать значение OFF.

Общие рекомендации

1. Избегите использования звёздочки (*) в SELECT, всегда перечисляйте только необходимые столбцы.
2. В инструкции INSERT всегда указывайте имена столбцов.
3. Всегда присваивайте таблицам (а при необходимости и столбцам) псевдонимы – это позволяет избежать путаницы. При использовании псевдонима столбца обязательно добавляйте ключевое слово AS.
4. При ссылке на объект всегда указывайте схему (владельца).
5. Избегите использования non-SARGable предикатов (“IS NULL”, “<>”, “!=”, “!>”, “!<“, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, “LIKE ‘%500′”, CONVERT и CAST, Строковые функции: LEFT(Column,2) = ‘GR’ , Функции даты/времени: DATEPART (mm, Datecolumn) = 5, Математические операции со столбцом: qty+1> 100 ).
6. Для сокращения числа итераций старайтесь по возможности использовать строчный оператор CASE. Например:

select sum(case when e.age < 20 then 1 else 0 end) as under_20

        , sum(case when e.age >= 20 and age <= 40 then 1 else 0end) as between_20_40

       , sum(case when e.age > 40 then 1 else 0 end) as over_40

from dbo.employee e

7. Используйте индексы. Что бы понять, работает ли индекс, всегда проверяйте планы исполнения разрабатываемых запросов.
8. Используйте формат даты по стандарту ISO – yyyymmdd или ODBC – yyyy-mm-dd hh:mi:ss
9. Используйте ANSI стиль соединений. Для левых соединений опускайте ключевое слово OUTER.
10. Для форматирования кода используйте стандартный размер табуляции – четыре символа, и отделяйте логически независимые модули кода пустой строкой.
11. Старайтесь не использовать недокументированные средства.
12. Если важна безопасность, не используйте динамический SQL.
13. Порядок сортировки задавайте только предложением ORDER BY.
14. Старайтесь хранить скрипты объектов схемы и серверного кода в системе управления версиями (например: VSS или CVS), и включать теги редакций в блок описания назначения скрипта.
15. Всегда располагайте все DLL команды в начале кода, дабы избежать лишних компиляций.
16. Избегите использования триггеров и курсоров, оставьте эти инструменты на крайний случай, когда по-другому задачу решить невозможно. Если пришлось писать курсор, предпочтение отдавайте локальным, в режиме: FAST_FORWARD, они самые диетические из всех остальных.
17. Для повышения производительности соединений, когда ничего другого уже не помогает, используйте индексированные представления соединяемых точно таким же образом таблиц (в не Enterprise редакциях нужно добавлять подсказку NOEXPAND).
18. Следует помнить, что представления могут маскировать необходимые для оптимизации метаданные, например, когда они скрывают соединения/объединения таблиц из разных баз данных, или когда не задействованы используемые для внутреннего соединения столбцы. В подобных случаях, всегда проверяйте план исполнения запроса, что бы вовремя принять меры по исправлению ситуаций с не оптимальным планом запроса.
19. Старайтесь делать определяемые пользователем функции детерминированными, они дают более эффективные планы исполнения.
20. Никогда не используйте в именах процедур префикс “sp_”, он зарезервирован для системных процедур, которые вначале ищутся в базе master.

Неявное преобразование типов

Если оператор связывает два выражения различных типов данных, то по правилам приоритета типов данных определяется, какой тип данных имеет меньший приоритет и будет преобразован в тип данных с большим приоритетом. Если неявное преобразование не поддерживается, возвращается ошибка. Если оба операнда выражения имеют одинаковый тип данных, результат операции будет иметь тот же тип данных. В SQL Server 2005 используется следующий приоритет типов данных:

1. определяемые пользователем типы данных (высший приоритет);
2. sql_variant;
3. xml;
4. datetime;
5. smalldatetime;
6. float;
7. real;
8. decimal;
9. money;
10. smallmoney;
11. bigint;
12. int;
13. smallint;
14. tinyint;
15. bit;
16. ntext;
17. text;
18. image;
19. timestamp;
20. uniqueidentifier;
21. nvarchar;
22. nchar;
23. varchar;
24. char;
25. varbinary;
26. binary (низший приоритет).

Автор: Александр Гладченко