Системные процедуры также переписаны с использованием новых системных представлений.
Вообще с доступностью метаданных все стало гораздо строже. Теперь даже просмотреть можно только те метаданные, на которые есть соответствующие права.
Например, если создать простенькую процедуру в тестовой базе:
| CREATE PROCEDURE tst_sel ASSELECT * FROM employee GO |
а потом подключиться к этой базе под ранее созданным логином Vasya и попытаться найти эту процедуру в метаданных, то попытка успехом не увенчается. Вот такой запрос:
| SELECT * FROM sys.procedures WHERE name='tst_sel' |
который в принципе должен был вернуть запись с информацией о только что созданной процедуре, не вернет ни одной записи. Если же опять вернуться к предыдущему подключению и дать пользователю Vasya права на исполнение процедуры tst_sel:
| GRANT EXECUTE ON tst_sel TO vasya |
а потом повторить запрос под все тем же логином Vasya, то информация о процедуре будет доступна. Однако если теперь пользователь Vasya захочет просмотреть текст процедуры tst_sel, у него ничего не получится. Вот такой запрос, который, в принципе, позволяет увидеть тексты процедур и функций, выполненный из подключения Vasya:
| SELECT definition FROM sys.sql_modules WHERE name = 'tst_sel' |
не вернет ни одной записи. В то же время, если его выполнить из подключения, в котором процедура создавалась, то ее код вполне можно просмотреть.
Подобные нововведения в безопасность метаданных, безусловно, очень полезны, но вполне могут привести к проблемам при переносе старых приложений, в которых предполагается, что для просмотра доступны практически все метаданные, вне зависимости от прав.
Чтобы избежать этих проблем, а также для большей гибкости при настройке прав просмотра метаданных, в Yukon добавлено новое право – VIEW DEFINITION. Это право перекрывает правила, описанные выше. Если предоставить пользователю Vasya право VIEW DEFINITION на объект, то ему будут доступны для просмотра все метаданные этого объекта, не взирая на остальные права, если же это право явно запретить, то никакие метаданные посмотреть уже будет нельзя, опять-таки не взирая на остальные права.
Права VIEW DEFINITION могут быть применены к объектам, расположенным на разных уровнях иерархии сервера.
| -- НауровнебазыданныхGRANT VIEW DEFINITION TO <principal> [WITH GRANT OPTION];-- НауровнесхемыGRANT VIEW DEFINITION ON SCHEMA :: <schema> TO <principal> [WITH GRANT OPTION];-- На уровне определенного объекта схемыGRANT VIEW DEFINITION ON <object> TO <principal> [WITH GRANT OPTION];-- Здесь<principal> ::= <user> | <role> | PUBLIC<object> ::= <table name> | <view name> | <function name> | <procedure name> | ... |
Таким образом, если сейчас разрешить пользователю Vasya VIEW DEFINITION науровнебазы:
| GRANT VIEW DEFINITION TO Vasya |
то ему будет доступен для просмотра текст тестовой процедуры, как и всех остальных метаданных, имеющих отношение к тестовой базе. Если же явно запретить ему работу с метаданными в базе:
| DENY VIEW DEFINITION TO Vasya |
то мало того, что под этим логином нельзя будет просмотреть ни запись о наличии процедуры, ни, тем более, ее текст – вообще никакие метаданные не будут доступны. Например, попытка посмотреть, что за объекты в принципе есть в базе, даст очень любопытный результат.
| SELECT * FROM sysobjects-- илиSELECT * FROM sys.objects |
Ни один из этих запросов не вернет ни одной записи.
Вернуть первоначальное положение вещей можно, удалив это правило.
| REVOKE VIEW DEFINITION TO Vasya |
Сами по себе метаданные могут быть организованы в некоторую иерархию. Например, представление tables унаследовано от objects, это означает, что помимо столбцов, содержащих информацию, относящуюся исключительно к таблицам, tables содержит также все столбцы, входящие в objects.
Индексы – это внутренний механизм сервера, позволяющий кардинально повысить скорость выполнения запросов, и без них производительность реляционных БД была бы удручающе низка. В новой версии Mcrosoft SQL Server разработчики не обошли вниманием столь ответственный участок, и в механику индексирования были внесены, некоторые усовершенствования. Естественно, изменился немного и синтаксис команды создания индекса, теперь он выглядит так:
| CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON [ { database_name . [ schema_name ] . | schema_name . } ] {table_or_view_name} ( column [ ASC | DESC ] [ ,...n ] ) [ INCLUDE (column_name [ ,...n ] ) ] [ WITH ( <relational_index_option> [ ,...n ] ) ] [ ON {partition_scheme_name ( column_name [,...n]) | filegroup_name |default } ] |
А дополнительные настройки таковы:
| <relational_index_option> ::= { PAD_INDEX = {ON | OFF}| FILLFACTOR = fillfactor| SORT_IN_TEMPDB = {ON | OFF}| IGNORE_DUP_KEY = {ON | OFF}| STATISTICS_NORECOMPUTE = {ON | OFF}| DROP_EXISTING = {ON | OFF}| ONLINE = {ON | OFF}| ALLOW_ROW_LOCKS = {ON | OFF}| ALLOW_PAGE_LOCKS = {ON | OFF}| MAXDOP = number_of_processors} |
Прежде всего стоит обратить внимание на то, что изменился синтаксис указания дополнительных настроек. Теперь рекомендуется параметры ON или OFF указывать в обязательном порядке, а старый синтаксис, без ON/OFF, поддерживается лишь из соображений обратной совместимости. В будущих версиях от его поддержки обещают отказаться. При этом новые команды поддерживают только синтаксис с ON/OFF. Так же недопустимо смешивать два различных синтаксиса в одном операторе, например попытка создания индекса с опциями WITH (DROP_EXISTING, ONLINE = ON ) – вызовет ошибку.
Ряд настроек индекса, которые ранее задавались с помощью специальной хранимой процедуры, теперь можно указать напрямую, при создании или изменении индекса. ALLOW_ROW_LOCKS разрешает или запрещает блокировку индекса на уровне строк, а ALLOW_PAGE_LOCKS – на уровне страниц данных.
MAXDOP (max degree of parallelism) – максимальное количество процессоров, используемых при построении плана выполнения запроса. В предыдущих версиях задать этот параметр напрямую при работе с индексами было нельзя – использовались настройки для всей системы, задаваемые через системную хранимую процедуру sp_configure. Теперь же этот параметр можно указать отдельно для каждого индекса. Здесь имеется в виду количество процессоров, которое будет использоваться непосредственно при создании или изменении индекса, а не при последующей работе с ним. Что называется, «пустячок, а приятно» ;)
В команде создания индекса появился параметр “INCLUDE”. Он позволяет задействовать новую, достаточно полезную функциональность, но для того, чтобы показать, что это такое, лучше начать немного издалека.
В Microsoft SQL Server индекс представляет собой B+tree, узлы которого состоят из ключевых полей, а в листьях (узлах самого последнего уровня) содержатся ссылки на записи таблицы.
Индекс может быть двух типов, кластерный (clustered) и не кластерный.
Кластерный индекс отличается от некластерного тем, что в листьях этого индекса содержатся не ссылки на записи в таблице, а сами записи. Таким образом, при наличии кластерного индекса записи в таблице выстраиваются в порядке ключей такого индекса (строго говоря это не совсем так, но в первом приближении верно). По очевидным причинам кластерный индекс может быть только один на таблицу. Идентификация конкретной записи в таблице также находится в прямой зависимости от наличия кластерного индекса. Если кластерного индекса нет, то запись находится по уникальному идентификатору записи – RID, который однозначно определяет ее положение в файле данных. Если же кластерный индекс в таблице присутствует, то физическое место этой записи не постоянно, а, следовательно, использовать RID не очень практично, поскольку его пришлось бы обновлять во всех индексах при каждом изменении. Поэтому запись идентифицируется по ключу кластерного индекса.
Допустим, необходимо выбрать поле одной из записей в таблице по какому-нибудь условию. В этом случае, в зависимости от наличия индексов, сервер может поступить несколькими способами.
Поле, которое надо выбрать, совпадает с полем, по которому нужно осуществить поиск, и по этому полю построен индекс.
| SELECT LastName FROM employees WHERE LastName = 'Callahan' |
План такого запроса прост и незатейлив:
| |--Index Seek(OBJECT:([Employees].[LastName]), SEEK:([Employees].[LastName]=Convert([@1])) ORDERED FORWARD) |
Нужное значение просто находится по индексу.
Поле, которое необходимо просмотреть, не совпадает с полем, по которому нужно искать, но при этом по полю поиска построен кластерный индекс.
| SELECT LastName FROM Employees WHERE EmployeeID = 8 |
План такого запроса также не отличается излишней сложностью:
| |--Clustered Index Seek(OBJECT:([Employees].[PK_Employees]), SEEK:([Employees].[EmployeeID]=Convert([@1])) ORDERED FORWARD) |
Все закономерно – идет поиск по кластерному индексу, а затем извлекается нужное поле из этого индекса.
Поле, которое надо достать из таблицы, не совпадает с полем, по которому производится поиск, и по полю поиска построен самый обычный индекс.
| SELECT LastName FROM Employees WHERE PostalCode = '98105' |
Вот здесь уже серверу приходится совершать дополнительные телодвижения, и план запроса немного усложняется:
| |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Employees])) |--Index Seek(OBJECT:([Employees].[PostalCode]), SEEK:([Employees].[PostalCode]=Convert([@1])) ORDERED FORWARD) |
Сначала по индексу находится нужная запись, а точнее, не запись, а ссылка на запись. При этом значения поля, которое на самом деле надо достать из таблицы, по-прежнему нет, так как поле поиска не является нужным полем, как это было в первом случае. На данный момент у сервера, как уже говорилось, есть только ссылка, и чтобы извлечь нужное поле, надо выполнить еще одну операцию – bookmark lookup. Стоимость этой операции в некоторых случаях может быть очень высока, например, в этом запросе она составляет половину всей его стоимости (49%). В случае с кластерным индексом такого не происходит, потому что в листьях кластерного индекса содержится вся запись, а значит, ничего искать уже не надо.