Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 14

  Количество данных в таблице;

  Наличие и тип индексов таблицы, и распределение данных в индексных колонках;

  Сопоставляет операторы и значения, которые используются в WHERE.

  Наличие объединений.

Компиляций подразумевает процесс анализа встроенной процедуры и создания плана выполнения, который помещается в кэш процедур. Этот кэш хранит часто используемые планы процедур. Факторы, которые увеличивают шансы плана для включения в кэш:

  Время, необходимое для перекомпиляции

  Частота использования

Преимущества встроенных процедур

Встроенные процедуры представляют множество преимуществ. С ними вы можете:

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

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

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

  Повышается производительность. Процедуры выполняют множество задач, как набор операторов Transact-SQL.

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

9.2 Создание, выполнение, изменение и удаление процедур

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

Создание встроенных процедур

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

Для создания процедуры используйте оператор CREATE PROCEDURE. При создании примите во внимание следующие факты:

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

  Если процедура создаёт временную локальную таблицу, таблица существует только во время выполнения и невидима после завершения выполнения.

  Для создания процедуры, вы должны иметь соответствующие права, например быть владельцем базы данных или администратором.

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

Синтаксис:

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

Следующий пример создаёт процедуру, которая отображает просроченные счета:

USE Northwind

GO

CREATE PROC dbo.OverdueOrders

AS

SELECT *

FROM dbo.Orders

WHERE RequiredDate<GETDATE() AND SnippedDate IS NULL

GO

Процедуры могут быть вложенными (одна процедура может вызывать другую). Характеристики вложенных процедур следующие:

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

  Текущей уровень вложенности хранится в системной функции @@nestlevel.

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

  Встроенные процедуры могут быть рекурсивными. Например, если процедура 1 вызвала процедуру 2, то процедура 2 может вызвать первую.

Для просмотра информации о встроенных процедурах используйте системные процедуры sp_help, sp_helptext, sp_depends. Для просмотра всех имён процедур базы данных используйте sp_stored_procedures.

Рекомендации по созданию процедур

Примите во внимание следующие рекомендации, когда создаёте процедуры: