Transact-sql | авторизация пользователей

Введение

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

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

Метод авторизации доступа, используемый в SQL, относится к мандатным (mandatory) видам защиты данных. При этом подходе с каждым зарегистрированным в системе пользователем ( субъектом ) и каждым защищаемым объектом системы связывается мандат, определяющий действия, которые может выполнять данный субъект над данным объектом.

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

Следующий раздел посвящен фундаментальному в области баз данных (и не только) понятию транзакции – последовательности операций над базой данных (в общем случае включающей операции обновления базы данных), которая воспринимается системой как одна неделимая операция.

При классическом подходе к управлению транзакциями следуют принципу ACID ( Atomicy, Consistency, Isolation, Durability ).

Этому принципу следовали и разработчики языка SQL. Однако понятие транзакции выходит далеко за пределы SQL; механизмыуправления транзакциями составляют отдельную и большую исследовательскую область.

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

Похожее:  Кабинет пользователя байфлай

Приложения обычно выполняются на клиентской аппаратуре, отделенной (по крайней мере, логически) от серверной аппаратуры, на которой работает собственно СУБД. Чтобы получить доступ к базе данных, приложение должно подключиться к серверу и образовать сессию в этом подключении.

Завершение транзакций

Как мы отмечали в начале этого раздела, транзакции могут инициироваться как явным способом (с помощью оператора START TRANSACTION ), так и неявно, при выполнении первого оператора, требующего наличия контекста транзакции.

Для завершения транзакции всегда16 требуется выполнение одного из двух операторов COMMIT (фиксация транзакции) или ROLLBACK (откат транзакции), которые имеют следующий синтаксис:

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

Если требуется завершить транзакцию с аннулированием всех произведенных изменений, то нужно использовать оператор ROLLBACK ( ROLLBACK WORK ).

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

Оператор COMMIT считается безусловно выполненным только в том случае, когда сервер баз данных подтвердил это после выполнения всех действий, требуемых для фиксации транзакции.

Аварийная ситуация во время выполнения операции ROLLBACK ничем не отличается от аварийной ситуации, возникшей в процессе выполнения транзакции. В этом случае (при восстановлении базы данных) прерванная транзакция считается незафиксированной (что так и есть), и все ее изменения автоматически удаляются из состояния базы данных.

Синтаксис обоих операторов показывает, что в каждом из них может содержаться раздел AND [ NO ] CHAIN. Постараемся кратко пояснить смысл этого раздела (не вдаваясь в детали, поскольку стандарт SQL:1999 оставляет окончательное решение за реализацией).

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

Поэтому часто используется компромиссный вариант, при котором действия операторов COMMIT или ROLLBACK приводят не только к завершению текущей транзакции, но и к образованию новой транзакции18.

Именно эту возможность поддерживает раздел AND [ NO ] CHAIN операторов COMMIT и ROLLBACK.

Если такой раздел отсутствует в операторе завершения транзакции, то подразумевается наличие раздела AND NO CHAIN, и новая транзакция не образуется. Если же раздел AND CHAIN присутствует, то немедленно после завершения выполнения COMMIT или ROLLBACK текущей транзакции образуется новая транзакция, наследующая все характеристики завершенной транзакции.

Семантику раздела TO SAVEPOINT мы поясним немного позже.

Инструкция grant

Инструкция GRANT предоставляет разрешения принципалам на защищаемые объекты. Эта инструкция имеет следующий синтаксис:

Предложение ALL означает, что указанному принципалу предоставляются все применимые к указанному защищаемому объекту разрешения. В параметре permission_list указываются разрешаемые инструкции или объекты (разделенные запятыми), а в параметре class – класс или имя защищаемого объекта, для которого предоставляются разрешения.

Предложение on securable указывает защищаемый объект, на который предоставляется разрешение. В параметре prinicpal_list перечисляются все учетные записи (разделенные запятыми), которым предоставляются разрешения. Параметр principal и составляющие списка principal_list могут быть учетной записью пользователя Windows, регистрационным именем или учетной записью пользователя, сопоставленной с сертификатом, регистрационным именем, сопоставленным с асимметричным ключом, пользователем базы данных, ролью базы данных или ролью приложения.

В таблице ниже приводятся разрешения и защищаемые объекты, к которым они применяются, а также краткое описание предоставляемых каждым разрешением возможностей:

Разрешения и соответствующие защищаемые объекты
РазрешениеПрименениеОписание
SELECT

Таблицы и их столбцы, синонимы, представления и их столбцы, возвращающие табличные значения функции

Предоставляет возможность выборки (чтения) строк. Это разрешение можно ограничить одним или несколькими столбцами, перечислив требуемые столбцы. (Если список столбцов отсутствует, то разрешение применимо ко всем столбцам таблицы)

INSERT

Таблицы и их столбцы, синонимы, представления и их столбцы

Предоставляет возможность вставлять столбцы

UPDATE

Таблицы и их столбцы, синонимы, представления и их столбцы

Предоставляет возможность изменять значения столбцов. Это разрешение можно ограничить одним или несколькими столбцами, перечислив требуемые столбцы. (Если список столбцов отсутствует, то разрешение применимо ко всем столбцам таблицы)

DELETE

Таблицы и их столбцы, синонимы, представления и их столбцы

Предоставляет возможность удалять столбцы

REFERENCES

Определяемые пользователем функции (SQL и среды CLR), таблицы и их столбцы, синонимы, представления и их столбцы

Предоставляет возможность обращаться к столбцам внешнего ключа в родительской таблице, когда пользователь не имеет разрешения SELECT для этой таблицы

EXECUTE

Хранимые процедуры (SQL и среды CLR), определяемые пользователем функции (SQL и среды CLR), синонимы

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

CONTROL

Хранимые процедуры (SQL и среды CLR), определяемые пользователем функции (SQL и среды CLR), синонимы

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

ALTER

Хранимые процедуры (SQL и среды CLR), определяемые пользователем функции (SQL и среды CLR), таблицы, представления

Предоставляет возможность изменять свойства (за исключением владения) защищаемых объектов. Когда это право предоставляется применимо к области, оно также предоставляет права на выполнение инструкций ALTER, CREATE и DROP на любых защищаемых объектах в данной области

TAKE OWNERSHIP

Хранимые процедуры (SQL и среды CLR), определяемые пользователем функции (SQL и среды CLR), таблицы, представления, синонимы

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

VIEW DEFINITION

Хранимые процедуры (SQL и среды CLR), определяемые пользователем функции (SQL и среды CLR), таблицы, представления, синонимы

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

CREATE (безопасность сервера)

Нет данных

Предоставляет возможность создавать защищаемые объекты сервера

CREATE (безопасность базы данных)

Нет данных

Предоставляет возможность создавать защищаемые объекты базы данных

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

Применение инструкции GRANT показано в примерах ниже. Для начала, в следующем примере показано использование разрешения CREATE:

В этом примере пользователям Vasya и [ProfessorWebAlexandr] дается право на выполнение инструкций языка Transact-SQL CREATE TABLE и CREATE PROCEDURE. (Как можно видеть в этом примере, инструкция GRANT для разрешения CREATE не включает параметр ON.)

В примере ниже пользователю Vasya предоставляется возможность для создания определяемых пользователем функций в базе данных SampleDb:

Далее показано использование разрешения SELECT в инструкции GRANT:

Здесь пользователь Vasya получает разрешение на чтение строк из таблицы Employee. Когда разрешение дается учетной записи пользователя Windows или регистрационному имени, это разрешение распространяется только на данную учетную запись (регистрационное имя).

В примере ниже показано использование разрешения UPDATE в инструкции GRANT:

После выполнения инструкции GRANT в этом примере ниже, пользователь Vasya может модифицировать значения столбцов Id и EnterDate таблицы Works_on.

В следующем примере показано использование разрешения VIEW DEFINITION, которое предоставляет пользователям доступ для чтения метаданных:

Здесь показаны две инструкции для разрешений VIEW DEFINITION. Первая из них предоставляет пользователю Vasya разрешение на просмотр метаданных таблицы Employee базы данных SampleDb. (В предложении ON OBJECT указывается защищаемый объект базы данных. Посредством этого предложения можно предоставлять разрешения для работы с конкретными объектами, такими как таблицы, представления и хранимые процедуры.)

Благодаря иерархической структуре защищаемых объектов, защищаемый объект более высокого уровня можно использовать, чтобы расширить область действия разрешения VIEW DEFINITION (или любого другого базового разрешения). Вторая инструкция GRANT в примере предоставляет пользователю Vasya доступ к метаданным всех объектов схемы dbo базы данных SampleDb.

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

В примере ниже показано использование разрешения CONTROL:

Здесь пользователю Vasya предоставляются, по сути, все определенные права доступа к защищаемому объекту (в данном случае к базе данных SampleDb). Принципалу, которому предоставлено разрешение CONTROL для защищаемого объекта, также неявно предоставляется возможность самому предоставлять разрешения для данного объекта.

Иными словами, разрешение CONTROL включает в себя предложение WITH GRANT OPTION. Разрешение CONTROL является самым высшим разрешением, применительно ко многим защищаемым объектам базы данных. Вследствие этого, разрешение CONTROL на уровне определенной области неявно включает разрешение CONTROL для всех защищаемых объектов в этой области.

По умолчанию, если пользователь A предоставляет разрешение пользователю B, то пользователь B может использовать разрешения при выполнении инструкций языка Transact-SQL в инструкции GRANT. Предложение WITH GRANT OPTION дает пользователю B дополнительную возможность предоставлять данное разрешение другим пользователям:

В этом примере пользователю Vasya предоставляется разрешение выполнять инструкцию SELECT для выборки строк из таблицы Works_on, а также право самому предоставлять это разрешение другим пользователям базы данных SampleDb.

Обработка исключений с помощью инструкций try, catch и throw

В версиях SQL Server более ранних, чем SQL Server 2005 требовалось наличие кода для обработки ошибок после каждой инструкции Transact-SQL, которая могла бы вызвать ошибку. (Для обработки ошибок можно использовать глобальную переменную @@error.) Начиная с версии SQL Server 2005, исключения можно перехватывать для обработки с помощью инструкций TRY и CATCH. В этом разделе сначала объясняется значение понятия “исключение”, после чего обсуждается работа этих двух инструкций.

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

Роль инструкции TRY заключается в перехвате исключения. (Поскольку для реализации этого процесса обычно требуется несколько инструкций, то обычно применяется термин “блок TRY”, а не “инструкция TRY”.) Если же в блоке TRY возникает исключение, компонент системы, называющийся обработчиком исключений, доставляет это исключение для обработки другой части программы. Эта часть программы обозначается ключевым словом CATCH и поэтому называется блоком CATCH.

Обработка исключений с использованием инструкций TRY и CATCH является общим методом обработки ошибок, применяемым в современных языках программирования, таких как C# и Java.

Обработка исключений с помощью блоков TRY и CATCH предоставляет программисту множество преимуществ, включая следующие:

  • исключения предоставляют аккуратный способ определения ошибок без загромождения кода дополнительными инструкциями;

  • исключения предоставляют механизм прямой индикации ошибок, вместо использования каких-либо побочных признаков;

  • программист может видеть исключения и проверить их в процессе компиляции.

В SQL Server 2022 добавлена еще одна инструкция THROW, имеющая отношение к обработке ошибок. Эта инструкция позволяет вызвать исключение, которое улавливается в блоке обработки исключений. Иными словами, инструкция THROW – это другой механизм возврата, который работает подобно рассмотренной ранее инструкции RAISEERROR.

Использование инструкций TRY, CATCH и THROW для обработки исключений показано в примере ниже:

USE SampleDb;

BEGIN TRY
    BEGIN TRANSACTION
        insert into Employee values(11111, 'Анна', 'Самойленко','d2');
        insert into Employee values(22222, 'Игорь', 'Васин','d4');
        -- Создаем ошибку ссылочной целостности
        insert into Employee values(33333, 'Петр', 'Сидоров', 'd2');
    COMMIT TRANSACTION
    PRINT 'Транзакция выполнена'
END TRY
BEGIN CATCH
    ROLLBACK
        PRINT 'Отмена транзакции';
    THROW
END CATCH

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

Выполнение кода в примере осуществляется следующим образом. После успешного выполнения первой инструкции INSERT попытка исполнения второй инструкции вызывает ошибку нарушения ссылочной целостности. Так как все три инструкции заключены в блок TRY, возникает исключение для всего блока и обработчик исключений начинает исполнение блока CATCH.

Выполнение кода в этом блоке осуществляет откат исполнения всех инструкций в блоке TRY и выводит соответствующее сообщение. После этого инструкция THROW возвращает управление исполнением вызывающему объекту. Вследствие всего этого содержимое таблицы Employee не будет изменено.

Инструкции Transact-SQL – BEGIN TRANSACTION, COMMIT TRANSACTION и ROLLBACK начинают, фиксируют и выполняют откат транзакций соответственно. Предмет транзакций, в общем, и эти инструкции, в частности, мы будем рассматривать в одной из следующих статей.

Общие сведения

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

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

Но между индексом книги и индексом базы данных есть две существенные разницы:

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

Индексы сохраняются в дополнительных структурах базы данных, называющихся страницами индексов. Для каждой индексируемой строки имеется элемент индекса (index entry), который сохраняется на странице индексов. Каждый элемент индекса состоит из ключа индекса и указателя.

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

Индексы компонента Database Engine создаются, используя структуру данных сбалансированного дерева B . B -дерево имеет древовидную структуру, в которой все самые нижние узлы находятся на расстоянии одинакового количества уровней от вершины (корневого узла) дерева. Это свойство поддерживается даже тогда, когда в индексированный столбец добавляются или удаляются данные.

На рисунке ниже показана структура B -дерева для таблицы Employee и прямой доступ к строке в этой таблице со значением 25348 для столбца Id. (Предполагается, что таблица Employee проиндексирована по столбцу Id.) На этом рисунке можно также видеть, что B -дерево состоит из корневого узла, узлов дерева и промежуточных узлов, количество которых может быть от нуля и больше:

Поиск в этом дереве значения 25348 можно выполнить следующим образом. Начиная с корня дерева, выполняется поиск наименьшего значения ключа, большего или равного требуемому значению. Таким образом, в корневом узле таким значением будет 29346, поэтому делается переход на промежуточный узел, связанный с этим значением.

В этом узле заданным требованиям отвечает значение 28559, вследствие чего выполняется переход на узел дерева, связанный с этим значением. Этот узел и содержит искомое значение 25348. Определив требуемый индекс, мы можем извлечь его строку из таблицы данных с помощью соответствующих указателей. (Альтернативным эквивалентным подходом будет поиск меньшего или равного значения индекса.)

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

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

Поддержка авторизации доступа к данным в языке sql

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

В языке SQL (SQL:1999) предусмотрены возможности контроля доступа к разным объектам базы данных, в том числе к следующим объектам:

В совокупности в SQL:1999 может поддерживаться девять видов защиты разных объектов в соответствии со следующими возможными действиями (см. табл.18.1).

При разработке средств контроля доступа к объектам баз данных создатели SQL придерживались принципа сокрытия информации об объектах, содержащихся в схеме базы данных, от пользователей, которые лишены доступа к этим объектам.

Другими словами, если некоторый пользователь не обладает, например, привилегией на просмотр таблицы PRO, то при выполнении операцииSELECT * FROM PRO он получит такое же диагностическое сообщение, как если бы таблицаPRO не существовала.

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

Таблица
18.1.
Вид защиты и соответствующее действиеНазвание привилегииПрименимо к следующим объектам
ПросмотрSELECTТаблицы, столбцы, подпрограммы, вызываемые из SQL
ВставкаINSERTТаблицы, столбцы
МодификацияUPDATEТаблицы, столбцы
УдалениеDELETEТаблицы
СсылкаREFERENCESТаблицы, столбцы
ИспользованиеUSAGEДомены, определенные пользователями типы, наборы символов, порядки сортировки символов, преобразования
ИнициированиеTRIGGERТаблицы
ВыполнениеEXECUTEПодпрограммы, вызываемые из SQL
ПодтипизацияUNDERСтруктурные типы

Создание индексов

Индекс для таблицы создается с помощью инструкции CREATE INDEX. Эта инструкция имеет следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
    ON table_name (column1 [ASC | DESC] ,...)
        [ INCLUDE ( column_name [ ,... ] ) ]
        
[WITH
    [FILLFACTOR=n]
    [[, ] PAD_INDEX = {ON | OFF}]
    [[, ] DROP_EXISTING = {ON | OFF}]
    [[, ] SORT_IN_TEMPDB = {ON | OFF}]
    [[, ] IGNORE_DUP_KEY = {ON | OFF}]
    [[, ] ALLOW_ROW_LOCKS = {ON | OFF}]
    [[, ] ALLOW_PAGE_LOCKS = {ON | OFF}]
    [[, ] STATISTICS_NORECOMPUTE = {ON | OFF}]
    [[, ] ONLINE = {ON | OFF}]]
    [ON file_group | "default"]Соглашения по синтаксису

Параметр index_name задает имя создаваемого индекса. Индекс можно создать для одного или больше столбцов одной таблицы, обозначаемой параметром table_name. Столбец, для которого создается индекс, указывается параметром column1. Числовой суффикс этого параметра указывает на то, что индекс можно создать для нескольких столбцов таблицы. Компонент Database Engine также поддерживает создание индексов для представлений.

Можно проиндексировать любой столбец таблицы. Это означает, что столбцы, содержащие значения типа данных VARBINARY(max), BIGINT и SQL_VARIANT, также могут быть индексированы.

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

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

Параметр CLUSTERED задает кластеризованный индекс, а параметр NONCLUSTERED (применяется по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Компонент Database Engine разрешает для таблицы максимум 249 некластеризованных индексов.

Возможности компонента Database Engine были расширены, позволяя создать поддержку индексов с убывающим порядком значений столбцов. Параметр ASC после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, а параметр DESC означает убывающий порядок значений столбца индекса.

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

Чтобы по-настоящему понять полезность параметра INCLUDE, нужно понимать, что собой представляет покрывающий индекс (covering index). Если все столбцы запроса включены в индекс, то можно получить значительное повышение производительности, т.к. оптимизатор запросов может определить местонахождение всех значений столбцов по страницам индекса, не обращаясь к данным в таблице.

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

Параметр FILLFACTOR задает заполнение в процентах каждой страницы индекса во время его создания. Значение параметра FILLFACTOR можно установить в диапазоне от 1 до 100. При значении n=100 каждая страница индекса заполняется на 100%, т.е. существующая страница узла так же, как страница, не относящаяся к узлу, не будет иметь свободного места для вставки новых строк.

При значении параметра FILLFACTOR между 1 и 99 страницы узлов создаваемой структуры индекса будут содержать свободное место. Чем больше значение n, тем меньше свободного места в страницах узлов индекса. Например, при значении n=60 каждая страница узлов индекса будет иметь 40% свободного места для вставки строк индекса в дальнейшем.

(Строки индекса вставляются посредством инструкции INSERT или UPDATE.) Таким образом, значение n=60 будет разумным для таблиц, данные которых подвергаются довольно частым изменениям. При значениях параметра FILLFACTOR между 1 и 99 промежуточные страницы индекса содержат свободное место для одной записи каждая.

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

Параметр PAD_INDEX тесно связан с параметром FILLFACTOR. Параметр FILLFACTOR в основном задает объем свободного пространства в процентах от общего объема страниц узлов индекса. А параметр PAD_INDEX указывает, что значение параметра FILLFACTOR применяется как к страницам индекса, так и к страницам данных в индексе.

Параметр DROP_EXISTING позволяет повысить производительность при воспроизведении кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Более подробную информацию смотрите далее в разделе “Пересоздание индекса”.

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

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

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

Когда параметр ALLOW_ROW_LOCKS активирован (имеет значение on), система применяет блокировку строк. Подобным образом, когда активирован параметр ALLOW_PAGE_LOCKS, система применяет блокировку страниц при параллельном доступе.

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

Параметр ON создает указанный индекс или на файловой группе по умолчанию (значение default), или на указанной файловой группе (значение file_group).

В примере ниже показано создание некластеризованного индекса для столбца Id таблицы Employee:

Создание однозначного составного индекса показано в примере ниже:

В этом примере значения в каждом столбце должны быть однозначными. При создании индекса заполняется 80% пространства каждой страницы узлов индекса.

Создание однозначного индекса для столбца невозможно, если этот столбец содержит повторяющиеся значения. Такой индекс можно создать лишь в том случае, если каждое значение (включая значение NULL) встречается в столбце только один раз. Кроме этого, любая попытка вставить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнута системой в случае дублирования значения.

Создание представления

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

Инструкция CREATE VIEW должна быть единственной инструкцией пакета. (Это означает, что эту инструкцию следует отделять от других инструкций группы посредством инструкции GO.)

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

Параметр select_statement задает инструкция SELECT, которая извлекает строки и столбцы из таблиц (или других представлений). Параметр WITH ENCRYPTION задает шифрование инструкции SELECT, повышая таким образом уровень безопасности системы баз данных.

Предложение SCHEMABINDING привязывает представление к схеме таблицы, по которой оно создается. Когда это предложение указывается, имена объектов баз данных в инструкции SELECT должны состоять из двух частей, т.е. в виде schema.db_object, где schema – владелец, а db_object может быть таблицей, представлением или определяемой пользователем функцией.

Любая попытка модифицировать структуру представлений или таблиц, на которые ссылается созданное таким образом представление, будет неудачной. Чтобы такие таблицы или представления можно было модифицировать (инструкцией ALTER) или удалять (инструкцией DROP), нужно удалить это представление или убрать из него предложение SCHEMABINDING.

Когда при создании представления указывается параметр VIEW_METADATA, все его столбцы можно обновлять (за исключением столбцов с типом данных timestamp), если представление имеет триггеры INSERT или UPDATE INSTEAD OF.

Инструкция SELECT в представлении не может содержать предложение ORDER BY или параметр INTO. Кроме этого, по временным таблицам нельзя выполнять запросы.

Представления можно использовать для разных целей:

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

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

  • Для ограничения вставляемых или обновляемых значений некоторым диапазоном.

В примере ниже показано создание представления:

Запрос в этом примере выбирает из таблицы Works_on строки, удовлетворяющие условию Job=’Консультант’. Представление view_Consultant определяется строками и столбцами, возвращаемыми этим запросом. На рисунке ниже отображена таблица Works_on, в которой строки, выбранные в представлении view_Consultant, выделены красным цветом:

Запрос в этом примере задает выборку строк, т.е. он создает горизонтальное подмножество базовой таблицы Works_on. Возможно также создание представления с ограничениями на включаемые в него столбцы и строки. Создание такого представления показано в примере ниже:

Запрос в этом примере выбирает для включения в представление view_WithoutBudget все столбцы таблицы Project, за исключением столбца Budget.

Как уже упоминалось ранее, в общем формате инструкции CREATE VIEW не обязательно указывать имена столбцов представления. Однако, с другой стороны, в приведенных далее двух случаях обязательно требуется явно указывать имена столбцов:

  • если столбец представления создается из выражения или агрегатной функции;

  • если два или больше столбцов представления имеют одинаковое имя в базовой таблице.

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

Здесь имена столбцов представления view_Count должны быть указаны явно по той причине, что инструкция SELECT содержит агрегатную функцию count(*), которая требует, чтобы все столбцы представления были именованы.

Не требуется явно указывать список столбцов в инструкции CREATE VIEW, если применить заголовки столбцов, как это показано в примере ниже:

Представление можно создать из другого представления, как показано в примере:

Представление view_project_p2 в примере ниже создается из представления view_Consultant. Все запросы, использующие представление view_project_p2, преобразовываются в эквивалентные запросы к базовой таблице Works_on.

Представления можно также создавать посредством среды Management Studio. Для этого выберите в обозревателе объектов базу данных, в которой требуется создать представление, щелкните в ней правой кнопкой мыши узел Views и в открывшемся контекстном меню выберите пункт New View. Откроется редактор представлений, в котором можно выполнять следующие действия:

  • выбрать базовые таблицы и строки в этих таблицах для создания представления;

  • присвоить представлению имя и определить условия в предложении WHERE соответствующего запроса.

Транзакции и ограничения целостности

Материал этого подраздела уже излагался в лекции 13, но там это делалось в контексте определений ограничений целостности. Для полноты картины мы воспроизведем часть этого материала в контексте управления транзакциями.

Итак, любое ограничение целостности обладает атрибутом, определяющим время проверки данного ограничения. Этот атрибут может иметь значения DEFERRABLE (отложенная проверка) или NOT DEFERRABLE (немедленная проверка).

Чтобы данное ограничение целостности могло когда-либо обладать свойством отложенной проверки, нужно, чтобы в определении такого ограничения присутствовали ключевые слова INITIALLY DEFERRED или INITIALLY IMMEDIATE.

В любом случае, в каждый момент времени выполнения транзакции любое ограничение целостности находится в одном из двух состояний – отложенная проверка или немедленная проверка. Если начальным состоянием ограничения является INITIALLY DEFERRED, то в начале любой транзакции его текущим состоянием будет отложенная проверка. Аналогично для ограничений с начальным состоянием INITIALLY IMMEDIATE.

Любое ограничение, находящееся в состоянии немедленной проверки, всегда проверяется в конце выполнения любого оператора SQL19. Немедленно проверяются и те ограничения, которые были определены как NOT DEFERRABLE, но для которых впоследствии был установлен режим немедленной проверки.

Однако если текущим состоянием ограничения является отложенная проверка, оно будет проверяться только тогда, когда перейдет в состояние немедленной проверки. Это делается неявно при выполнении оператора COMMIT или явно при выполнении оператора SET CONSTRAINTS. Этот оператор имеет следующий синтаксис:

Ключевое слово ALL является сокращенной формой задания списка имен всех ограничений целостности, определенных в базе данных, которые специфицированы с указанием ключевого слова DEFERRABLE.

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

Если какое-либо из ограничений нарушается, то операция COMMIT трактуется как операция ROLLBACK, и пользователю (или приложению) сообщается, что возникла ошибка.

Избежать этой неприятной ситуации можно явным выполнением оператора SET CONSTRAINTS ALL IMMEDIATE до фиксации транзакции, для которой имеются DEFERRABLE ограничения, текущим режимом которых является отложенная проверка.

Установление соединений

Начиная со стандарта SQL/92, при разработке языковых средств стала приниматься во внимание клиент-серверная организация СУБД. Если говорить более точно, стал очевиден тот факт, что во всех существующих СУБД до начала работы приложения со средствами управления базой данных требуется выполнить некоторые предварительные инициирующие действия.

В частности, необходимо создать контекст, в котором будет работать система баз данных. В некоторых реализациях этот контекст создается автоматически при запуске приложения, поскольку клиентская часть СУБД компонуется к приложению. В других случаях прикладная программа связывается с СУБД за счет наличия специализированных реализационно зависимых средств подключения к СУБД. Иногда контекст формируется на основе состояния системных переменных.

Очевидно, что для выработки языковых средств, которые не противоречили бы существующим реализациям, требовался компромисс. Этот компромисс выразился в том, что в SQL:1999 допускается установление связи приложения с СУБД по умолчанию, а также обеспечиваются средства явного управления соединениями. Общий подход состоит в следующем.

  • Почти все операторы SQL (с небольшим числом исключений) могут выполняться только при наличии подключения клиентской части СУБД к серверу базы данных.
  • Если соединение с сервером установлено и приложение пытается выполнить один из операторов SQL (для выполнения которых требуется соединение), то его выполняет та СУБД, с которой установлено соединение.
  • Если приложение пытается выполнить один из операторов SQL (для выполнения которых требуется соединение), а соединение не установлено, то, прежде всего, требуется установить соединение. В SQL:1999 указывается, что такое соединение является соединением с СУБД по умолчанию. Что собой представляет это умолчание, определяется в реализации. После установления соединения упомянутый оператор SQL выполняется той СУБД, с которой установлено соединение.
  • Если первым (до установки соединения ) выполняемым оператором SQL является оператор CONNECT (это одно из исключений), то соединение по умолчанию не устанавливается, а происходит обращение к запрашиваемому серверу, и соединение устанавливается именно с ним.
  • Можно выполнять оператор CONNECT для установления соединений со вторым, третьим и т. д. серверами, не разрывая ранее установленные соединения. Каждое вновь установленное соединение называется текущим соединением ( current connection ), а все ранее установленные соединенияотложенными соединениями ( dormant connection ).
  • С каждым соединением ассоциирована сессия. Сессия, ассоциированная с текущим соединением, называется текущей сессией ( current session ), а сессии, ассоциированные с отложенными соединениями, называются отложенными сессиями ( dormant session )21
  • Если у приложения имеется несколько соединений, можно переключать их с помощью оператора SET CONNECTION.
  • Для поддержания установленных соединений могут расходоваться значительные системные ресурсы. Поэтому может возникнуть потребность в ликвидации соединения. Это можно сделать с помощью оператора DISCONNECT. Все соединения, не ликвидированные явно до завершения работы приложения, ликвидируются системой автоматически. Попытка ликвидировать текущее соединение, в котором выполняется транзакция, расценивается как ошибка.
  • В реализации определяется, можно ли переключать соединения во время выполнения транзакции. Однако если реализация это допускает, то, в соответствии со стандартом, все операторы, выполняемые в одной транзакции, но в разных соединениях, являются частью одной общей транзакции.

Хранимые процедуры и среда clr

SQL Server поддерживает общеязыковую среду выполнения CLR (Common Language Runtime), которая позволяет разрабатывать различные объекты баз данных (хранимые процедуры, определяемые пользователем функции, триггеры, определяемые пользователем статистические функции и пользовательские типы данных), применяя языки C# и Visual Basic. Среда CLR также позволяет выполнять эти объекты, используя систему общей среды выполнения.

Среда CLR разрешается и запрещается посредством опции clr_enabled системной процедуры sp_configure, которая запускается на выполнение инструкцией RECONFIGURE. В примере ниже показано, как можно с помощью системной процедуры sp_configure разрешить использование среды CLR:

Для создания, компилирования и сохранения процедуры с помощью среды CLR требуется выполнить следующую последовательность шагов в указанном порядке:

  1. Создать хранимую процедуру на языке C# или Visual Basic, а затем скомпилировать ее, используя соответствующий компилятор.

  2. Используя инструкцию CREATE ASSEMBLY, создать соответствующий выполняемый файл.

  3. Сохранить процедуру в виде объекта сервера, используя инструкцию CREATE PROCEDURE.

  4. Выполнить процедуру, используя инструкцию EXECUTE.

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

Сначала создайте требуемую программу в какой-либо среде разработки, например Visual Studio. Скомпилируйте готовую программу в объектный код, используя компилятор C# или Visual Basic. Этот код сохраняется в файле динамической библиотеки (.dll), который служит источником для инструкции CREATE ASSEMBLY, создающей промежуточный выполняемый код.

В примере ниже показан исходный код хранимой процедуры на языке C#:

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

Далее определяется класс StoredProcedures, для которого применяется атрибут SqlProcedure, который информирует компилятор о том, что этот класс является хранимой процедурой. Внутри кода класса определяется метод CountEmployees(). Соединение с системой баз данных устанавливается посредством экземпляра класса SqlConnection.

В следующем фрагменте кода:

используется инструкция SELECT для подсчета количества строк в таблице Employee и отображения результата. Текст команды указывается, присваивая свойству CommandText переменной cmd экземпляр, возвращаемый методом CreateCommand(). Далее вызывается метод ExecuteScalar() экземпляра SqlCommand.

Теперь вы можете скомпилировать этот код, используя среду Visual Studio. Я добавил этот класс в проект с именем CLRStoredProcedures, поэтому Visual Studio скомпилирует одноименную сборку с расширением *.dll. В примере ниже показан следующий шаг в создании хранимой процедуры: создание выполняемого кода.

Инструкция CREATE ASSEMBLY принимает в качестве ввода управляемый код и создает соответствующий объект, для которого можно создавать хранимые процедуры среды CLR, определяемые пользователем функции и триггеры. Эта инструкция имеет следующий синтаксис:

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

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

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

Чтобы сохранить информацию о коде сборке, пользователь должен иметь возможность выполнить инструкцию CREATE ASSEMBLY. Владельцем сборки является пользователь (или роль), исполняющий эту инструкцию. Владельцем сборки можно сделать другого пользователя, используя предложение AUTHORIZATION инструкции CREATE SCHEMA.

Компонент Database Engine также поддерживает инструкции ALTER ASSEMBLY и DROP ASSEMBLY. Инструкция ALTER ASSEMBLY используется для обновления сборки до последней версии. Эта инструкция также добавляет или удаляет файлы, связанные с соответствующей сборкой. Инструкция DROP ASSEMBLY удаляет указанную сборку и все связанные с ней файлы из текущей базы данных.

В примере ниже показано создание хранимой процедуры на основе управляемого кода, реализованного ранее:

Инструкция CREATE PROCEDURE в примере отличается от такой же инструкции в примерах ранее тем, что она содержит параметр EXTERNAL NAME. Этот параметр указывает, что код создается средой CLR. Имя в этом предложении состоит из трех частей:

assembly_name.class_name.method_name

где:

  • assembly_name – указывает имя сборки;

  • class_name – указывает имя общего класса;

  • method_name – необязательная часть, указывает имя метода, который задается внутри класса.

Выполнение процедуры CountEmployees показано в примере ниже:

Инструкция PRINT возвращает текущее количество строк в таблице Employee.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *