Использование мастера Index Tuning Wizard
Настройка базы данных не может выполняться в пустоте. Не имеет смысла вопрос, какая схема работает лучше в абсолютном выражении. Нас прежде всего интересует, какое сочетание представлений и индексов приведет к наиболее быстрому выполнению конкретных операций. По этой причине мастеру Index Tuning Wizard требуется предоставить определенные данные о загруженности, в качестве которых может выступать либо SQL-сценарий, либо данные трассировки сервера из SQL Profiler.
Вторая страница мастера требует, чтобы вы указали сервер и базу данных для анализа, а также предоставляет две дополнительных опции выбора: Keep All Existing Indexes (Не изменять существующие индексы) и Tuning Mode (Режим настройки). Опция режимов настройки Tuning Mode задает глубину анализа, выполняемого мастером. Сбросив установленный по умолчанию флажок Keep All Existing Indexes (Не изменять существующие индексы), вы позволите мастеру выдавать рекомендации относительно индексов, которые не играют роли для выбранной рабочей нагрузки. Следует проявлять осторожность этой опцией, поскольку, несмотря на то, что индексы могут и не вовлекаться в анализируемую рабочую нагрузку, они существенно сказываются на производительности некоторых других запросов, не затрагиваемых в тестировании.
После того как мастер закончит анализ, вам представляется несколько возможностей выполнения рекомендаций: выполнить их немедленно, составить расписание их выполнения в будущем, или записать их в SQL-сценарий для последующего выполнения.
Используйте мастер Index Tuning Wizard для настройки базы данных
В базе данных Aromatherapy раскройте папку Indexes таблицы Oils.
увеличить изображение
Выберите индекс Oil_PlantParts и нажмите кнопку Delete (Удалить). Query Analyzer запросит подтверждение об удалении индекса.
Нажмите OK. Query Analyzer удалит индекс.
увеличить изображение
Перейдите к окну Query (Запрос) и из меню Query (Запрос) выберите Index Tuning Wizard (Мастер настройки индексов). Query Analyzer отобразит первую страницу мастера настройки индексов Index Tuning Wizard.
Нажмите Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать базу данных и режим настройки.
Убедитесь, что выбрана база данных Aromatherapy и затем выберите режим настройки Thorough (Полная).
Нажмите Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать рабочую нагрузку для анализа.
Нажмите кнопку Advanced Options (Дополнительные параметры). Мастер отобразит диалоговое окно с параметрами настройки индексов.
Примите установленные по умолчанию значения настройки, нажав OK.Примите установленные по умолчанию значения SQL Query Analyzer Selection, нажав кнопку Next (Далее). Мастер отобразит страницу, приглашающую вас выбрать таблицу для настройки.
Использование Query Analyzer для оптимизации производительности
В добавлении к панели редактирования Editor Pane, окно Query (Запрос) анализатора запросов SQL Server Query Analyzer предоставляет три дополнительных панели для анализа производительности отдельных запросов. Панель Execution Plan Pane содержит графическое представление задач, которые SQL Server будет обрабатывать для выполнения запроса. Панель Trace Pane показывает детальную информацию о выполнении запроса на стороне сервера, включая время и число операций чтения и записи. Панель Client Statistics Pane отображает информацию о выполнении запроса на стороне клиента, включая количество обращений и ответов от сервера и пропускную способность сети.
Клиентская статистика
Последней утилитой для анализа запросов, предоставляемой окном Query (Запрос) Query Analyzer является панель клиентской статистики Client Statistics Pane, которая отображает выполнение запроса на стороне клиента.
Информация в панели клиентской статистики Client Statistics Pane делится на три раздела: Application Profile Statistics, в котором содержится информация о количестве выполненных операторов Transact-SQL и выполненных строках; Network Statistics, в котором содержится информация о сформированном трафике сети; Time Statistics, который помогает вам определить где происходит замедление: на клиенте или на сервере.
Совет. Раздел статистика сети Network Statistics, обеспечиваемая панелью клиентской статистики Client Statistics Pane, будет присутствовать, даже если вы подключены к локальному серверу.
Отобразите клиентскую статистику
Если вы закрыли окно Query (Запрос) после предыдущего упражнения, то снова откройте его и введите в панели редактирования Editor Pane следующий оператор Transact-SQL:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS NumberOfOils FROM Oils INNER JOIN PlantParts On Oils.PlantPartID = PlantParts.PlantPartID GROUP BY PlantParts.PlantPart
увеличить изображение
В окне Query (Запрос) выберите Show Client Statistics (Показать клиентскую статистику).Нажмите кнопку Execute Query (Выполнить запрос)
Мастер настройки индексов Index Tuning Wizard
Анализатор запросов Query Analyzer предоставляет еще одну утилиту для оптимизации приложений вашей базы данных: мастер настройки индексов Index Tuning Wizard. Анализируя вашу базу данных и внося предложения по увеличению производительности, Index Tuning Wizard сохраняет огромное количество времени, которое вам пришлось бы затратить, осуществляя тестирование производительности методом проб и ошибок.
Планы выполнения
Панель планов выполнения Execution Plan Pane окна Query (Запрос) графически отображает последовательность выполнения вашего запроса SQL Server. На рис. 23.1 представлен план выполнения для простого оператора SELECT:
SELECT OilName, LatinName FROM Oils ORDER BY LatinName
увеличить изображение
Рис. 23.1. Панель плана выполнения Execution Plan Pane окна Query (Запрос).
Совет. Информация, отображаемая в панели плана выполнения Execution Plan Pane, идентична тексту, отображаемому опцией SHOWPLAN базы данных, которая хорошо известна пользователям предыдущих версий SQL Server и все еще присутствует в SQL Server 2000. Если оператор SET SHOWPLAN_ALL ON выполняется как часть сценария в окне Query (Запрос), то результаты будут отображаться в панели сетки Grids Pane. Панель Execution Plan Pane отображает информацию в формате, который понятен большинству людей.
Панель Execution Plan Pane использует довольно большое количество значков для представления операций, которые может выполнить обработчик запросов. Значки описаны в документации SQL Server Books Online, но нет большой необходимости изучать их. Просто наведите курсор мыши на значок и удерживайте некоторое время на нем, после чего отобразится окно подсказки, описывающее не только действие, представляемое значком, но и некоторый объем полезной информации, такой как цена выполнения ввода/вывода I/O, цена загрузки процессора, число строк в операции и итоговая цена операции. Рис. 23.2 показывает окно подсказки для плана выполнения операции кластерного индексного сканирования Clustered Index Scan, представленного на рис. 23.1.
увеличить изображение
Рис. 23.2. Окно подсказки для операции Clustered Index Scan.
Операции в плане выполнения исполняются слева направо. Окно подсказки для каждой стрелки, соединяющей операции, показывает число строк, выполненных в предыдущей операции и расчетный размер каждой строки, как показано на рис. 23.3.
увеличить изображение
Рис. 23.3. Окно подсказки для соединительных стрелок.
Помимо отображения операций, которые SQL Server будет исполнять при выполнении определенного запроса, план выполнения также предоставляет механизм для оптимизации запроса. Используя контекстное меню панели плана выполнения Execution Plan Pane, вы можете обновлять статистику, используемую оптимизатором запросов при определении стратегии выполнения, и добавлять индексы для оптимизации производительности.
увеличить изображение
В меню Query (Запрос) выберите Show Execution Plan (Показать план выполнения).
Примечание. Во время выполнения запроса панель Execution Plan Pane не отображается.
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Query Analyzer выполнит запрос и отобразит результаты в панели сетки Grids Pane.
увеличить изображение
Выберите вкладку Execution Plan (План выполнения).
увеличить изображение
Добавьте индекс в панели Execution Plan Pane
В панели плана выполнения запроса Execution Plan Pane щелкните правой кнопкой мыши на значке, представляющем операцию Clustered Index Scan
Нажмите кнопку New (Создать). Query Analyzer отобразит диалоговое окно Create New Index (Создание нового индекса).
Введите в качестве имени индекса Oils_PlantParts и выделите строку PlantPartID для включения ее в индекс.
Нажмите OK. Query Analyzer создаст индекс и отобразит его в диалоговом окне Manage Indexes (Управление индексами).
Закройте диалоговое окно Manage Indexes (Управление индексами).Нажмите кнопку Execute Query (Выполнить запрос)
увеличить изображение
Трассировка сервера
Вторая утилита Query Analyzer предоставляет возможности анализа производительности запроса через трассировку сервера. Панель Trace Pane показывает команды, которые выполняются на сервере во время исполнения запроса. Команды не соответствуют операциям в плане выполнения – ряд команд выполняется дополнительно, а реальные команды Transact-SQL не будут показаны столь же детально.
Совет. SQL Server 2000 также предоставляет другое средство для выполнения трассировки сервера - SQL Profiler. Утилиту SQL Profiler мы не будем рассматривать в этом курсе.
Отобразите трассировку сервера
Если вы закрыли окно Query (Запрос) после предыдущего упражнения, то снова откройте его и введите в панели редактирования Editor Pane следующий оператор Transact-SQL:
SELECT PlantParts.PlantPart, Count(Oils.OilName) AS NumberOfOils FROM Oils INNER JOIN PlantParts On Oils.PlantPartID = PlantParts.PlantPartID GROUP BY PlantParts.PlantPart
увеличить изображение
В меню Query (Запрос) выберите Show Server Trace (Показать трассировку сервера).Для выполнения запроса в панели инструментов анализатора Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
увеличить изображение
Арифметические операции
Transact-SQL предоставляет операции для выполнения основных арифметических действий. Соответствующие операторы показаны в таблице 24.4. Эти операторы в точности выполняют то, что они обозначают. Только один оператор может оказаться для вас незнакомым, это арифметический модуль (modulo), который возвращает целую часть (целое число) остатка от деления. Например, результатом выражения 16 % 3 будет 1, а не 5 1/3.
| + | Сложение. |
| - | Вычитание. |
| * | Умножение. |
| / | Деление. |
| % | Остаток от деления. |
| + | Положительное число. |
| - | Отрицательное число. |
Используйте арифметические операции в операторе SELECT
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).
В корневой директории, в папке SQL 2000 Step by Step выберите файл Arithmetic и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).
Закройте окно Query(Запрос).
Другие команды
Остались нерассмотренными еще три набора команд Transact-SQL. Первый набор команд управляет использованием программных переменных. Мы рассмотрим эти команды в уроке 25.
Набор команд управления потоком контролирует выполнением операторов в SQL-сценарии. Команды управления потоком мы рассмотрим в уроке 26. Набор команд для работы с курсорами управляет поведением объекта специального типа – курсора, который указывает на определенную запись в таблице или представлении. Курсоры мы рассмотрим в уроке 27.
Другие операции
Transact-SQL предоставляет еще две полезных операции, которые описаны в таблице 24.8. В уроке 12 мы уже использовали операцию конкатенации строк +. Операция конкатенации прибавляет содержимое одной строки к другой.
Операция присвоения =, присваивает значение, стоящее справа от, оператора значению, стоящему слева от оператора. Учтите, что здесь порядок отличается от того, который вы изучали в школе: не "a+b=c", а "c=a+b". Мы будем использовать операцию присвоения далее в этом уроке при изучении переменных.
| + | Конкатенация строк. |
| = | Присвоение. |
Используйте операцию конкатенацию в операторе SELECT
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем Concatenation и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Функции агрегирования
Функции агрегирования, представленные в таблице 24.11, принимают в качестве параметра коллекцию значений и возвращают одиночное значение.
| AVG | Возвращает среднее значение из коллекции, игнорируя нулевые (NULL) значения. |
| COUNT | Возвращает количество значений в коллекции, включая и нулевые. |
| MAX | Возвращает наибольшее значение из коллекции. |
| MIN | Возвращает наименьшее значение из коллекции. |
| SUM | Возвращает сумму значений из коллекции, игнорируя нулевые значения. |
| STDEV | Возвращает стандартное статистическое отклонение для каждого из значений в коллекции. |
| STDEVP | Возвращает стандартное статистическое отклонение все совокупности значений в коллекции. |
| VAR | Возвращает статистическую вариацию значений в группе. |
| VARP | Возвращает статистическую вариацию всех значений в коллекции. |
Используйте функции агрегирования
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите файл с именем Aggregate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Функции безопасности
Функции безопасности, представленные в таблице 24.13, возвращают информацию о привилегиях безопасности, имеющихся для пользователей и ролей.
| HAS_DBACCESS | database_name | Показывает, имеет ли текущий пользователь доступ к базе данных database_name. |
| IS_MEMBER | group_or_role | Показывает, имеет ли текущий пользователь членство в группе или роли group_or_role. |
| IS_SRVROLEMEMBER | role [, login] | Показывает, имеет ли текущая или указанная учетная запись login членство в роли role. |
| SUSER_SID | [login] | Для текущей или указанной учетной записи login возвращает идентификационный номер безопасности (SID). |
| SUSER_SNAME | [] | Возвращает имя учетной записи по ее идентификационному номеру безопасности SID. |
| USER_ID | [user] | Возвращает идентификационный номер текущего или указанного пользователя user. |
| USER | Возвращает имя текущего пользователя базы данных. |
Используйте функции безопасности
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer кнопку нажмите New Query (Новый запрос).
Выберите файл с именем Security и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Функции даты и времени
Функции даты и времени принимают в качестве входных значений дату и время и возвращают либо строковые, числовые значения, либо значения в формате даты и времени. (Помните, что в SQL Server, время считается компонентом типа данных datetime). Параметр единицы, фигурирующий во многих функциях, обычно обозначает единицы измерения времени, например такие, как "год" или "минута". В таблице 24.9 представлены функции даты и времени Transact-SQL.
| DATEADD | единицы, число, дата | Рассчитывает новую дату, добавляя к существующей указанное число единиц (дней, месяцев, часов и т.д.). |
| DATEDIFF | единицы, нач_дата, кон_дата | Возвращает количество единиц времени, между двумя указанными датами. |
| DATENAME | единицы, дата | Возвращает имя указанной единицы времени даты в виде строки. |
| DATEPART | единицы, дата | Возвращает имя указанной единицы времени даты в виде числа. |
| DAY | дата | Возвращает день для указанной даты в виде числа. |
| GETDATE | Возвращает текущее системное время и дату. | |
| MONTH | дата | Возвращает месяц для указанной даты в виде числа. |
| YEAR | дата | Возвращает год для указанной даты в виде числа. |
Используйте функции даты
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем DateTime и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Функции метаданных
Функции метаданных возвращают информацию о данных, а не сами данные. Имеется очень большое количество функций метаданных. В таблице 24.12 представлен перечень наиболее часто используемых функций метаданных.
| COL_LENGTH | table, column | Возвращает количество байт столбца column. |
| COL_NAME | tableID, columnId | Возвращает имя columnID. |
| COLUMNPROPERTY | ID, column, property | Возвращает информацию о свойстве property столбца column. |
| DATABASEPROPERTY | database, property | Возвращает значение свойства property. |
| DB_ID | database_name | Возвращает идентификационный номер базы данных Database_name. |
| DB_NAME | databaseID | Возвращает имя базы данных по идентификатору databaseID. |
| INDEX_COL | table, indexID, keyed | Возвращает имя индексированного столбца по идентификаторам indexID и keyID. |
| INDEXPROPERTY | tableID, index, property | Возвращает информацию о свойстве property индекса index. |
| OBJECT_ID | object | Возвращает идентификационный номер объекта object базы данных. |
| OBJECT_NAME | objectID | Возвращает имя объекта по его идентификационному номеру objectID. |
| OBJECTPROPERTY | ID, property | Возвращает информацию о свойстве property объекта по его идентификационному номеру ID. |
| SQL_VARIANT_PROPERTY | SQL_variant, property | Возвращает указанное свойство property варианта Sql_variant. |
| TYPEPROPERTY | datatype, property | Возвращает информацию о свойстве property для типа данных datatype. |
Используйте функции метаданных
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите файл с именем Metadata и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Функции Transact-SQL
Функция очень похожа на операции, но вместо одного символа, соответствующего выполнению одного действия, функция состоит из множества операций. Функция может выполнять одно логическое (logical) действие, но это логическое действие может состоять из любого множества физических действий. Например, функция, перемещающая строку из одной таблицы в другую, выполняет одно логическое действие (перемещение), но при этом два отдельных физических действия (INSERT и DELETE).
Нововведением в SQL Server 2000 является возможность создания своих собственных функций, которые называются пользовательскими функциями (user-defined). Их мы рассмотрим в уроке 30.
Transact-SQL также предоставляет несколько встроенных функций, которые мы и будем рассматривать.
Примечание. В таблицах в этом разделе представлены не все функции. Полный перечень функций доступен в панели Object Browser в папке Common Objects.
Использование функций
Встроенные функции Transact-SQL классифицируются по характеру возвращаемого ими результата: они могут быть либо детерминированными (deterministic), либо недетерминированными (non-deterministic). Детерминированная функция, получая одни и те же значения данных, которыми будет оперировать, всегда будет возвращать одинаковый результат: SQRT(9) всегда возвращает 3, следовательно, функция SQRT детерминированная. Недетерминированная функция, такая как RAND, наоборот, при каждом обращении всегда возвращает различные значения.
В Transact-SQL функции могут использоваться в самых различных случаях. В столбцах со значениями по умолчанию, в вычисляемых столбцах в таблицах или представлениях, в условии отбора в фразе WHERE и т. д. Тем не менее, детерминизм функции определяет, может ли она использоваться в качестве индекса. Индекс всегда должен возвращать согласующиеся результаты, и только детерминированные функции могут использоваться в индексах.
Команды администрирования базы данных
Большинство команд Transact-SQL, поддерживающие администрирование базы данных, доступны интерактивно через средства Enterprise Manager. Собственно команды администрирования позволяют выполнить эти же задачи программно.
Команды администрирования базы данных показаны в таблице 24.3. Команды GRANT, DENY и REVOKE управляют средствами ограничения доступа и защиты базы данных (безопасностью). Команды BACKUP, RESTORE и UPDATE STATISTICS дублируют функциональные возможности планировщика обслуживания в Enterprise Manager.
Команда SET используется совместно с ключевыми словами, например такими, как DATEFORMAT и LANGUAGE, для управления текущим сеансом SQL Server. В Enterprise Manager большинство из этих переменных доступны из диалогового окна свойств базы данных.
Последние две команды администрирования базы данных, KILL и SHUTDOWN, используются для управления работой SQL Server. Команда KILL заканчивает выполнение операций, ассоциированных с соединением с определенным пользователем. Команда SHUTDOWN безусловно завершает работу SQL Server.
| GRANT | Устанавливает определенные разрешения для объекта безопасности. |
| DENY | Отключает определенные разрешения для объекта безопасности, и предотвращает наследование объектом разрешений через его членство в роли или группе. |
| REVOKE | Удаляет определенное разрешение для объекта безопасности. |
| BACKUP | Создает резервную копию базы данных или журнала трансакций. |
| RESTORE | Восстанавливает данные после резервирования. |
| UPDATE STATISTICS | Обновляет статистику, используемую обработчиком запросов. |
| SET | Управляет окружением SQL Server. |
| KILL | Завершает соединение и все связанные с ним процессы. |
| SHUTDOWN | Отключает SQL Server. |
Команды манипулирования данными
Наиболее важные команды SQL относятся к группе команд языка управления данными (DML), который мы рассматривали ранее. Команды DML используются для вставки, изменения, удаления и извлечения данных.
Команды DML представлены в таблице 24.1. Большинство из них вам хорошо знакомы из уроков частей 3 и 4. Командами, с которыми мы не сталкивались, являются BULK INSERT – позволяющая вставлять множество строк из файла данных, и USE – указывающая на базу данных, которая будет использоваться в SQL-сценарии.
| INSERT | Вставляет строки в таблицу или представление. |
| UPDATE | Изменяет строки в таблице или представлении. |
| DELETE | Удаляет строки из таблицы или представления. |
| SELECT | Извлекает строки из таблицы или представления. |
| TRUNCATE TABLE | Удаляет все строки из таблицы или представления. |
| BULK INSERT | Вставляет строки из файла данных в таблицу или представление. |
| USE | Выполняет соединение с базой данных. |
Команды определения данных
Команды языка определения данных (DDL) представлены в таблице 24.2. Команды DDL используются для создания, изменения и удаления объектов базы данных. В этом языке существует только три основных команды, каждая команда имеет несколько вариаций, зависящих от характера создаваемого вами объекта базы данных (см. уроке 22).
| CREATE | Определяет новый объект базы данных. |
| ALTER | Изменяет определение объекта базы данных. |
| DROP | Удаляет объект базы данных из базы данных. |
Команды Transact-SQL
В основе языка Transact-SQL лежат команды: "стержневые" операторы, описывающие фундаментальные операции, которые может выполнить язык.
Зарезервированные слова
Зарезервированное слово – это одно из средств, используемых языком Transact-SQL. Если вы используете зарезервированное слово как идентификатор, например, в качестве имени столбца, вы должны окружить это имя специальными символами, называемыми ограничителями (delimiters). В Microsoft SQL Server ограничительными символами являются [ и ]. Например, если вы используете SELECT в качестве имени столбца, вы должны при ссылке на этот столбец в запросе указать [SELECT], чтобы SQL Server воспринял это как идентификатор. (По возможности старайтесь избегать использования зарезервированных слов в качестве идентификаторов.)
То, что мы называем командой, в документации SQL Server Books Online обозначается как "зарезервированные ключевые слова" (reserved keywords). Этот термин не очень удачен, поскольку нет большого различия между "зарезервированные ключевые слова" и любым другим зарезервированным словом. По этой причине мы будем использовать термин команда (command), который означает определенный набор зарезервированных ключевых слов, которые представляют действия, выполняемые SQL Server.
Принимая во внимание сложность приложений, которые могут быть созданы с помощью Transact-SQL, вам будет весьма удивительно узнать, что язык состоит всего из нескольких команд, со многими из которых мы уже познакомились.
Изучать команды Transact-SQL будет намного проще, если разделить их на группы по типу выполняемых действий, например таких, как манипулирование данными в таблице или представлении, или управление операционным окружением SQL Server.
Мы уже использовали команды Transact-SQL. Например, вводили их в панели редактирования Editor Pane окна Query (Запрос) анализатора запросов Query Analyzer, а также в панели SQL Pane конструктора запросов Query Designer в Enterprise Manager. Кроме того, мы использовали их косвенно применяя утилиты, которые выполняют команды Transact-SQL "за сценой". Конструктор таблиц Table Designer в Enterprise Manager, например, формирует операторы CREATE и ALTER, основываясь на заданных вами параметрах.
Общение с SQL Server
Большинство приложений баз данных используют традиционный язык программирования, такой как Microsoft Visual Basic, для создания интерактивного интерфейса с SQL Server. Используя средства интерфейса, предоставляемые языком, эти приложения представляют данные пользователям в удобной и "дружественной" форме. "За сценой" же они, тем не менее, используют команды Transact-SQL. Как Enterprise Manager, так и анализатор запросов Query Analyzer в SQL Server как раз и являются приложениями для работы с базами данных, которые выполняют эту задачу.
Когда вы используете обычные языки программирования, язык сам определяет, как исполнить команды. Некоторые окружения, например такие, как Microsoft Access, предоставляют интерактивные программные инструменты, схожие с Enterprise Manager и Query Analyzer. Другие, такие как Visual Basic или Microsoft Visual C++, используют объектную модель типа ADO для взаимодействия с сервером.
Логические операции
Как и операции сравнения, логические операции возвращают булевые значения "истина" (TRUE) или "ложь" (FALSE), но их использование ограничивается сравнением булевых значений. В таблице 24.6 представлены три логических оператора, поддерживаемых SQL Server.
| AND | TRUE, если оба значения есть TRUE. |
| NOT | Инвертирует значения булевого оператора. |
| OR | TRUE, если хотя бы один из операторов есть TRUE. |
Используйте логические операции в операторе SELECT
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем Logical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Математические функции
Математические функции, представленные в таблице 24.10, выполняют числовые вычисления.
| ABS | numeric_expression | Возвращает абсолютное значение выражения numeric_expression. |
| ACOS | float_expression | Возвращает арккосинус выражения float_expression. |
| ASIN | float_expression | Возвращает арксинус выражения float_expression. |
| ATAN | float_expression | Возвращает арктангенс выражения float_expression. |
| ATN2 | float_expression, float_expression | Возвращает угол в радианах, тангенс которого находится между двумя значениями float_expression. |
| CEILING | numeric_expression | Возвращает ближайшее число, большее или равное выражению numeric_expression. |
| COS | float_expression | Возвращает тригонометрический косинус выражения float_expression. |
| COT | float_expression | Возвращает тригонометрический котангенс выражения float_expression. |
| DEGREES | numeric_expression | Данный угол numeric_expression в радианах возвращает в градусах. |
| EXP | float_expression | Возвращает экспоненциальное значение выражения float_expression. |
| FLOOR | numeric_expression | Возвращает ближайшее число, меньшее или равное выражению numeric_expression. |
| LOG | float_expression | Возвращает натуральный логарифм выражения float_expression. |
| LOG10 | float_expression | Возвращает десятичный логарифм выражения float_expression. |
| PI | Возвращает значение константы pi. | |
| POWER | numeric_expression, y | Возвращает значение выражения numeric_expression, возведенное в степень y. |
| RADIANS | numeric_expression | Данный угол numeric_expression в градусах возвращает угол в радианах. |
| RAND | [seed] | Возвращает случайное значение в интервале от 0 до 1. |
| ROUND | numeric_expression, lenght | Возвращает округленное с указанной точностью значение выражения numeric_expression. |
| SIGN | float_expression | Возвращает +1, если numeric_expression положительно, 0 если numeric_expression ноль, и -1 если numeric_expression отрицательно. |
| SIN | float_expression | Возвращает тригонометрический синус даваемого в радианах угла float_expression. |
| SQUARE | float_expression | Возвращает квадрат float_expression. |
| SQRT | float_expression | Возвращает квадратный корень из float_expression. |
| TAN | float_expression | Возвращает тангенс выражения float_expression. |
Используйте математические функции
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем Mathematical и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Операции сравнения
В уроке 13 мы уже рассматривали использование операций сравнения при конструировании фразы WHERE. Соответствующие операторы отображены в таблице 24.5. Операторы сравнения возвращает булевые значения "истина" (TRUE) или "ложь" (FALSE).
| = | Равно |
| > | Больше |
| < | Меньше |
| >= | Больше или равно |
| <= | Меньше или равно |
| <> | Не равно |
Используйте операции сравнения в фразе CLAUSE
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем Comparison и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Зарос).
Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Зарос).
Операции Transact-SQL
Операцией (operator) мы будем называть символ, обозначающий действие, которое будет выполнено программой SQL Server. В уроке 12 уроке мы использовали операцию конкатенации + для создания вычисляемого столбца в операторе SELECT.
Операции Transact-SQL классифицируются по количеству значений, которыми они могут оперировать. Это свойство называется кардинальным числом (cardinality) операции. Операции Transact-SQL по их кардинальному числу различаются на унарные и бинарные.
Большинство операций являются бинарными. Операция называется бинарной, если она оперирует с двумя значениями. Операция + в выражении 4+3, и операция < в выражении MonthSales < MonthBudget являются примерами бинарных операций. Операция является унарной, если она оперирует только с одним значением. В выражении -10 операция (-) является унарным.
Операторы комментариев
Transact-SQL поддерживает два специальных оператора, которые не используются для операций вычисления, а предписывают SQL Server игнорировать определенный текст в сценарии. Transact-SQL поддерживает два оператора комментариев. Двойное тире (--) предписывает SQL Server игнорировать всю строку после этого символа. Этот оператор может использоваться в начале строки, в результате чего SQL Server будет игнорировать всю строку, или же он может использоваться внутри строки, в результате чего SQL Server будет игнорировать все, что находится после двойного тире до конца строки.
Другим оператором комментариев являются два оператора, /* и */, которые используются вместе. SQL Server будет игнорировать все, что находится между первым оператором комментария /* и вторым оператором комментария */, причем не важно сколько строк расположено между ними.
На рис. 24.1 показано использование оператора комментариев.
Рис. 24.1. Transact-SQL поддерживает два оператора комментариев.
Совет. Operator /* и */ полезны при временном отключении operators Transact-SQL во время отладки.
Побитные операции
Выполнение побитных операций очень схоже с выполнением логических операций, но в то время как логические операции возвращают результаты, основанные на булевых значениях, побитные операции работают на битовом уровне с числовыми значениями. Побитные операции представлены в таблице 24.7.
Побитные операторы $ и | работают так же, как и их логические собратья, за исключением того, что они сравнивают каждый бит двух значений, а не два значения целиком.
Оператор ^ не имеет соответствующего логического эквивалента. В булевой алгебре, булевое OR (ИЛИ) возвращает TRUE (истина), если хотя бы одно или оба значения есть TRUE (истина). Однако булевое исключающее OR (ИЛИ) возвращает TRUE(истина), если одно, но не оба сравниваемых значения есть TRUE (истина). То же самое делает и оператор ^, который возвращает TRUE(истина), только если одно, но не оба сравниваемых бита есть TRUE (истина).
| & | Побитное AND (И). |
| | | Побитное OR (ИЛИ). |
| ^ | Побитное исключающее OR (ИЛИ). |
| ~ | Побитное NOT. |
Битовое представление
Побитные операции выполняются над целочисленными значениями, использующими каждый бит в числе для указания отдельного свойства или атрибута – эта техника названа битовом представлением (bit packing). По определению, если одно целочисленное число используется для хранения множества свойств, значение не является скалярным и таблица не будет соответствовать нормальной форме.
При данных обстоятельствах вы должны избегать использования битового представления в соответствующей базе данных. Однако битовое представление будет незаменимо при использовании наследуемых данных, и побитные операции будут в данной ситуации весьма удобны.
Используйте побитные операции в операторе SELECT
Для открытия нового окна Query (Запрос), нажмите в панели инструментов анализатора запросов Query Analyzer кнопку New Query (Новый запрос).
Выберите файл с именем Bitwise и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Приоритет операций
Когда вы создаете составной оператор Transact-SQL, важно представлять себе порядок, в котором должны выполняться операции – их приоритет (precedence). Определение приоритета часто не представляет проблемы, но иногда незнание приоритета может ввести вас в заблуждение при работе с операциями. Например, 3*(4+1) равно 15, в то время как 3*4+1 равно 13, поскольку операция умножения выполняется первой. Операция умножения имеет наивысший приоритет.
Ниже представлены операторы в порядке их приоритетов. Операции, имеющие одинаковый приоритет, будут выполняться слева на право.
+ (положительное число), - (отрицательное число), и ~ (побитная инверсия NOT)*, /, %+ (сложения), + (конкатенации), - (вычитания)= (сравнения), >, <, >=, <=, <>^, &, |NOTANDOR= (присваивания)
Вы можете управлять порядком вычисления, используя скобки, как в предыдущем примере.
Как и команды Transact-SQL, операции будет легко изучать, если сгруппировать их по типу выполняемых ими действий.
Системные функции
Системные функции возвращают информацию об окружении SQL Server. Так же, как и функций метаданных, системных функций очень много и все они доступны через панель Object Browser. В таблице 24.15 представлены основные системные функции.
| APP_NAME | Возвращает имя приложения. | |
| DATALENGHT | выражение | Возвращает количество байт, используемых для хранения выражения. |
| ISDATE | выражение | Определяет, корректно ли данное выражение. |
| ISNULL | выражение | Определяет, является ли данное выражение нулем. |
| ISNUMERIC | выражение | Определяет, является ли данное выражение числом. |
| NEWID | Создает новый уникальный идентификационный номер uniqueidentifier. | |
| NULLIF | выражение, выражение | Возвращает NULL, если первое и второе выражение одинаковы. |
| PARSENAME | object_name, name_part | Возвращает часть имени name_part объекта object_name. |
| SYSTEM_USER | Возвращает текущее имя пользователя системы. | |
| USER_NAME | [id] | Возвращает имя текущего пользователя или пользователя по указанному id. |
Используйте системные функции
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите файл с именем System и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса нажмите в панели инструментов анализатора запросов Query Analyzer кнопку Execute Query (Выполнить запрос).
Закройте окно Query (Запрос).
Строковые функции
Строковые функции выполняют операции со строковыми значениями и возвращают либо строковые, либо числовые значения. В таблице 24.14 представлен список основных строковых функций.
| ASCII | char_expression | Возвращает ASCII-код самого левого символа в строке char_expression. |
| CHAR | integer_expression | Возвращает ASCII-символ, код которого равен integer_expression. |
| CHARINDEX | char_expression, char_expression [, start_position] | Возвращает позицию первого выражения char_expression во втором выражении char_expression. |
| LEFT | char_expression, integer_expression | Возвращает крайние слева символы integer_expression в выражении char_expression. |
| LEN | char_expression | Возвращает количество символов в выражении char_expression. |
| LOWER | char_expression | Возвращает выражение char_expression, в котором все символы приведены к нижнему регистру. |
| LTRIM | char_expression | Возвращает выражение char_expression с удаленными начальными пробелами. |
| NCHAR | integer_expression | Возвращает символ UNICODE, код которого задает integer_expression. |
| REPLACE | char_expression, char_expression, char_expression | Находит все вхождения второй строки char_expression в первую char_expression и заменяет их на третью char_expression. |
| RIGHT | char_expression, integer_expression | Возвращает крайние справа символы integer_expression в строке char_expression. |
| RTRIM | char_expression | Возвращает строку char_expression с удаленными конечными пробелами. |
| SOUNDEX | char_expression | Возвращает четырехзначный код SOUNDEX для char_expression. |
| SPACE | integer_expression | Возвращает число integer_expression пробелов. |
| SUBSTRING | char_expression start, lenght | Возвращает подстроку char_expression указанной длины lenght, начиная с символа start. |
| UNICODE | unicode_expression | Возвращает значение UNICODE для первого символа в unicode_expression. |
| UPPER | char_expression | Возвращает выражение char_expression, в котором все символы приведены к верхнему регистру. |
Используйте строковые функции
Для открытия нового окна Query (Запрос), в панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите файл с именем String и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Для выполнения запроса в панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
увеличить изображение
Закройте окно Query (Запрос).
Глобальные переменные
Глобальные переменные обозначаются двойным символом @ (@@VERSION) и предоставляются программой SQL Server. Они не могут создаваться пользователем. Большинство глобальных переменных предоставляет информацию о текущем статусе SQL Server. Они все представлены в панели Object Browser в папке Common Functions.
Переменные конфигурирования
Наиболее часто используемые переменные конфигурирования приведены в таблице 25.1. Они предоставляют информацию о текущих установках различных свойств и параметров SQL Server.
| @@CONNECTIONS | Число соединений или попыток соединения с момента последнего запуска сервера. |
| @@DATEFIRST | Возвращает число, обозначающее день недели (Monday=1, Sunday=7). |
| @@DBTS | Последнее значение колонки с отметкой времени, вставленной в базу данных. |
| @@LANGID | Идентификатор локального языка, использующегося в данный момент. |
| @@LANGUAGE | Название текущего языка. |
| @@OPTIONS | Возвращает значение текущей опции SET. |
| @@SERVERNAME | Имя локального сервера. |
| @@VERSION | Дата, версия и тип процессора текущей инсталляции. |
Статистические переменные
Статистические переменные предоставляют информацию о процессах, выполненных SQL Server с момента последнего запуска. Основные переменные показаны в таблице 25.2.
| @@CPU_BUSY | Время, потраченное процессором на работу с момента последнего запуска сервера. |
| @@IDLE | Время, бездействия SQL Server с момента последнего запуска сервера. |
| @@IO_BUSY | Время, которое потратил SQL Server на выполнение операций ввода и вывода с момента последнего запуска сервера. |
| @@TOTAL_ERRORS | Число ошибок чтения/записи диска с момента последнего запуска сервера. |
| @@TOTAL_READ | Число выполненных сервером операций чтения с диска с момента последнего запуска сервера. |
| @@TOTAL_WRITE | Число выполненных сервером операций записи на диск с момента последнего запуска сервера. |
Системные переменные
Системные переменные показаны в таблице 25.3. Они предоставляют информацию о последних операциях с таблицами, выполненных сервером.
| @@IDENTITY | Последнее значение идентификационной колонки, вставленной в базу данных. |
| @@ROWCOUNT | Количество строк, подвергшихся воздействию последнего оператора. |
Использование переменных
Переменные могут использоваться во всех выражениях языка Transact-SQL. Однако, они не используются вместо имени объекта или ключевого слова. Таким образом, представленные ниже операторы будут корректными:
DECLARE @theOil char(20) SET @theOil = 'Basil'
-- Эта команда будет выполнена SELECT OilName, Description FROM Oils WHERE OilName = @theOil Однако оба следующих оператора SELECT не будут выполнены: DECLARE @theCommand char(10), @theField char(10) SET @theCommand = 'SELECT' SET @theField = 'OilName'
-- Эта команда не будет выполнена @theCommand * FROM Oils
-- Как и эта SELECT @theField from Oils
Объявите локальную переменную
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите сценарий CreateLocalVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Используйте оператор SELECT для присвоения значений
В окне запроса выберите панель редактирования Editor Pane или откройте новое окно Query (Запрос), если вы закрыли окно, использовавшееся в предыдущем упражнении.
Выберите сценарий SelectVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Выберите сценарий UseTableVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Используйте глобальные переменные для отображения информации о сервере
В окне запроса выберите панель редактирования Editor Pane или откройте новое окно Query (Запрос), если вы закрыли окно, использовавшееся в предыдущем упражнении.
Выберите сценарий GlobalVariable и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Использование временных таблиц
Временные таблицы создаются с помощью таких же команд, как и обычные таблицы: CREATE или SELECT INTO. Предоставляемая таблица является доступной для соединения (т. е., она не считается локальной таблицей, созданной другим соединением), и она может использоваться точно так же, как и обычная таблица.
Создайте локальную временную таблицу
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос) для открытия нового окна Query (Запрос).
Выберите сценарий CreateLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
увеличить изображение
Нажмите клавишу F5 для обновления содержимого окна Object Browser и откройте папку User Tables. Query Analyzer отобразит в списке таблицу dbo.#LocalTable.
увеличить изображение
увеличить изображение
Используйте локальную временную таблицу из текущего сеанса
Выберите окно Query (Запрос), содержащее сценарий CreateLocal.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).
Выберите сценарий UseLocal и нажмите кнопку Open (Открыть). Query Analyzer откроет сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Используйте глобальную временную таблицу из текущего сеанса
Выберите окно Query (Запрос), содержащее сценарий CreateGlobal.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).
Выберите сценарий UseGlobal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Используйте локальную временную таблицу из другого сеанса
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос).
Выберите сценарий UseLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Используйте глобальную временную таблицу из другого сеанса
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Load Script (Загрузить сценарий).
Выберите сценарий UseGlobal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Закройте два открытых окна запроса Query, но одно оставьте открытым для сохранения соединения с сервером. Если будет предложено сохранить изменения, нажмите No (Нет).
Переменные
Несмотря на то, что временные таблицы в некоторых ситуациях очень полезны, при написании программ все же сложно обойтись без переменных, которые являются программными объектами, используемыми для временного хранения значений.
Понятие о переменных
Переменные обозначаются префиксом @; например @MyVariable. Как и временные таблицы, переменные имеют две области действия: локальную и глобальную. Глобальные переменные обозначаются двойным символом @: @@VERSION.
Имеется несколько различий между переменными и временными таблицами. Все глобальные переменные определяются SQL Server; вы не можете определить их самостоятельно. Кроме того, область действия локальных переменных еще уже, чем у локальных таблиц: они доступны только в пределах пакета или процедуры, в которой они объявлены.
Локальные переменные
Локальные переменные создаются с помощью оператора DECLARE, который имеет следующий синтаксис:
DECLARE @локальная_переменная тип_данных
Идентификатор локальная_переменная должен соответствовать обычным правилам, действующим для идентификаторов базы данных; тип_данных может быть любым системным типом данных, исключая text, ntext или image. С помощью одного оператора DECLARE может быть создано несколько локальных переменных. Для этого переменные нужно указывать через запятую:
DECLARE @var1 int, @var2 int
Большинство типов данных являются скалярными (scalar); то есть они содержат одно значение, такое как число или строка. Возможность объявления перемененных с табличным типом данных, является новшеством в SQL Server 2000. Синтаксис для создания переменной табличного типа показан ниже:
DECLARE @локкальная_переменная Table ({определение_таблицы})
В этом примере, определение_таблицы идентично обычному определению CREATE TABLE, за исключением того, что разрешается использование лишь следующих ограничений: PRIMARY KEY, UNIQUE KEY, NULL и CHECK.
Совет. Другим полезным типом данных для переменных является sql_variant. Переменные типа варианты могут содержать любой тип данных. Это позволяет вам назначать различные типы данных одной локальной переменной в ходе выполнения процедуры.
После создания, локальная переменная первоначально имеет значение NULL. Вы можете присвоить переменной значение следующими способами:
использовать команду SET с указанием константы или переменной:
SET @myCharVariable = 'Hello, World'использовать команду SELECT с указанием константы или переменной:
SELECT @myCharVariable = 'Hello, World'использовать команду SELECT с указанием другого оператора SELECT:
SELECT @myCharVariable = MAX (OilName) FROM Oilsиспользовать команду INSERT INTO с указанием переменной табличного типа:
INSERT INTO @myTableVariable SELECT * FROM Oils
Обратите внимание, что в третьем случае (SELECT с другим SELECT) оператор присвоения (=) замещает второе ключевое слово в SELECT; оно второй раз не повторяется. Последний пример демонстрирует синтаксис INSERT INTO...SELECT команды INSERT INTO. Вы также можете использовать синтаксис INSERT INTO...VALUES:
INSERT INTO @myTableVariable VALUES ('The Value')
Понятие о временных таблицах
Временные таблицы создаются такими же командами, с помощью которых Transact-SQL создает обычную таблицу – это CREATE или SELECT INTO. Различие состоит лишь в том, что имя временной таблицы начинается либо с #, либо с ##. Таким образом, первый оператор в представленном ниже листинге создает обычную таблицу, а второй – временную таблицу.
CREATE TABLE NormalTable (theKey INT PRIMARY KEY, theValue CHAR(20)) CREATE TABLE #TamporaryTable (theKey INT PRIMARY KEY, theValue CHAR(20))
Единственное обстоятельство, которое следует учитывать при создании временной таблицы, состоит в том, что она не может иметь ограничений внешнего ключа FOREIGN KEY. Все другие возможности оператора CREATE TABLE доступны – временные таблицы могут иметь проверочное ограничение CHECK, значение по умолчанию и любую необходимую вам структуру.
Временные таблицы всегда создаются в системной базе данных tempdb. Поскольку tempdb всегда заново создается при запуске SQL Server, временные таблицы не могут сохраняться между сеансами. Кроме того, временные таблицы всегда уничтожаются при выходе за пределы их действия (область видимости), то есть тогда, когда никто их не использует.
Область действия временной таблицы определяется ее именем. Временная таблица, имя которой содержится один значок решетки, например #MyTable, имеет локальную (local) область действия. Временная таблица, содержащая в имени два значка, например ##MyTable, имеет глобальную (global) область действия.
Локальные временные таблицы (иногда называемые личными временными таблицами) видимы только в создавшем их соединении. Никакие другие соединения (даже установленные этим же пользователем) не могут их видеть или иметь доступ к ним. Соединение, создавшее временную таблицу, может в любое время удалить ее, но если она после выхода пользователя из системы все еще существует, SQL Server завершает работу таблицы автоматически. Глобальные таблицы, создаваемые указанием в имени двойного символа решетки, доступны всем пользователям. В глобальной таблице не требуется назначения привилегий: любой пользователь получает привилегии автоматически.
Глобальные временные таблицы могут явно уничтожаться, или же SQL Server будет уничтожать их после того, как создавшее соединение закроется, и все текущее использование таблицы будет завершено. Например, если пользователь А создаст временную таблицу ##MyTable, пользователь В может выполнить команды через нее. Если пользователь А выйдет из системы, в то время как пользователь В использует таблицу ##MyTable, то любые исполняемые на данный момент команды SQL Server будут выполнены, но пользователь В не сможет больше исполнить каких-либо команд с помощью этой таблицы.
Временные таблицы
Временные таблицы походят на обычные таблицы, но существуют только тогда, когда их используют. Они автоматически удаляются Microsoft SQL Server после того, как все пользователи закончили работу с ними.
Совет. Создание временной таблицы – довольно дорогая процедура с точки зрения потребления ресурсов сервера и циклов процессора. Там, где ранее в большинстве случаев использовались временные таблицы, сейчас можно использовать табличные переменные.
CASE
В большинстве языков программирования оператор CASE является расширенной формой оператора IF, которая позволяет вам определять множество булевых выражений в одном операторе. В SQL Server CASE является функцией, а не командой. Она используется не сама по себе, как IF. а как часть оператора SELECT или UPDATE.
Операторы, включающие в себя структуру CASE, могут использовать одну из двух синтаксических форм, в зависимости от того, будет ли изменяться оцениваемое выражение. Простейшая форма в предположении, что всегда будет оцениваться булево выражение, имеет следующий вид:
значение = выражение
Значение может быть сколь угодно сложным. Вы можете использовать константу, имя столбца, или составное выражение, если необходимо. В качестве оператора сравнения всегда используется равенство. Синтаксис простой структуры CASE представлен ниже:
CASE значение WHEN выражение_один THEN результирующее_выражение_один WHEN выражение_два THEN результирующее_выражение_два . . . WHEN выражение_n THEN результирующее_выражение_n [ELSE альтернативное_результирующее_выражение] END
В этой форме функции CASE, выражение результирующее_выражение возвращается, только в том случае, если выражение, следующее за ключевым словом WHEN, логически равно указанному значению. Вы можете использовать в выражение любое количество фраз WHEN. Фраза ELSE необязательна – она выполняется, только если все фразы WHEN оцениваются как FALSE.
Сравнение одного значения с некоторым количеством других значений является типичной задачей, но иногда вам требуется большая степень гибкости. В это случае вы можете воспользоваться так называемым поисковым синтаксисом функции CASE Transact-SQL, который имеет следующий вид:
CASE WHEN булево_выражение_один THEN результирующее_выражение_один WHEN булево_выражение_два THEN результирующее_выражение_два . . . WHEN булево_выражение_n THEN результирующее_выражение_n [ELSE альтернативное_результирующее_выражение] END
В этой форме CASE вы можете указать целое булево выражение в каждой фразе WHEN вместо неявного сравнения выражения в простой форме. Учтите, что при определении истинности можно оценивать несколько булевых_выражений. Transact-SQL возвратит только первое результирующее_выражение, а затем перейдет к оператору, следующему за END.
Используйте простую структуру CASE
В окне Query (Запрос) выберите вкладку Editor (Редактор), а затем нажмите кнопку Clear Window (Очистить окно).
Выделите сценарий с именем SimpleCase и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Выделите сценарий с именем SimpleCase и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Используйте поисковую форму структуры CASE
В окне Query (Запрос) выберите вкладку Editor (Редактор), а затем нажмите кнопку Clear Window (Очистить окно).
Выделите сценарий с именем SimpleCase и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Циклы
Последняя из команд управления ходом выполнения позволяет вам выполнять оператор или блок операторов до тех пор, пока выполняется (является истинным) указанное условие.
IF...ELSE
Оператор IF, является простейшим из набора команд управления ходом выполнения. Если булево выражение, следующее за командой IF, имеет значение TRUE, то будет выполнен оператор или блок операторов, следующий за этим. Если булево выражение имеет значение FALSE, то оператор или блок операторов, следующий за этим, будет пропущен.
Необязательная команда ELSE позволяет вам задавать оператор или блок операторов, который будет выполняться, только если булево выражение имеет значение FALSE. Например, команды Transact-SQL, представляемые ниже, возвращают 'Истина', если @test имеет значение "истина", и 'Ложь', если нет.
IF @test SELECT 'Истина' ELSE SELECT 'Ложь'
Совет. Операторы IF...ELSE могут быть вложены один в другой, образуя логическую структуру, схожую с конструкцией IF...ELSEIF...ELSE, принятой в других языках программирования. Однако следует остерегаться использовать этот прием в простых случаях. Тот же эффект можно достичь путем использования оператора CASE, который мы рассмотрим далее. При этом оценить ход выполнения будет легче.
Используйте структуру IF...ELSE для управления выполнением
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку New Query (Новый запрос) для открытия нового окна Query (Запрос).
Query Analyzer отобразит диалоговое окно Open Query File (Открытие файла запроса).
В корневой директории найдите папку SQL 2000 Step by Step, выберите сценарий с именем CreateLocal и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
Совет. Этот сценарий использует команду PRINT для отображения сообщений на вкладке Messages (Сообщения) окна запроса Query. Команда PRINT полезна, когда вы впервые разрабатываете сценарий, в реальных же приложениях вы редко будете ее использовать.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить сценарий).
Выберите вкладку Messages (Сообщения). Query Analyzer отобразит результат выполнения оператора IF...ELSE.
Команда GOTO
Структуры IF...ELSE и CASE управляют порядком выполнения операторов, основываясь на результатах вычисления булевого выражения. Команда GOTO является безусловной. Она передает выполнение непосредственно к оператору, следующему после метки, которая на него указывает.
В SQL Server метки являются неисполняемыми операторами, имеющими следующий синтаксис:
имя_метки:
Имя метки должно удовлетворять правилам, принятым для идентификаторов. Сама команда GOTO имеет очень простой синтаксис:
GOTO имя_метки
Код "спагетти"
GOTO является весьма непопулярной в кругу программистов командой. По большому счету это связано с историческими причинами: ранние языки предоставляли очень мало механизмов управления ходом выполнения – обычно только структуру IF и команду GOTO. Как следствие, создаваемый код оказывался очень сложным для восприятия и анализа. Подобный код получил название "спагетти".
Применение команды GOTO, тем не менее, бывает вполне оправданным, особенно при обработке ошибок. Если вы будете аккуратно использовать эту команду, она даже сможет сделать код проще для понимания. Следует всего лишь следить, что вы использовали команду GOTO для выполнения задач, которые могут быть более просто выражены через другие команды или функции передачи управления.
Используйте команду GOTO для безусловного перехода
В окне Query (Запрос) выберите вкладку Editor (Редактор), а затем нажмите кнопку Clear Window (Очистить окно).
Выделите сценарий с именем GOTO и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Простой цикл WHILE
Простейшая форма цикла WHILE содержит булево выражение и оператор или блок операторов. Операторы будут повторяться, пока булево выражение не примет FALSE. Если при первой оценке булево выражение имеет значение FALSE, то оператор или блок операторов не будет выполняться вообще.
Используйте простой цикл WHILE
В окне Query (Запрос) выберите вкладку Editor (Редактор), а затем нажмите кнопку Clear Window (Очистить окно).
Выделите сценарий с именем WHILE и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Сложные циклы WHILE
Синтаксис оператора WHILE также позволяет вам осуществлять и более сложную логику выполнения, в отличие от простой логики в предыдущем примере. Фраза BREAK вызывает выход из цикла; выполнение продолжается с оператора, следующего за фразой END блока оператора структуры WHILE. Фраза CONTINUE возвращает выполнение на начало цикла, при этом операторы, следующие за CONTINUE в пределах блока операторов, будут пропущены. Оба оператора, BREAK и CONTINUE, обычно выполняются как условные в пределах оператора IF.
Если вам потребуется, вы можете использовать команды BREAK и CONTINUE в одном и том же операторе WHILE. Вы также можете использовать каждую команду несколько раз внутри блока операторов, хотя при этом будет исполняться только одна задача.
Используйте структуру WHILE...BREAK
В окне Query (Запрос) выберите вкладку Editor (Редактирование), а затем нажмите кнопку Clear Window (Очистить окно).
Выделите сценарий с именем WHILEBREAK и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий.
В панели инструментов анализатора запросов Query Analyzer нажмите кнопку Execute Query (Выполнить запрос).
Условное выполнение
Первая группа операторов управления ходом выполнения, которую мы рассмотрим, определяет порядок выполнения операторов на основе значения булева выражения. (Напомним, что булевы выражения работают с логическими значениями типа "истина" (TRUE) или "ложь" (FALSE)).
Характеристики курсоров
Transact-SQL поддерживает несколько различных типов курсоров. Выяснение различных характеристик каждого из курсоров является довольно утомительной задачей, но ее можно облегчить, если принять во внимание для каждого типа курсора три более или менее независимых характеристики: способность отражать изменения в исходных данных, способность осуществлять прокрутку во множестве строк, а также способность модифицировать множество строк.
Отражение изменений
Способность курсора отражать изменения в данных называется чувствительностью курсора. Предположим, что вы создали курсор для оператора:
SELECT * FROM Oils WHERE Left(OilName, 1) = 'B'
База данных Aromatherapy вернет четыре строки, как показано на рис. 27.1. Если в процессе использования вами курсора кто-либо добавит значение Description для элемента Bergamot, либо добавит строку для элемента Bayberry, что произойдет с множеством строк, на которое указывает ваш курсор?
увеличить изображение
Рис. 27.1. База данных Aromatherapy содержит четыре строки, начинающиеся с буквы В.
При создании вашего курсора могут быть независимо определены два вида чувствительности: изменения каких строк включаются во множество (членство множества) и отражение изменений в исходных строках.
Прокрутка
Второй характеристикой курсора является способность осуществления прокрутки как вперед, так и назад, либо только вперед. Здесь имеет место извечная для программирования дилемма: скорость против гибкости. Последовательные курсоры (forward-only) работают значительно быстрее, но имеют меньшую гибкость.
Обновление
Последней характеристикой, используемой для классификации курсоров, является возможность обновления строк курсором. Опять же, курсоры "только чтение" обычно более производительны, но имеют меньшую гибкость.
Использование курсоров
Использование курсоров подобно использованию локальных переменных – вы объявляете их, устанавливаете значение, а затем используете. Однако, в отличие от локальных переменных, которые автоматически уничтожаются при выходе за пределы области действия, вы должны явным образом освобождать используемые курсором строки, а затем уничтожать курсор.
Манипулирование строками с помощью курсора
Курсоры сами по себе не вызвали бы никакого интереса, если бы вы не могли осуществлять с их помощью определенные действия. Transact-SQL поддерживает три различные команды для работы с курсорами: FETCH, UPDATE и DELETE.
Команда FETCH извлекает указанную строку из множества строк курсора. В своем простейшем варианте команда FETCH имеет следующий синтаксис:
FETCH курсор_или_переменная
В этом формате записи возвращается строка в позиции курсора (текущая строка).
Используйте простую команду FETCH
Нажмите кнопку New Query (Новый запрос) в панели инструментов анализатора запросов Query Analyzer.
Перейдите к папке SQL 2000 Step by Step в корневой директории, выделите сценарий с именем SimpleCursor и нажмите кнопку Open (Открыть).Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос) в панели инструментов анализатора запросов Query Analyzer.
Совет. Возможно, вы обратили внимание, что этот сценарий выполняется дольше, чем соответствующий оператор SELECT. Дело в том, что создание и открытие курсора требует дополнительного времени. Никогда не используйте курсор, если для выполнения задачи достаточно оператора SELECT.
Команда FETCH может не только возвращать строку непосредственно, но и позволяет сохранять значения из возвращенного столбца в переменных. Чтобы сохранить результаты выполнения команды FETCH в переменной, используйте следующий синтаксис:
FETCH курсор_или_переменная INTO список_переменных
Список_переменных есть перечень разделяемых запятыми идентификаторов переменных. Перед выполнением команды FETCH вы должны объявить переменные. Список_переменных должен содержать переменную для каждого столбца, фигурирующего в операторе SELECT, который определяет курсор. Тип данных переменной должен либо совпадать, либо быть совместимым с типом данных столбца.
Модификация и удаление строк через курсоры
Если ваш курсор является модифицируемым. Изменение исходных значений во множестве курсора выполняется достаточно просто. Предусмотрена специальная форма фразы WHERE, которая поддерживает модификацию через курсор:
UPDATE таблица_или_представление SET список_для_модификации WHERE CURRENT OF курсор_или_переменная
Это называется позиционным обновлением. Transact-SQL также поддерживает позиционное удаление, которое имеет следующую форму записи:
DELETE таблица_или_представление WHERE CURRENT OF курсор_или_переменная
Выполните позиционное обновление
Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий с именем PositionedUpdate и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Мониторинг курсоров Transact-SQL
Transact-SQL предоставляет две глобальные переменные и функцию, которые помогают вам контролировать работу и состояние вашего курсора. Переменная @@CURSOR_ROWS возвращает количество строк во множестве последнего курсора, открытого в соединении. Значения, возвращаемые @@CURSOR_ROWS, представлены в таблице 27.1.
| -m | Курсор заполнен записями не полностью; множество курсора на данный момент содержит m строк. |
| -1 | Курсор является динамическим, и количество строк может варьироваться. |
| 0 | Курсор либо не открыт, а последний открытый курсор был закрыт и освобожден, либо курсор содержит нуль строк. |
| n | Количество строк в курсоре равно n. |
Переменная @@FETCH_STATUS возвращает информацию о выполнении последней команды FETCH. В таблице 27.2 представлены значения, возвращаемые переменной @@FETCH_STATUS.
| 0 | Оператор FETCH был выполнен успешно. |
| -1 | Оператор FETCH был выполнен неудачно. |
| -2 | Извлекаемая строка отсутствует. |
Наконец, Transact-SQL предоставляет функцию CURSOR_STATUS. Эта функция имеет следующий синтаксис:
CURSOR_STATUS(тип, курсор_или_переменная)
Тип может иметь значения 'local', 'global' или 'variable', а курсор_или_переменная – это идентификатор курсора или курсорной переменной, информацию о котором требуется получить. Результаты, возвращаемые функцией CURSOR_STATUS, представлены в таблице 27.3.
| 1 | Если функция вызывалась для динамического курсора, множество курсора состоит из нуля, одной или нескольких строк. Если функция вызывалась для другого типа курсора, курсор состоит по меньшей мере из одной строки. |
| 0 | Множество курсора пусто. |
| -1 | Курсор закрыт. |
| -2 | Возвращается только для курсорной переменной. Либо курсор, назначенный указанной переменной, закрыт, либо переменной не назначен какой-либо курсор. |
| -3 | Указанный курсор или курсорная переменная не существует. |
Используйте функции мониторинга курсором
Нажмите кнопку Load Script (Загрузить сценарий)
Выделите сценарий StatusFunctions и нажмите кнопку Open (Открыть). Query Analyzer загрузит сценарий в окно Query (Запрос).
Нажмите кнопку Execute Query (Выполнить запрос)
Понятие о курсорах
Microsoft SQL Server реально поддерживает два различных типа курсоров: курсоры Transact-SQL и курсоры API (курсоры программного интерфейса приложений). Курсоры API создаются внутри приложения, использующего объекты Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC или DB-Library. Каждое из этих API поддерживает несколько отличающиеся функциональные возможности и использует различный синтаксис. Здесь мы не будем подробно обсуждать курсоры API; если вы планируете использовать их, обратитесь к соответствующей документации на API и языку программирования, который вы собираетесь применить.
Курсоры Transact-SQL создаются с помощью команды DECLARE CURSOR. Как объект курсора, так и множество строк, на которое он указывает, должны существовать на сервере. Подобные курсоры называются серверными курсорами. Если вы используете серверный курсор из приложения, соединенного с SQL Server через сеть, каждая операция с курсором требует двустороннего сетевого взаимодействия. Библиотеки API-курсоров, поддерживающие серверные курсоры, поддерживают также клиентский курсор, который существует в клиентской системе и кэширует строки, которые он обрабатывает на клиенте.
Множество строк, на которое указывает курсор, определяется с помощью команды SELECT. При создании курсора Transact-SQL на команду SELECT накладываются несколько ограничений:
команда SELECT не может возвращать несколько результирующих множеств;команда SELECT не может содержать фразу INTO для создания новой таблицы;команда SELECT не может содержать фразу COMPUTE или COMPUTE BY, используемые для агрегирования результатов. (Однако, она может содержать функции агрегирования, например, AVG.)
Создание курсоров
Первым шагом в использовании курсора является его создание. Курсоры Transact-SQL создаются с помощью оператора DECLARE CURSOR.
Внимание! SQL Server поддерживает два различных метода создания курсоров: с использованием синтаксиса SQL-92 и с использованием синтаксиса Transact-SQL. Синтаксис SQL-92 соответствует стандарту ANSI, но имеет меньшие функциональные возможности, чем синтаксис Transact-SQL, который и рассматривается здесь.
Оператор DECLARE CURSOR имеет следующий синтаксис:
DECLARE имя_курсора CURSOR [видимость] [прокрутка] [тип] [блокировка] [TYPE_WARNING] FOR оператор_выборки [FOR UPDATE [OF имена_столбцов]]
Обратите внимание, что все параметры, определяющие характеристики курсора, – видимость, тип и т.д. – являются необязательными. Значениями по умолчанию для этих параметров являются сложными и могут указывать, либо не указывать способы взаимодействия с исходными записями или представлениями, а также опции работы с базой данных. Чтобы сделать восприятие оператора долее удобным, лучше явно задавать все необходимые вам параметры. При этом вы будете точно знать, что получите.
Видимость курсора определяется с помощью ключевых слов LOCAL или GLOBAL, которые имеют тот же эффект, что и ключевые слова @local_table или @@global_table при объявлении временных таблиц.
Совет. SQL Server будет закрывать и освобождать локальный курсор при выходе за пределы его области действия (видимости), но лучше всегда делать это явно.
Параметр прокрутка допускает использование ключевых слов FORWARD_ONLY и SCROLL, которые задают, соответственно, возможность перемещения только от начала к концу, или в любом направлении.
Параметр тип определяет тип создаваемого курсора. Здесь допустимы ключевые слова STATIC, KEYSET, DYNAMIC и FAST_FORWARD. Параметр типа FAST_FORWARD и параметр прокрутки FORWARD_ONLY являются взаимно исключающими.
Параметр блокировка определяет, могут ли строки модифицироваться курсором, и если да, то могут ли их модифицировать другие пользователи. Если используется ключевое слово READ_ONLY, курсор не может вносить никаких изменений в исходные данные. Тем не менее, другие пользователи могут модифицировать данные, либо это можете делать вы сами с помощью оператора UPDATE. Если в качестве параметра блокировка задано SCROLL_LOCKS, обновления могут быть выполнены только курсором. Все другие операторы UPDATE, как внутри этого же пакета, так и предоставляемые другими пользователями, выполняться не будут.
Последняя опция блокировки, OPTIMISTIC, позволяет осуществлять обновления строк как внутри курсора, так и вне его. Это наиболее гибкая опция, но при этом всегда существует вероятность, что модификация, выполненная курсором, окончится неудачей, если строка была изменена после ее чтения курсором.
Параметр TYPE_WARNING предписывает SQL Server отправлять предупреждающее сообщение клиенту, если тип курсора преобразуется от заданного к другому типу. Это возможно, если вы объявляете курсор, который не поддерживает заданный оператор SELECT.
Параметр оператор_выборки, указываемый в фразе FOR, является обязательным. Он задает строки, которые будут включены во множество курсора.
Фраза FOR UPDATE является необязательной. По умолчанию курсоры являются модифицируемыми, если не задан параметр READ_ONLY, однако и в этом случае лучше все-таки использовать эту фразу, чтобы быть уверенным в полученном результате. Вы можете использовать раздел OF имена_столбцов, чтобы указать определенные строки, для которых вы допускаете модификацию. Если вы опускаете раздел OF имена_столбцов, модификация может быть выполнена для всех столбцов, указанных в операторе SELECT.
DECLARE myCursor CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils DECLARE @myCursorVariable CURSOR SET @myCursorVariable = myCursor
Этот синтаксис полезен, если вы хотите создать переменные, которые могут быть назначены различным курсорам. Это может потребоваться, если вы создаете обобщенную процедуру для работы с различными результирующими множествами.
Вы можете объявить курсорную переменную, а затем использовать ее для непосредственного создания курсора.
DECLARE @myCursorVariable CURSOR SET @myCursorVariable = CURSOR LOCAL FAST_FORWARD FOR SELECT OilName FROM Oils
При использовании такого синтаксиса курсор не имеет идентификатора, и ссылка на него может быть осуществлена только через переменную. Подобный синтаксис больше подходит для хранимых процедур, которые мы рассмотрим в уроке 28.
Открытие курсора
Объявление курсора создает объект курсора, но не создает набор записей, которыми курсор будет манипулировать (множество курсора). Множество курсора не создается, пока вы не откроете курсор. После достаточно сложного синтаксиса оператора DECLARE CURSOR, синтаксис оператора кажется вполне прозрачным:
OPEN [GLOBAL] курсор_или_переменная
Ключевое слово GLOBAL помогает избежать конфликтов: если курсор, объявленный с ключевым словом LOCAL, и курсор, объявленный с ключевым словом GLOBAL, имеют одинаковый идентификатор, ссылки на курсор будут по умолчанию отнесены к локальному курсору, если вы не использовали ключевое слово GLOBAL. Как и в других подобных случаях, лучше явно указывать ключевое слово, если вы открываете глобальный курсор.
Закрытие курсора
Закончив использование курсора, вы должны его закрыть. Оператор CLOSE освобождает ресурсы, используемые для обслуживания множества курсора, а также освобождает все блокировки, наложенные на строки, если вы использовали параметр SCROLLOCKS в операторе DECLARE. Синтаксис команды CLOSE почти идентичен синтаксису оператора OPEN – меняется только ключевое слово:
CLOSE [GLOBAL] курсор_или_переменная
Освобождение курсора
Последним оператором в последовательности действий, связанных с созданием курсора, является оператор DEALLOCATE. Синтаксис его также прост:
DEALLOCATE [GLOBAL] курсор_или_переменная
Однако здесь есть одна тонкость: оператор DEALLOCATE удаляет идентификатор или курсорную переменную, но он не обязательно удаляет сам курсор. Сам курсор не удаляется до тех пор, пока все ссылающиеся на него идентификаторы будут либо освобождены, либо перестанут действовать (при выходе за пределы области действия). Рассмотрим следующие примеры:
-- Создание курсора DECLARE myCursor CURSOR KEYSET READ_ONLY FOR SELECT * FROM Oils -- Создание курсорной переменной DECLARE @cursorVariable CURSOR -- Создание множества записей курсора OPEN myCursor -- Назначение переменной курсору SET @cursorVariable = myCursor -- Освобождение курсора DEALLOCATE myCursor
После освобождения курсора идентификатор myCursor больше не ассоциируется с множеством курсора, но поскольку на множество курсора еще ссылается переменная @cursorVariable, курсор и множество курсора не освобождаются. Если вы явно не освободите также и курсорную переменную, курсор и множество курсора будут существовать, пока переменная не утратит свое действие.
Типы курсоров
Transact-SQL поддерживает четыре различных типа курсоров: статические, ключевые, динамические и курсоры быстрого доступа, или "пожарные" (firehose). Каждый тип курсора хранит различные данные относительно строк, на которые он указывает, кроме того, каждому типу курсора свойственны различные сочетания характеристик, рассмотренных в предыдущем разделе.
Статические курсоры
Статический курсор делает как бы моментальный снимок данных, задаваемых оператором SELECT, и хранит их в базе данных tempdb. Он "не чувствует" изменений в структуре или в значениях данных, а поскольку любые модификации будут отражены только в копии, этот курсор всегда открывается в режиме "только чтение". Статические курсоры, однако, могут быть объявлены как последовательные или как прокручиваемые.
Ключевые курсоры
Ключевой курсор копирует в базу tempdb только те столбцы, которые уникально идентифицируют каждую строку. Чтобы иметь возможность объявить ключевой курсор, каждая таблица, входящая в определение оператора SELECT, должна иметь уникальный индекс, который задает копируемый набор – ключ.
Ключевые курсоры могут быть как модифицируемыми, так и иметь режим "только для чтения". Они также могут быть прокручиваемыми или последовательными.
Членство в ключевом курсоре фиксируется на момент объявления курсора. Если в процессе открытого состояния курсора добавляется строка, удовлетворяющая условию отбора, она не будет добавлена во множество. В нашем предыдущем примере, где в качестве условия отбора использовалось LEFT(OilName, 1) = 'B', новая строка со значением поля OilName 'Bayberry' не будет добавлена к строкам, относящимся к области действия курсора.
Аналогично, если изменение вносится в строку, которая после этого не будет удовлетворять условию членства во множестве, например, замена 'Basil' на 'Kumquat', строка все же останется членом множества. Даже если строка удаляется, она по-прежнему остается членом множества, но SQL Server возвращает NULL для всех значений столбцов.
Хотя членство во множестве курсора после открытия курсора остается фиксированным, тем не менее, изменения значений данных, вносимые в исходные таблицы, находят отражение. Например, изменение значения поля Description для строки Bergamot будет возвращено курсором. Однако изменения значений множества ключей отражаются в курсорах только в том случае, если они осуществляются внутри курсора. В продолжение предыдущего примера, если значение поля OilName было изменено с 'Basil' на 'Kumquat' внутри курсора, курсор вернет 'Kumquat'. Если же изменение было внесено другим пользователем, курсор по-прежнему будет возвращать 'Basil'.
Совет. Как мы увидим в следующем разделе, создание курсора и открытие курсора являются разными операциями. Чтобы обновить содержимое ключевого курсора, можно закрыть и повторно открыть его.
Динамические курсоры
Динамический курсор ведет себя так, как если бы при каждом обращении к строке повторно выполнялся оператор SELECT. (На самом деле все происходит несколько иначе, но подобное представление позволяет лучше судить о работе динамических курсоров.) Динамические курсоры отражают изменения, связанные как с членством, так и со значениями исходных данных, независимо от того, сделаны ли эти изменения внутри курсора, либо внесены другим пользователем.
Для динамических курсоров действует одно ограничение: используемый для определения курсора оператор SELECT может содержать фразу ORDER BY только в том случае, если имеется индекс, включающий в себя столбцы, используемые в фразе ORDER BY. Если вы объявляете ключевой курсор с использованием фразы ORDER BY, не оперирующей индексом, SQL Server преобразует курсор в ключевой.
Курсоры быстрого доступа
SQL Server поддерживает специальную оптимизированную форму не прокручиваемого курсора, допускающего только чтение. Этот вид курсора объявляется с использованием ключевого слова FAST_FORWARD, и чаще всего его называют "пожарным" курсором (firehose).
"Пожарные" курсоры очень эффективны, но при их использовании имеются два важных ограничения. Во-первых, если в операторе определения SELECT курсора вы использовали столбцы с типом данных text, ntext или image, а также фразу TOP, SQL Server преобразует курсор в ключевой.
Во-вторых, если оператор SELECT, который вы использовали для определения курсора, содержит таблицы, имеющие триггеры, и таблицы, не имеющие триггеров, курсор преобразуется в статический. Триггеры представляют собой сценарии Transact-SQL, которые автоматически исполняются сервером при выполнении для таблицы операторов Data Manipulation Language (DML). Подробнее мы рассмотрим триггеры в уроке 29, сейчас же обратим внимание на следующий момент: если кто-либо добавляет триггер к одной из таблиц, используемых курсором, ваше приложение может внезапно остановить выполнение, поскольку SQL Server преобразует более быстрый курсор в менее быстрый.
