Создание, отладка и оптимизация SQL – модулей

 

Цель работы– приобретение навыков использования интегрированной среды утилиты SQL Server Query Analyzer для создания, хранения, отладки и оптимизации программных модулей языка Transact – SQL.

 

Основной функцией сервера SQL Server 2000, выполняемой его службой MSSQLServer, является интерпретация команд языка Transact – SQL, поступающих от утилиты SQL Server Query Analyzer, клиентских приложений, службы автоматизации исполнения заданий SQLServerAgent, служб преобразования данных DTS (Data Transformation Services) и других источников. Логически завершенная последовательность команд называется запросом и оформляется в виде пакета, заканчивающегося системной командой GO. Эта команда не является командой языка Transact – SQL и сигнализирует системе о конце пакета.

Пакет команд сервера должен строиться с учетом следующих правил:

1. Нельзя в одном пакете изменять таблицу и ссылаться на ее новую колонку.

2. Команды создания объектов типа DEFAULT, PROCEDURE, RULE, TRIGGER и

VIEW не могут комбинироваться с другими командами в том же пакете.

3. Команда CREATE должна начинать пакет, и если другие команды допустимы, то они должны дополнять первую команду CREATE.

Пакет определяет область действий локальных переменных и перед интерпретацией преобразуется в один модуль – план исполнения пакета. В службе SQLServerAgent пакет может быть шагом задания (job), а в службах DTS – составлять задачу (task) пакета этих служб (package). Следует отметить, что если команда EXECUTE является первой командой пакета, то для этого пакета не требуется задавать ключевое слово EXECUTE.

Для создания нового пакета надо щелкнуть левой клавишей мыши по пиктограмме New Query (Ctrl ^ N) слева на панели инструментов или исполнить команду File/New… и выбрать элемент Blank Query Window (пустое окно для запроса), при этом предварительно необходимо подсоединиться к серверу с помощью команды File/Connect… . После выполнения этих действий открывается окно текстового редактора, в котором необходимо сформировать требуемый запрос. В меню Edit имеется полный набор команд для редактирования текста. Логические части набираемых команд имеют различное цветовое представление. После формирования пакета запросов его можно сохранить в любом файле с расширением *.sql, используя команды Save, Save As… и Save All Queries меню File. При необходимости сохраненные пакеты можно повторно открыть, исполнив команду File/Open… или найдя соответствующий файл в списке Recent File List того же самого меню File.

Таким образом, пакеты можно создавать, хранить и редактировать как обычные программные модули. Однако, хранятся они в обычных файлах, которые не являются ни объектами баз данных, ни объектами сервера в отличии от хранимых процедур или функций, которые сохраняются на сервере и имеют владельца. Сохраняемые в файлах пакеты, хранимые системные и пользовательские процедуры, а также системные и пользовательские функции составляют набор модулей, который система программирования MS SQL Server 2000 предоставляет разработчикам и пользователям баз данных этой системы.

Для создания типовых пакетов интегрированная среда SQL Query Analyzer имеет набор шаблонов, открываемых командой File/New…, щелчком клавиши мыши по вкладке Templates или по треугольнику справа от пиктограммы New Query панели инструментов: Attach and Detach Database, Create Database, Create Table и т.д. Большинство из этих шаблонов определяют сценарии создания объектов, которые используются при переносе объектов и баз данных с одного сервера на другой.

 

Каждый шаблон имеет две части:

1. неизменяемуючасть, которая переходит в формируемый пакет без каких-либо изменений;

2. изменяемуючасть, которая состоит из одного или нескольких списков, выделяемых символами “<” и “>”; каждый список включает три элемента: синтаксическое понятие, его тип и экземпляр этого понятия.

Формирование пакета по шаблону заключается в удалении первых двух элементов каждого списка, символов“<” и “>” и замене экземпляра понятия на собственное название. В шаблоне созданию каждого объекта предшествует проверка его наличия в базе данных и его удаление. Если он в ней найден.

Приведем пример шаблона для создания базы данных: IF EXISTS (SELECT *

FROM master..sysdatabases

WHERE name=’<databases_name, sysname, test_db>) DROP DATABASES <databases_name, sysname, test_db >

GO

CREATE DATABASES <databases_name, sysname, test_db > GO

 

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

После создания пакета можно проверить его синтаксис, исполнив команду Query/Parse Query, а затем и выполнить с помощью команды Query/Execute Query или пиктограммы с тем же названием. Если требуется оценить оптимальность использования ресурсов при выполнении запроса, то следует воспользоваться графической диаграммой его логических шагов, которая создается после исполнения команды Query/Display Estimated Execution Plan. В дальнейшем можно организовать сбор статических данных при выполнении того или иного запроса.

Интегрированная среда SQL Query Analyzer позволяет вести отладку хранимых процедур. Для этих целей следует выбрать вновь созданную процедуру в списке пользовательских хранимых процедур рассматриваемой базы данных и в ее контекстном меню исполнить команду Debug. Если требуется отладить пакет или функцию, то их следует предварительно оформить в виде хранимой процедуры. При переходе в отладочный режим в специальном окне необходимо задать значения входных параметров.

При трассировке процедуры в окнах отражаются имена и значения локальных и глобальных переменных, а также имена вызываемых процедур (Callstack). Отладочные команды позволяют устанавливать и отменять точки останова выполнять пошаговое выполнение с различными модификациями (с включением вызываемых процедур, с их пропуском и т.д.), отменять выполненные при исполнении процедуры работы, производить повторный запуск процедуры и т.п.

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

SQL Server 2000 позволяет создавать сценарии для любых объектов, которые имеются в той или иной базе данных. Для этих целей необходимо выбрать объект и в его контекстном меню исполнить команду:

All Tasks/Generate SQL Scripts

 

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

Вкладка General окна Generate SQL позволяет выбрать объекты, для которых будут создаваться сценарии: Script all objects (для всех объектов), All tables (для всех таблиц), All views (для всех представлений) и т.д. Если необходимо выбрать только несколько конкретных объектов, их нужно ввести в список Objects to be scripted (объекты для создания сценариев) на той же вкладке. Выбрав объекты, следует щелкнуть по кнопке Preview для получения сценариев этих объектов. Кнопка Copy позволяет сохранить сценарии в буфере обмена, откуда они могут быть переписаны в файл и загружены в окно SQL Query Analyzer для создания объектов в другой базе данных.

На вкладке Formatting можно выполнить настройку процесса создания сценариев: с генерацией команды CREATE для каждого объекта или без этой команды; с генерацией команды

DROP для каждого объекта или без этой команды; с генерацией сценариев для всех зависимых объектов, что необходимо для сохранения логической целостности объектов; с включением пояснительных заголовков для объектов и др.

Вкладка Options содержит три части:

1) Security Scripting Options– параметры сценариев системы безопасности для управления генерированием кода для пользователей и ролей базы данных, учтенных записей и разрешений:

2) Table Scripting Options– параметры сценариев для таблиц: создание индексов,

триггеров, ключей и т.п.;

3) File Format– формат файла: MS-DOS, Windows, Unicode, а также Files to Generate

– указание на размещение в одном файле всех объектов или каждого объекта в отдельном файле.

 

Задание1. Загрузить пакет команд SQLExpression, демонстрирующий основные возможности языка Transact – SQL, который был создан ранее. С помощью команды Query/Parse Query проверить синтаксис команд пакета. Оформить пакет в виде хранимой процедуры без параметров, исполнить команду Debug в контекстном меню этой процедуры и провести ее трассировку, используя все отладочные возможности этого режима. После отладки процедуры оценить оптимальность использования ресурсов и организовать сбор статистических данных. Процедуру сохранить в базе данных master под именем sp_TestSQL.

 

Задание 2.Используя шаблоны Templates, создать базу данных DB Templates и один или несколько объектов всех типов, для которых имеются шаблоны. Сохранить пакет команд в файле SQLTemplates. Протестировать пакет

 

Задание 3. С помощью графических мастеров создать базу данных DBWizard и набор объектов всех типов для которых имеются графические мастера. Создать сценарии для базы данных DBWizard и всех ее объектов и сохранить в файле DB Script с точно такими же объектами, что и у DBWizard.

 

Задание 4. Используя генерацию сценариев, создать руссифицированный вариант базы данных Pubs, задав имя этой базе RusPubs.

 

Задание 5. Для всех таблиц баз данных Pubs и RusPubs выдать на экран справочную информацию, исполнив команду:

EXECUTE sp_help имя таблицы

Убедиться, что все справочные таблицы для соответствующих таблиц этих баз данных совпадают: таблицы с общей информацией, со свойствами кнопок, со счетчиками IDENTITY, с индексами и ссылками.