авторефераты диссертаций БЕСПЛАТНАЯ БИБЛИОТЕКА РОССИИ

КОНФЕРЕНЦИИ, КНИГИ, ПОСОБИЯ, НАУЧНЫЕ ИЗДАНИЯ

<< ГЛАВНАЯ
АГРОИНЖЕНЕРИЯ
АСТРОНОМИЯ
БЕЗОПАСНОСТЬ
БИОЛОГИЯ
ЗЕМЛЯ
ИНФОРМАТИКА
ИСКУССТВОВЕДЕНИЕ
ИСТОРИЯ
КУЛЬТУРОЛОГИЯ
МАШИНОСТРОЕНИЕ
МЕДИЦИНА
МЕТАЛЛУРГИЯ
МЕХАНИКА
ПЕДАГОГИКА
ПОЛИТИКА
ПРИБОРОСТРОЕНИЕ
ПРОДОВОЛЬСТВИЕ
ПСИХОЛОГИЯ
РАДИОТЕХНИКА
СЕЛЬСКОЕ ХОЗЯЙСТВО
СОЦИОЛОГИЯ
СТРОИТЕЛЬСТВО
ТЕХНИЧЕСКИЕ НАУКИ
ТРАНСПОРТ
ФАРМАЦЕВТИКА
ФИЗИКА
ФИЗИОЛОГИЯ
ФИЛОЛОГИЯ
ФИЛОСОФИЯ
ХИМИЯ
ЭКОНОМИКА
ЭЛЕКТРОТЕХНИКА
ЭНЕРГЕТИКА
ЮРИСПРУДЕНЦИЯ
ЯЗЫКОЗНАНИЕ
РАЗНОЕ
КОНТАКТЫ


Pages:     | 1 |   ...   | 25 | 26 || 28 | 29 |   ...   | 33 |

«Е.Мамаев MS SQL SERVER 2000 Книга посвящена одной из самых мощных и популярных современных систем управления базами данных - Microsoft SQL Server 2000. ...»

-- [ Страница 27 ] --

с Замечание Как видно, на рисунке приведены системные таблицы. Если они вам мешают, то можно предписать Enterprise Manager не отображать системные объекты. Для этого следует в окне свойств регистрации сервера сбросить флажок Show system data bases and objects.

Рис. 21.2. Папка Tables Часть IV. Разработка и сопровождение баз данных Создание таблицы Первая операция, которую выполняет пользователь в отношении таблицы (если конечно не считать этапа разработки), — это ее создание.

Создание таблицы средствами Enterprise Manager выполняется с помощью окна New Table (рис. 21.3), для открытия которого достаточно в контекстном меню папки Tables выбрать команду New Table.

с Замечание На рис. 21.3 приведено уже заполненное окно New Table. Однако в только что от крытом окне нет никакой информации, и все строки будут пусты.

Рис. 21.3. Окно создания новой таблицы New Table Окно New Table разделено на две части. С помощью верхней части формируется набор столбцов, из которых будет состоять таблица, а также указываются их ос новополагающие свойства. Самая верхняя строка соответствует первому столбцу таблицы, вторая строка — второму столбцу и т. д. Порядок перечисления столб цов очень важен. При вставке и выборке данных без указания столбов сервер будет обрабатывать значения именно в той последовательности, в которой они Глава 21. Работа с таблицами были перечислены при создании таблицы. Рассмотрим назначение колонок, с помощью которых указываются базовые характеристики столбцов:

• Column Name. В этой колонке задается имя, которое будет иметь столбец.

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

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

• Length. Указывается размер типа данных, выбранного для столбца. Некото рые типы данных (такие как char, nvarchar, binary и другие) позволяют из менять значение в этой колонке, тем самым предоставляя возможность управлять количеством памяти, выделяемой для столбца. Другие же типы данных всегда имеют один и тот же размер (например, i n t, datetime, money и т. д.) и для них изменять значения в колонке Length нельзя.

• Allow Nulls. Установка флажка в этой колонке разрешает для соответствую щего столбца хранение значений NULL. Отметим, однако, что если столбец входит в первичный ключ или имеет тип данных timestamp, то хранение значений NULL ДЛЯ него разрешить нельзя.

С помощью верхней части можно также определить первичный ключ таблицы.

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

На рис. 21.3 первичным ключом является столбец е ю.

Мы рассмотрели работу с верхней частью окна New Table. Теперь же обратимся к нижней части, содержимое которой зависит от того, какой столбец выбран в верхней части. В нижней части окна выполняется управление дополнительными свойствами столбца. Рассмотрим элементы управления, находящиеся в распо ряжении пользователя:

• Description. Это текстовое поле предназначено для ввода краткого описания столбца, которое не используется системой.

• Default Value. Значение, которое будет присваиваться столбцу по умолчанию.

• Precision. В этом поле указывается максимальное количество цифр (в т. ч. и после запятой), которое можно хранить в столбце. Доступно только для неце лочисленных типов данных с фиксированной точностью, также называемых десятичными (decimal). К этим типам данных относятся numeric и decimal.

Для нецелочисленных (или приблизительных) типов данных с фиксированной точностью, также называемых приблизительными (approximately), таких как Q84 Часть IV. Разработка и сопровождение баз данных r e a l и float, значение в этом поле (24 и 53 соответственно) доступно только для чтения. Аналогичная ситуация и с целочисленными типами данных (tinyint, smallint, int И bigint).

• Scale. Используется совместно с предыдущим полем и предназначено для указания количества цифр после запятой, которое будет хранить десятичный тип данных. Значение в этом поле не может превышать величины, указанной в предыдущем поле.

• Identity. Если в данном раскрывающемся списке выбрано значение Yes, то соответствующий столбец будет сконфигурирован как столбец-счетчик. Тогда становятся доступными два следующих поля.

• Identity Seed. В этом поле указывается начальное значение, которое будет использовано для автоматического генерирования значений для столбца счетчика.

П Identity Increment. С помощью этого поля можно указать величину, на кото рую станет увеличиваться значение в столбце-счетчике при вставке новой строки.

• Is RowGuid Этот раскрывающийся список, доступный только для типа дан ных uniqueidentif i e r, содержит всего два значения — Yes и No. При выбо ре первого из них столбец конфигурируется в качестве уникального глобаль ного идентификатора строки, что соответствует указанию ключевого слова ROWGUIDCOL при описании столбца в команде CREATE TABLE. Автоматически в поле Default Value подставляется значение newid (). Таким образом, при до бавлении в таблицу новой строки в соответствующий столбец будет автома тически помещаться уникальное значение. Напомним, что только один стол бец в таблице может быть сконфигурирован как уникальный глобальный идентификатор строки. Тем не менее, можно создавать множество столбцов с типом данных uniqueidentif i e r и значением по умолчанию newid ().

• Formula. В данном поле можно ввести формулу, с помощью которой будет формироваться значение столбца. Таким образом, соответствующий столбец будет являться вычисляемым столбцом (computed columns). Формула может включать ссылки на столбцы, функции, а также константы, которые связы ваются в одно выражение с помощью различных операторов. Для вычисляе мых столбцов помимо формулы разрешается указывать только имя столбца.

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

П Collation. Поле доступно только для типов данных, предназначенных для хранения символьных и текстовых данных, т. к. с помощью него указывается сопоставление, которое будет использоваться для столбца. По умолчанию предлагается применять то же сопоставление, что было выбрано для базы данных, в которой создается таблица (значение database default). Для вы бора иного сопоставления следует нажать кнопку л^Л расположенную непо средственно справа от поля Collation. После нажатия кнопки откроется окно выбора сопоставления, подобное приведенному на рис. 21.4.

Глава 21. Работа с таблицами После того, как будут сконфигурирова ны параметры всех столбцов, необходи мо сохранить сконфигурированную таб лицу. Для этого достаточно нажать кнопку Save, расположенную в панели инстру ментов. При этом будет выведено окно Choose Name, с помощью которого следу ет ввести имя, которое будет присвоено сконфигурированной таблице. Затем мож но закрыть окно создания таблицы. В принципе, Enterprise Manager автоматиче ски предложит сохранить таблицу, открыв при этом окно Choose Name.

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

Общие свойства таблицы Если в предыдущем разделе рассматривалось в основном определение отдельных столбцов таблицы, то этот и несколько следующих разделов будут посвящены рас смотрению работы со свойствами самой таблицы. Для управления свойствами таблицы существует окно Properties (рис. 21.5), открыть которое можно с помо щью кнопки Table and Index Properties, расположенной на панели инструментов.

с Замечание Окно Properties используется не только при создании таблицы, но и при работе с диаграммами. Этим объясняется наличие в нем элементов управления, на первый взгляд ненужных. Примером является раскрывающийся список Selected table, по зволяющий выбрать таблицу, свойства которой необходимо редактировать. В мо мент создания таблицы указанный список содержит только один элемент, тогда как при работе с диаграммами в списке будет находиться множество значений.

Как видно из рисунка, окно Properties имеет четыре вкладки. Первая из этих вкладок (см. рис. 21.5) имеет название Tables и предназначена для управления базовыми свойствами таблицы. Рассмотрим элементы управления, доступные на этой вкладке:

• Selected table. Теоретически этот раскрывающийся список предназначен для выбора таблицы, со свойствами которой предполагается работать. Однако на момент создания в этом списке доступно только имя формируемой таблицы.

Часть IV. Разработка и сопровождение баз данных Рис. 21.5. Окно Properties, вкладка Tables П Owner. С помощью этого раскрывающегося списка можно выбрать пользова теля (или роль) базы данных, которому будет принадлежать создаваемая таб лица. Напомним, что рядовой пользователь может в качестве владельца соз даваемой таблицы указать только себя.

• Table name. Если в списке Selected table указывается текущее имя таблицы, то с помощью рассматриваемого поля можно задать новое имя, которое бу дет присвоено таблице.

• Table Identity Column. С помощью данного раскрывающегося списка можно выбрать столбец, который будет сконфигурирован в качестве столбца-счет чика. По умолчанию в этом элементе управления указывается имя столбца, сконфигурированного в качестве столбца-счетчика при определении списка столбцов. Тем не менее, можно выбрать любой из столбцов, имеющих цело численный тип данных.

• Table ROWGUID Column. В этом раскрывающемся списке перечислены все столбцы, имеющие тип данных u n i q u e i d e n t i f i e r. Выбирая тот или иной столбец в списке, можно тем самым сконфигурировать его в качестве уни кального глобального идентификатора строки.

П Table Filegroup. Данный раскрывающийся список содержит перечень всех групп файлов, определенных в базе данных. Выбирая ту или иную группу, Глава 21. Работа с таблицами вы тем самым предписывается размещать обычные данные (помимо дан ных типа t e x t, n t e x t и image) в указанной группе файлов. По умолчанию предлагается располагать в группе файлов по умолчанию (в данном случае PRIMARY).

• Text FHegroup. С помощью этого списка можно выбрать группу файлов, в которой будут располагаться данные столбцов с типом данных t e x t, ntext и image.

О Description. Текстовое поле предназначено для указания краткого коммента рия к создаваемой таблице. Его содержимое на работу сервера и работу с таблицей никакого влияния не оказывает.

На этом работа с вкладкой Tables заканчивается. Перейдем же к рассмотрению следующей вкладки.

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

Рис. 21.6. Окно Properties, вкладка Relationships 988 Часть IV. Разработка и сопровождение баз данных Рассмотрим элементы управления, доступные на вкладке:

П Table name. В этом поле указывается имя таблицы, свойства которой редак тируются.

П New. Нажав эту кнопку, можно создать новый внешний ключ, который в дальнейшем будем называть отношением.

• Delete. С помощью этой кнопки можно удалить отношение, выбранное в списке Selected relationship.

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

• Relationship name. Это текстовое поле содержит имя, присвоенное отноше нию при создании. Меняя значение в этом текстовом поле, можно переиме новать отношение.

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

• Foreign key table. В данном же поле указывается имя таблицы, которая в от ношении рассматривается в качестве зависимой. Предполагается, что в каче стве зависимой будет указано имя таблицы, свойства которой редактируются.

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

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

• Check existing data on creation. Установка этого флажка предписывает выпол нить проверку данных, имеющихся в таблице, на соответствие устанавливае мому ограничению целостности. При сброшенном флажке такая проверка осуществляться не станет, и вполне возможна ситуация, что в таблице будут иметься данные, не соответствующие наложенным ограничениям целостно сти.

• Enforce relationship for replication. Сброс этого флажка эквивалентен указанию ключевого слова NOT FOR REPLICATION при создании таблицы с помощью команды CREATE TABLE. Напомним, что в этом случае система не будет при менять ограничение целостности для данных, вставляемых или изменяемых подсистемой репликации. ^ • Enforce relationship for INSERTS and UPDATES. Установка данного флажка предписывает выполнять проверку при удалении или изменении данных в Глава 21. Работа с таблицами главной таблице на наличие связи между этими данными и данными в зави симой таблице. При этом становятся доступными два флажка:

Cascade Update Related Fields — в случае установки этого флажка сервер • будет отображать изменения первичного ключа главной таблицы на свя занные значения внешнего ключа зависимой таблицы. Если же флажок сброшен, то сервер будет отменять выполнение изменений первичного ключа, требуя предварительного изменения данных в зависимой таблице.

Cascade Delete Related Records — установка этого же флажка предписыва • ет при удалении строки главной таблицы выполнять удаление связанных строк в зависимой таблице. При сброшенном флажке сервер будет отме нять выполнение удаления строки в главной таблице.

На этом рассмотрение работы с вкладкой Relationships можно считать оконченным.

Управление индексами Следующей вкладкой окна свойств Properties, которую мы рассмотрим, будет вкладка Indexes/Keys (рис. 21.7), предназначенная для управления индексами, определенными для таблицы. С помощью этой вкладки также можно управлять некоторыми свойствами первичного ключа таблицы.

Рис. 21.7. Окно Properties, вкладка Indexes/Keys ддО Часть IV. Разработка и сопровождение баз данных В верхней части вкладки, как и для предыдущих вкладок, в поле Table name вы водится имя таблицы, свойствами которой осуществляется управление. Ниже расположен раскрывающийся список Selected index, в котором перечислены все индексы, определенные для таблицы, а также ограничения целостности Unique и Primary Key. Содержимое нижней части вкладки отображает свойства выбран ного объекта.

Для создания нового индекса или ограничения целостности Unique достаточно нажать кнопку New. Отметим, что создать первичный ключ средствами вкладки Indexes/Keys нельзя. Тем не менее, с помощью кнопки Delete можно удалить из таблицы первичный ключ, а также индекс или ограничение целостности Unique.

В поле Туре указывается тип объекта, выбранного в списке Selected index. До пускаются следующие значения:

• Index — индекс;

• Primary key — первичный ключ;

• Unique — ограничение целостности Unique, обеспечивающее уникальность данных.

Для изменения имени индекса (ограничения целостности) можно воспользо ваться полем Index name. Это же поле позволяет указать имя создаваемого объ екта. В центральной части вкладки имеется таблица, с помощью которой необ ходимо выбрать столбцы, предполагаемые к включению в индекс или огра ничение целостности Unique или Primary Key. Как видно, таблица имеет две колонки — Column name и Order, элемент каждой из них представляет раскры вающийся список. С помощью первой колонки определяется набор столбцов, которые будут входить в индекс или ограничение целостности. С помощью же второй колонки выбирается используемый для индекса тип сортировки.

При помощи раскрывающегося списка Index Filegroup можно указать группу файлов, в которой будут размещаться данные соответствующего индекса. Фла жок Create UNIQUE доступен только в момент создания индекса. Его установка свидетельствует о желании обеспечить уникальность данных для выбранных в таблице столбцов. Отметим, что уникальность данных может быть обеспечена одним из двух методов:

• Constraint — при установке переключателя в это положение уникальность данных будет обеспечена при помощи ограничения целостности Unique;

О Index — в этом случае будет создан уникальный индекс.

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

Глава 21. Работа с таблицами В правой части вкладки имеется поле Fill factor, с помощью которого можно указать фактор заполнения, используемый для создаваемого индекса. Ниже на ходится флажок Pat Index, установка которого предписывает серверу резервиро вать место во внутренних структурах индекса для добавления новых страниц.

Нерассмотренными остались только два флажка в нижней части вкладки:

П Create as CLUSTERED. Установка флажка предписывает серверу создать кластерный индекс. Особенностью индексов этого типа является то, что фи зический порядок строк в таблице перестраивается в соответствии с индек сом. В каждой таблице может быть создан только один кластерный индекс.

О Do not automatically recompute statistics. Когда этот флажок установлен, то сервер не станет производить автоматического обновления статистики для индекса.

На этом рассмотрение вкладки Indexes/Keys можно закончить.

Контроль значений Итак, нерассмотренной осталась только вкладка Check Constraints (рис. 21.8), с помощью которой можно управлять ограничениями целостности Check — соз давать их, удалять и изменять.

Рис. 21.6. Окно Properties, вкладка Check Constraints дд2 Часть IV. Разработка и сопровождение баз данных _^_^ Верхняя часть вкладки в большей части повторяет содержимое предыдущих вкладок. Поэтому мы сразу перейдем к рассмотрению центральной и нижней части. Как видно из рисунка, центральную часть вкладки занимает поле Con straint expression. В этом поле указывается логическое условие, которое и будет применяться для проверки значений. Допускается использование имен столб цов, функций, констант, а также различных операторов.

В нижней же части вкладки расположены три флажка:

• Check existing data on creation. Установка этого флажка предписывает серверу выполнить проверку существующих данных на соответствие накладываемому проверочному условию.

• Enforce constraint for replication. При сброшенном флажке созданное ограни чение целостности не используется при осуществлении операций, выпол няемых подсистемой репликации, что эквивалентно указанию параметра NOT FOR REPLICATION при создании ограничения целостности Check средствами КОМаНДЫ CREATE TABLE.

• Enforce constraint for INSERTS and UPDATES. Сброс этого флажка позволяет временно отключить ограничение целостности, не прибегая к его удалению.

По умолчанию флажок установлен и проверка выполняется.

На этом рассмотрение работы с ограничениями целостности Check, а также и в общем со свойствами таблицы можно считать оконченным.

Изменение таблицы Изменение таблицы средствами Enterprise Manager мало чем отличается от про цесса создания таблицы. Единственно, в силе остаются все ограничения, свя занные с модификацией структуры таблиц в одном из предыдущих разделов при описании изменения таблиц средствами Transact-SQL. Для изменения структуры таблицы достаточно в ее контекстном меню выбрать команду Design Table, по сле чего откроется окно, в целом весьма похожее на окно New Table, работа с которым была рассмотрена в предыдущих разделах.

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

В окне также имеется кнопка Permissions, с помощью которой можно открыть окно управления правами доступа пользователей к таблице. \ Глава 21. Работа с таблицами Рис. 21.9. Окно свойств таблицы Удаление таблицы Для удаления таблицы средствами Enterprise Manager достаточно выбрать нуж ную таблицу и нажать либо клавишу Delete, либо кнопку Delete на панели инструментов. Однако прежде чем производить эту операцию, не будет лишним посмотреть, не ссылаются ли на удаляемую таблицу другие объекты базы дан ных, такие как представления, функции или процедуры. Для получения этой информации можно воспользоваться окном Dependencies for (рис. 2I.10). От крыть это окно можно с помощью контекстного меню интересующей таблицы, выбрав в нем команду All Tasks, а затем команду View Dependencies.

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

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

Основную же часть окна занимает две таблицы — Objects that depend on 'objectname' n Objects that 'objectname' depends on1. В первой таблице приведен список объектов, которые ссылаются на выбранный объект. Во второй же таб ' Вместо 'objectname' в обеих таблицах, как и в заголовке окна будет указано имя интере сующего объекта. — Ред.

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

Рис. 21.10. Окно просмотра зависимостей объектов Глава Использование представлений При работе с таблицами иногда бывает необходимо скрыть от пользователей один или более столбцов с конфиденциальными данными. Например, таблица может содержать данные о фамилии, имени и отчестве служащего, дату его ро ждения, семейное положение, пол, национальность и размер оклада. Необходи мо разрешить всем пользователям просматривать всю информацию о сотрудни ках кроме размера оклада.

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

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

При создании запроса указывается запрос SELECT, который будет формировать содержимое представления. В этом запросе могут применяться все разделы ко манды SELECT. To есть пользователь может выполнять объединение Coin) и слия ние (union) данных, различные выражения, группировку, агрегирование и логи ческие условия.

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

В простейшем случае представление является полной копией данных одной таб лицы. В этом случае запрос, на основе которого создается представление, вы глядит как SELECT * FROM table_name. Более сложные представления созда ются на основе множества таблиц. Так как запрос SELECT позволяет обращаться к представлениям, то представление также способно строиться на основе других 996 Часть IV. Разработка и сопровождение баз данных представлений, которые, в свою очередь, могут быть созданы на основе таблиц или других представлений. Максимальная вложенность представлений не долж на превышать 32 уровней.

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

Также нельзя создать временное представление.

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

Создание представления Создание представлений в SQL Server 2000 можно выполнить различными ме тодами:

• с помощью Enterprise Manager;

• с помощью мастера Create View Wizard;

П средствами Transact-SQL.

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

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

Использование Transact-SQL Для создания представлений с помощью Transact-SQL служит команда CREATE VIEW, имеющая следующий синтаксис:

CREATE VIEW [ d a t a b a s e _ n a m e. ] [ o w n e r. ] v i e w _ n a m e [(column [,... n ] ) ] [WITH v i e w _ a t t r i b u t e [,... n ]].

AS s e l e c t s t a t e m e n t [WITH CHECK OPTION] Глава 22. Использование представлений Рассмотрим назначение и использование параметров команды:

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

О owner Имя пользователя базы данных, которому будет принадлежать создаваемое представление. Если параметр опускается, то представление будет принадле жать текущему пользователю.

d view_name Этот параметр определяет имя, которое будет присвоено представлению. При выборе имени представления необходимо следовать стандартным правилам именования объектов. Перед созданием представления следует убедиться, что в базе данных нет таблицы, или представления с таким же именем. Не разре шается начинать имя представления с символов # или ##, т. к. SQL Server 2000 не поддерживает создание временных представлений.

П (column [,...п]) С помощью этого параметра указываются имена, которые будут присвоены столбцам представления. Количество элементов параметра (column [,...п]) должно соответствовать количеству столбцов, возвращаемых запросом SELECT, на основе которого строится представление. Явное указание имен для столбцов представления необходимо, если запрос SELECT возвращает столбцы, построенные на основе функций, констант и других выражений, а также когда в результате объединения нескольких таблиц имеется несколько столбцов с одинаковыми именами. Параметр (column [,...п]) также может быть использован просто для изменения имен столбцов, возвращенных за просом. Если этот параметр не задан, то имена столбцов представления будут соответствовать именам столбцов, возвращенных запросом.

) ( Замечание Имена столбцам можно присвоить непосредственно в запросе SELECT. В этом слу чае применение параметра (column [,... п ] ) не требуется.

view_attribute Посредством этой конструкции указываются дополнительные атрибуты для создаваемого представления. Конструкция имеет следующий синтаксис:

v i e w _ a t t r i b u t e : : = {ENCRYPTION | SCHEMABINDING I VIEW_METADATA} Рассмотрим назначение каждого из параметров:

• WITH ENCRYPTION. При использовании этой опции сервер будет выполнять шифрование кода, используемого для создания представления. Этот код 998 Часть IV. Разработка и сопровождение баз данных хранится в системной таблице syscomments, имеющейся в каждой базе данных. Шифрование обычно требуется, когда необходимо скрыть от пользователя информацию о том, на основе каких таблиц было создано представление.

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

Типичным примером такого изменения является удаление (или переиме нование) из таблицы столбца, к которому обращается представление. При использовании параметра SCHEMABINDING требуется указывать не только имена объектов, но и имена владельцев.

• VIEWMETADATA. Данный параметр применяется при работе с технологиями DBLIB, ODBC и OLE DB API и предписывает серверу при запросе мета данных возвращать не метаданные об исходных таблицах, на основе кото рых построено представление, а непосредственно о самом представлении.

П AS select_statement После ключевого слова указывается запрос SELECT, на основе которого соб ственно и будет формироваться содержимое представления. Пользователь, создающий представление, должен иметь права доступа ко всем объектам, к которым выполняется обращение в запросе. В запросе разрешается обраще ние более чем к одной таблице или представлению. Для построения столбца могут быть использованы различные выражения, включающие имена столб цов, константы, функции. Однако не допускается применение переменных.

Кроме того, имеются еще несколько ограничений:

• строки, возвращаемые запросом, должны быть не отсортированными, т. е.

запрещается использование раздела ORDER BY;

• не допускается указание раздела COMPUTE, С ПОМОЩЬЮ которого вычисля ются значения функций агрегирования по столбцу;

• запрещается использование раздела INTO, С ПОМОЩЬЮ которого на основе результата запроса создается новая таблица;

• не разрешена ссылка на временные таблицы.

• WITH CHECK OPTION Использование этой опции при создании представления гарантирует, что для строк представления не будет разрешено выполнение изменений, которые могут привести к исчезновению строки из представления. Например, если в запросе SELECT, на основе которого создано представление, в разделе WHERE используется условие price=20, то сервер будет запрещать выполнение че рез представление изменений, которые устанавливают для столбца p r i c e значения меньше 20.

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

Эта таблица содержит список всех объектов, созданных в базе данных. Код ко манды, с помощью которой было создано представление, записывается в табли цу syscomments. Как часть этого кода сохраняется и запрос SELECT, С ПОМОЩЬЮ которого формируется содержимое представления. Если при создании представ ления применяется опция WITH ENCRYPTION, TO сохраняемые в таблице syscom ments данные представления будут шифроваться. В таблице syscoiumns будет сохранен список всех столбцов, определенных в представлении. Информация о таблицах и представлениях, на основе которых построено представление, сохра няется в таблице sysdepends, что позволяет отслеживать зависимости между объектами базы данных.

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

CREATE VIEW union_view AS SELECT title_id, title=CAST(title as char(30))+ CASE WHEN LEN(title)30 THEN '...' ELSE " END, value=price*ytd_sales, type='psychology FROM titles_psychology UNION SELECT title_id, title=CAST(title as char (30)) + CASE WHEN LEN(title)30 THEN '...' ELSE '' END, value=price*ytd_sales, type='business' FROM titles_business Теперь просмотрим, что же будет содержаться в представлении:

SELECT * F O union_view RM Будет возвращен следующий результат:

title id title value type BU1032 The Busy Executive's Database... 81859.0500 business BU1111 Cooking with Computers: Surrep... 46318.2000 business BU2075 You Can Combat Computer Stress... 55978.7800 business BU7832 Straight Talk About Computers 81859.0500 business PS3333 Prolonged Data Deprivation: Fo... 81399.2800 psychology PS1372 Computer Phobic AND Non-Phobic... 8096.2500 psychology PS2091 Is Anger the Enemy? 22392.7500 psychology PS7777 Emotional Security: A N e w A l g o... 26654.6400 psychology PS2106 Life Without Fear 777.0000 psychology (9 row(s) affected) Использование Enterprise Manager Предыдущий раздел был посвящен рассмотрению создания представления сред ствами Transact-SQL. В этом же разделе обсудим создание представлений сред ствами Enterprise Manager.

Часть IV. Разработка и сопровождение баз данных При работе с Enterprise Manager список представлений, имеющихся в базе дан ных, находится в папке Views (рис. 22.1). Как видно из рисунка, для каждого представления указывается его имя (столбец Name), владелец (столбец Owner), тип — системный или пользовательский (столбец Туре), а также дата создания (столбец Create Date).

Рис. 22.1. Папка Views Для создания нового представления достаточно выбрать в контекстном меню папки Views команду New View или нажать кнопку New на панели инструмен тов. В ответ откроется New View (рис. 22.2), с помощью которого и создается представление.

Это окно вертикально разделено на четыре части, каждая из которых имеет свое имя:

П Diagram Pane. В этой области, расположенной в верхней части окна, указы ваются все таблицы, представления и функции, которые предполагается ис пользовать для построения представления. Для добавления в область новой таблицы, представления или функции следует нажать кнопку Add table в па нели инструментов. После этого откроется окно Add Table, с помощью кото рого и можно добавить объект в область Diagram Pane. Окно Add Table со Глава 22. Использование представлений держит три вкладки — Tables, Views, Functions, — на которых, соответствен но, перечислены все имеющиеся в базе данных таблицы, представления и функции.

Рис. 22.2. Окно New View С помощью этой области можно разрешить или запретить включать те или иные столбцы в представление, определить связь между столбцами исходных таблиц, указать порядок сортировки, а также выполнить некоторые другие действия. Каждая таблица или представление отображается в виде самостоя тельного объекта, имеющего список столбцов, определенных в соответст вующей таблице или представлении. Установка флажка слева от имени столбца приведет к включению этого столбца в создаваемое представление (включение в раздел SELECT). В контекстном меню столбца можно выбрать тип сортировки. Если выбрать пункт Sort Ascending, то результат выборки будет отсортирован по возрастанию, если же в контекстном меню выбрать пункт Sort Descending, то сортировка будет выполняться по убыванию. Опре деление сортировки по столбцу влечет за собой включение имени соответст вующего столбца в раздел ORDER BY создаваемого запроса SELECT, на основе которого и будет создано представление. Помимо всего прочего, можно оп 1002 Часть IV. Разработка и сопровождение баз данных ределить отношения между столбцами двух таблиц. Для создания такого от ношения следует нажать левую кнопку мыши на имени столбца и, не отпус кая кнопки мыши, переместить курсор к имени столбца, с которым необхо димо установить отношение. Отношения между таблицами определяются с помощью раздела JOIN запроса SELECT. Изменения, выполняемые в области Diagram Pane, немедленно отображаются в областях Grid Pane и SQL Pane.

• Grid Pane. Эта область является второй сверху, расположенной непосредст венно ниже области Diagram Pane. Область Grid Pane позволяет выполнять частично те же задачи, что и предыдущая область, только в другой форме.

Тем не менее, включить таблицу или представление в создаваемое представ ление можно только с помощью области Diagram Pane. Для включения столбца в представление при работе с областью Diagram Pane необходимо было установить флажок слева от его имени, тогда как при работе с обла стью Grid Pane имя нужного столбца должно быть выбрано в колонке Col umn. Элемент этой колонки представляет собой раскрывающийся список, в котором перечислены имена столбцов всех таблиц, имеющихся в области Diagram Pane. Выбрав имя столбца в этом раскрывающемся списке, вы тем самым включаете его в запрос. Однако при этом еще не ясно, какую роль станет играть столбец — будет просто включен в результат выборки, будет служить критерием сортировки или использоваться для указания условия с целью ограничения диапазона строк, которые включаются в результат вы борки. В колонке Alias можно указать псевдоним, который будет использо ваться для соответствующего столбца в представлении. Таким образом, с по мощью псевдонима можно выполнять изменение имени столбца. В колонке Table указывается имя таблицы (или представления), к которой принадлежит столбец. Хотя при выборе имени столбца в колонке Column имя столбца приводится с указанием имени таблицы, впоследствии в колонке Column вы водится только имя собственно столбца, тогда как имя таблицы отображается в колонке Table. В колонке Output имеется флажок, установка которого по зволяет включить соответствующий столбец в представление. Это действие аналогично установке флажка слева от имени столбца при работе с областью Diagram Pane. Напомним, что это влечет к включению имени соответствую щего столбца в раздел SELECT создаваемого запроса. С помощью колонки Sort Type можно выбрать тип сортировки, если предполагается использовать соответствующий столбец для определения критерия сортировки. В распоря жении пользователя имеется всего два пункта — Ascending (по возрастанию) и Descending (по убыванию). При конфигурировании сортировки, помимо выбора ее типа, необходимо также указать очередность сортировки по столб цу. Очередность сортировки задается в колонке Sort Order. Если конфигури руемый столбец должен использоваться для ограничения количества строк, которые нужно включить в результат выборки, то для этого можно восполь зоваться колонкой Criteria, где необходимо указать условие, с которым будет сравниваться значение соответствующего столбца каждой строки. Если это условие выполняется, то строка станет участвовать в дальнейших операциях выборки. В противном случае она исключается и далее нигде не фигурирует.

Можно указать более одного условия, воспользовавшись колонками Or. Од Глава 22. Использование представлений нако следует учитывать, что условия, вводимые в отдельные колонки, объе диняются с помощью оператора OR. Таким образом, строка будет отобра жаться в представлении только в том случае, если выполняется условие хоть в одной колонке. Для создания условий, использующих оператор AND, необ ходимо использовать единственную колонку и во вводимом в ней значении указывать оператор AND.

• SQL Pane. Эта область содержит текст запроса SELECT, созданного с помо щью описанных выше областей. Внесение изменений в любую из двух пре дыдущих областей немедленно отображается на коде, указанном в области SQL Pane. Например, определение порядка сортировки приводит к форми рованию или изменению раздела ORDER BY, тогда как указание или измене ние условий выборки приводит к созданию или модификации раздела WHERE.

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

( Замечание ) Подробно написание запроса SELECT будет рассмотрено в главе 29.

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

После того как представление окажется сформированным, остается только сохра нить его. Для этого необходимо нажать кнопку Save на панели инструментов.

В ответ откроется окно Save As (рис. 22.3), с помощью которого нужно указать имя Рис. 22.3. Окно Save As представления. При выборе имени пред ставления необходимо придерживаться стандартных правил именования объектов. После нажатия кнопки ОК представ ление будет сохранено и появится в папке Views базы данных.

Модификацию созданного представления можно осуществлять двумя способа ми — визуально и на уровне кода Transact-SQL. В первом случае изменение представления практические ничем не будет отличаться от его создания. Для редактирования представления в этом режиме следует открыть папку Views, найти имя нужного представления и в его контекстном меню выбрать команду Design View. В ответ откроется окно, практически ничем не отличающееся от рассмотренного окна New View.

Если же требуется изменить представление на уровне команды CREATE VIEW, С помощью которой и было создано представление, то достаточно в контекстном Часть IV. Разработай сопровождение баз данных меню представления выбрать команду Properties или просто щелкнуть на нем. В ответ появится окно свойств представления View Properties (рис. 22.4), в кото ром и будет приведен код команды CREATE VIEW. ВНОСЯ изменения в приведен ный в окне код, можно тем или иным образом изменить представление. Нажав кнопку Check Syntax можно проверить правильность синтаксиса введенного кода. При этом также выполняется проверка имен объектов, на которые ссыла ется представление.

Рис. 22.4. Окно View Properties Нажав же в окне View Properties кнопку Permissions можно открыть окно Object -Properties (рис. 22,5), с помощью которого легко управлять правами доступа пользователей к представлению.

Для просмотра зависимостей между представлением и другими объектами базы данных следует в контекстном меню представления выбрать команду АИ Tasks, a затем команду View Dependencies, после чего откроется окно Dependencies for, в котором и будет отображена информация о зависимостях. Помимо всего прочего, с помощью Enterprise Manager можно просмотреть и изменить данные представ ления. Для этого следует опять воспользоваться контекстным меню представле ния, выбрав в нем команду Open View, а затем команду Return All Rows.

Глава 22. Использование представлений Рис. 22.5. Окно Object Properties Использование мастера Create View Wizard Итак, к настоящему моменту мы уже обсудили создание представлений средст вами Transact-SQL и Enterprise Manager. Осталось рассмотреть использование мастера Create View Wizard. Хотя этот способ создания представления и являет ся самым простым, тем не менее, предоставляемые им возможности весьма ог раничены. Мастер может применяться только для построения простых пред ставлений, не использующих сложные конструкции. Недостатком мастера является невозможность построения представления на основе других представ лений и пользовательских функций. Более того, мастер не допускает создания отношений между таблицами в графической форме, как это позволяет делать Enterprise Manager.

Запустить этот мастер можно с помощью окна Select Wizard, открыть которое можно, нажав в панели инструментов Enterprise Manager кнопку,. Run a Wizard.

Нужный нам мастер находится в папке Databases.

Первое окно мастера традиционно содержит перечень действий^ которые дол жен выполнить пользователь для создания представления. Никаких элементов, управления, непосредственно связанных с созданием представления, вркце нет.

Поэтому можно смело переходить к следующему окну.

Часть IV. Разработка и сопровождение баз данных Второе окно мастера называется Select Database (рис. 22.6) и предназначено для выбора базы данных, в контексте которой будет создаваться представление. Вы бранная базы данных станет определять список таблиц, на основе которых можно будет создавать представление — мастер работает только с таблицами, расположенными в указанной базе данных. Создаваемое представление будет размещено в выбранной базе данных.

Рис. 22.6. Окно Select Database мастера Create View Wizard Следующее окно мастера имеет имя Select Objects (рис. 22.7). В нем из элемен тов управления содержится только таблица, предназначенная для выбора объек тов, на основе которых будет создаваться представление (т. е. формирование раздела FROM запроса SELECT). В колонке Table Name указываются имена всех пользовательских таблиц, имеющихся в базе данных. Имя владельца таблицы приводится в столбце Table Owner. Для включения же таблицы в представление достаточно установить флажок в колонке Include in View.

После того, как все объекты будут выбраны, можно переходить к окну мастера Select Columns (рис. 22.8), где указываются столбцы выбранных в предыдущем окне таблиц, которые станут отображаться в представлении. В первой колонке приводится трехчастное имя столбца, включающее помимо имени собственно столбца имя таблицы и ее владельца. В колонке Data Type указывается тип дан ных, который имеет соответствующий столбец. Установка же флажка в колонке Select Column предписывает включить столбец в представление (^раздел SELECT).

Следующее окно, называющееся Define Restriction (рис. 22.9), предназначено для ввода кода, помещаемого в раздел WHERE запроса SELECT, на основе которого будет формироваться представление. Подробно написание запросов SELECT, а также ра Глава 22. Использование представлений бота с разделом WHERE, будут рассмотрена в главе 29. Сейчас же скажем, что с по мощью раздела WHERE МОЖНО связать две и более таблиц, а также установить вер тикальные фильтры, ограничив диапазон строк, входящих в представление.

Рис. 22.7. Окно Select Objects мастера Create View Wizard Рис. 22.8. Окно Select Columns мастера Create View Wizard 33 Зи. Часть IV. Разработка и сопровождение баз данных Рис. 22.9. Окно Define Restriction мастера Create View Wizard С помощью очередного окна — Name the View — указывается имя, которое бу дет иметь представление. Мы уже не раз в этой главе обсуждали выбор имени представления. Все сказанное ранее относится и к именованию представления при создании его с помощью мастера.

Рис. 22.10. Последнее окно мастера Create View Wizard Глава 22. Использование представлений Следующее же окно мастера (рис. 22.10) является последним. В нем выводится код команды CREATE VIEW, сгенерированный мастером на основе данных, ука занных пользователем в окнах мастера. Пользователь может внести в приведен ный код любые необходимые изменения. После нажатия кнопки Finish мастер запустит приведенный в окне код, в результате выполнения которого и будет создано представление. Дальнейшая работа с представлением может вестись как средствами Enterprise Manager, так и средствами Transact-SQL. Эти методы были рассмотрены в предыдущих разделах.

Модификация данных в представлении Часто представления используются как средство повышения безопасности дан ных. Администратор не предоставляет пользователям непосредственного доступа ко всем данным таблицы, а создает представление, с помошью которого пользо ватели могут просматривать данные. Однако часто помимо просмотра пользова телям бывает необходимо выполнить и изменения данных.


Представления SQL Server 2000 позволяют пользователям не только просматри вать, но и модифицировать данные. Изменение данных с помощью представлений выполняется точно так же, как и изменение данных в обычных таблицах. Однако разрешается исправление только тех столбцов представления, которые выбраны непосредственно из таблиц или представлений, а не являются результатом вычис ления какого бы ни было выражения. Кроме того, за одну операцию разрешается изменение только столбцов, принадлежащих одной таблице. Если нужно изменить данные во всех столбцах представления, построенного на основании четырех таб лиц, то такую операцию нужно будет разбить на четыре этапа.

Запрос, на основе которого создано представление, должен обращаться хотя бы к одной таблице. То есть в разделе FROM ДОЛЖНО быть указано как минимум од но имя. Если представление строится только на значениях, возвращаемых функциями и константами, то такое представление нельзя будет обновить.

Если представление создано с использованием опции WITH CHECK OPTION И В запросе существует раздел WHERE, TO будет запрещено выполнение любых моди фикаций, которые приведут к нарушению условия, указанного в разделе WHERE.

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

Для предоставления доступа к представлению используется команда GRANT, ДЛЯ запрещения доступа— команда DENY. Чтобы отменить выданные ранее права (неявное отклонение) доступа, применяется команда REVOKE.

зз* 1010 Часть IV. Разработка и сопровождение баз данных ^ Для представлений могут выдаваться следующие права доступа:

П SELECT — пользователю разрешается выборка данных из всего представления или только из определенных столбцов;

• UPDATE — это право разрешает выполнять изменение данных через представ ление, но не позволяет просматривать данные;

П INSERT — пользователю разрешается добавлять в представление новые стро ки, но запрещается просматривать и изменять данные таблицы, в т. ч. и вставленные им самим;

• DELETE — разрешается удаление строк представления.

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

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

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

Переименование представлений Иногда бывает необходимо изменить имя представления. Конечно, можно уда лить представление и создать его заново с другим именем, однако при этом бу дет потеряна информация о правах доступа пользователей к этому представле нию. Чтобы избежать этого, рекомендуется использовать системную хранимую процедуру sprename, специально предназначенную для изменения имен объек тов базы данных. Эта хранимая процедура напрямую обращается к системным таблицам базы данных и исправляет значения в столбцах, описывающих имя объекта. Хранимая процедура sprename имеет следующий синтаксис:

sp_rename [@objname =]. 'object_name', [@newname =] 'new name', 'OBJECT' Глава 22. Использование представлений.

Текущее имя представления задается с помощью параметра 'object name. Но вое имя определяется параметром • new_name'. Параметр • OBJECT ' в этом случае задает тип изменяемого объекта и не должен изменяться.

В качестве примера рассмотрим переименование представления, созданного в предыдущем примере:

sp_rename 'union_view', 'view2', 'OBJECT' ( Замечание ) Прежде чем изменить имя представления, следует убедиться, что оно не использу ется в хранимых процедурах или других представлениях. В противном случае их работа будет нарушена. Однако можно создать другое представление с таким же именем и набором столбцов.

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

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

Для изменения представления предназначена команда ALTER VIEW, имеющая синтаксис:

ALTER VIEW [database_name.][owner.]view_name [ (column [,... n ] ) ] [ WITH view_attribute [,...n]] AS select_statement [WITH CHECK OPTION] Назначение параметров команды аналогично одноименным параметрам коман ды CREATE VIEW и было рассмотрено ранее в этой главе.

) ( Замечание Если в команде ALTER TABLE не указывается опция WITH CHECK OPTION, TO КОД представления не будет шифроваться, независимо от того, использовалось ли шифрование при создании представления. Соответственно, если при изменении опция WITH CHECK OPTION указана, то шифрование будет выполнено.

( Замечание ^ При внесении изменений в представления следует убедиться, что эти изменения не коснутся других представлений или хранимых процедур. При удалении из представ Часть IV. Разработка и сопровождение баз данных ления столбцов или исправлении их имени или типа данных может быть нарушена работа связанных объектов.

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

Чтобы упростить эту задачу, в SQL Server 2000 имеются несколько хранимых процедур, с помощью которых можно получить информацию о представлениях.

Если вы хотите узнать, какое количество столбцов возвращает представление, а также получить подробную информацию о типе данных и свойствах каждого из столбцов, то необходимо вызвать хранимую процедуру s p h e l p, имеющую син таксис:

sp_help [[@objname =] name] Эта хранимая процедура возвращает информацию о представлении в нескольких блоках, каждый из которых содержит одну или более колонок, в которых и ука зывается информация о представлении. В табл. 22.1 приведен список колонок, содержащихся в первом блоке. Все перечисленные колонки образуют одну строку, каждая из которых описывает один столбец представления.

Таблица 22.1. Общие сведения о представлении Глава 22. Использование представлений Таблица 22.1 (окончание) Identity nvarchar (128) Имя столбца представления, для которого установ лено свойство IDENTITY Seed numeric Начальное значение счетчика, установленное для с столбца при создании таблицы Замечание Increment numeric Шаг приращения значений в столбце-счетчике Системная хранимая процедура sp_help применяется для получения информации Not For int Если указано Yes, то подсистема репликации может практически по всем типам объектов базы данных. Однако для каждого типа объек Replication вставлять произвольные значения в столбец тов возвращается свой набор блоков и колонок. Но для таблиц и представлений вы водятся одинаковые данные. То есть всет.сказанное о получении информации о IDENTITY, е. без использования автонумерации Следующие блоки данных содержат информацию о столбце RowGuidCoi, о груп свойствах представления относится и к таблицам.


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

spheip, не позволяет рассмотреть в книге пример использования этой проце дуры. Тем не менее, можно легко получить интересующую информацию, вы полнив хранимую процедуру в Query Analyzer.

Помимо свойств столбцов представления иногда бывает полезно просмотреть код, на основе которого было выполнено создание представления. Как и для всех других объектов баз данных сервер при создании представления автомати чески сохраняет в системной таблице syscomments код, необходимый для созда ния представления.

Для получения кода нужного представления Можно воспользоваться системной хранимой процедурой spheiptext И И напрямую обратиться к системной таб Л 1014 Часть IV. Разработка и сопровождение баз данных лице syscomments. Ранее уже был рассмотрен синтаксис и вызов хранимой про цедуры s p h e i p t e x t. Интерес представляет получение кода представления на прямую ИЗ таблицы syscomments:

SELECT t e x t F O syscomments where id = OBJECT_ID('union_view') RM Будет возвращен результат:

text CREATE VIEW union_view AS SELECT title_id, title = CAST(title as char(30)) + CASE WHEN LEN(title)30 THEN '...' ELSE " END, value = price*ytd_sales, type = 'psychology' FROM titles_psychology UNION SELECT title_id, title = CAST(title as char(30)) (1 row(s) affected) Идентичный результат возвращает и хранимая процедура s p h e i p t e x t.

Глава 22. Использование представлений W всех объектах базы данных— индексах, ограничениях целостности, умолчаниях, правилах, типах данных и т. д.

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

Более удобным является вызов хранимой процедуры s p d e p e n d s, имеющей син таксис:

sp_depends [Sobjname =] 'object' Для получения информации как о зависимых, так и о зависящих объектах доста точно указать имя интересующего объекта. Рассмотрим использование процедуры spdepends для получения информации о зависимости объектов базы данных от созданного в первом примере этой главы представления unionview:

sp_depends 'union_view' Будет получен следующий результат:

In the current database, the specified object references the following:

name type updated selected column In the current database, the specified object is referenced by the follow ing:

name type Как видно, от представления union_view не зависит ни один объект и его мож но удалять, не опасаясь нарушения функционирования других объектов базы данных.

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

И не важно, чем вызвано это желание.

1016 Часть IV. Разработка и сопровождение баз данных Для удаления представления предназначена следующая команда Transact-SQL:

DROP VIEW { v i e w } [,...n] С помощью этой команды за один раз можно удалить множество представле ний, перечислив их имена через запятую. Помимо собственно имени представ ления можно указать и имя владельца. Однако задание имени базы данных не разрешается, поэтому с помощью команды DROP VIEW МОЖНО удалить представ ления только из текущей базы данных.

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

Право удалить представление имеет только владелец этого представления и чле ны фиксированной роли базы данных dbo. Если необходимо, чтобы удаление объекта выполнил иной пользователь, следует передать ему права владения объ ектом. Для этого нужно использовать системную хранимую процедуру spchangeobjectowner, имеющую следующий синтаксис:

'object1, sp_changeobjectowner [Sobjname =] [Snewowner =] 'owner' Глава Индексы Современные базы данных часто содержат миллионы строк. По умолчанию данные хранятся в порядке их добавления в таблицу, т. е. неупорядоченно. По иск нужной информации в таком наборе данных занимает много времени. Во образите себе большую энциклопедию, содержащую десятки тысяч слов, не упорядоченных по алфавиту. Теперь представьте, что вам нужно найти в такой энциклопедии определенное слово. Нельзя даже приблизительно сказать, в ка ком месте энциклопедии может быть нужное слово. Поэтому придется начинать поиск с самой первой страницы и просматривать поочередно каждую страницу до тех пор, пока нужное слово не будет найдено. Поиск может закончиться как на второй, так и на предпоследней странице. Аналогичная ситуация и при рабо те со строками таблицы, не упорядоченными по какому-нибудь критерию. Что бы найти нужные данные, сервер должен будет перебрать все строки таблицы.

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

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

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

Аналогичный подход оптимизации поиска данных используется и в базах дан ных. Повышение производительности достигается за счет того, что данные представляются в упорядоченном хвиде. Механизмом, увеличивающим скорость 1018 Часть IV. Разработка и сопровождение баз данных поиска данных и обеспечивающим минимальные затраты времени на анализ таблиц является индексирование. Индексы представляют собой набор ссылок на места физического размещения строк в структуре базы данных, упорядоченный по возрастанию или убыванию. При этом данные в самой таблице могут быть и неупорядоченными. Столбец, по которому была произведена индексация, назы вается индексированным.

( Замечание ^ В SQL Server 2000 индекс может создаваться не только на основе значений одного столбца, но также и на комбинации значений двух и более столбцов. Индекс, соз данный на основе более чем одного столбца, называется составным индексом (composite index).

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

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

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

SQL Server 2000 не является исключением. Если в ходе выполнения запроса происходит обращение к столбцу, для которого был определен индекс, то сер вер автоматически производит поиск нужных значений непосредственно не в таблице, а в индексе. Когда в индексе находится искомое значение, сервер об ращается к соответствующей строке таблицы и выбирает нужные данные уже из нее. Хотя на самом деле данные в столбце могут быть и неупорядочены, за счет того, что столбец будет- иметь индекс, можно реализовать эффективные алго ритмы поиска информации. Это как раз и сделано в SQL Server 2000.

Одним из наиболее эффективных методов поиска, реализованных и в SQL Server 2000, является метод "деления пополам", работающий с упорядоченным представлением данных, т. е. с индексами. При поиске конкретного значения в отсортированном по возрастанию наборе в простейшем случае берется значение из середины упорядоченной последовательности и сравнивается с искомым.

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

Глава 23. Индексы Если значения опять не совпали, то интервал снова уменьшается вдвое и про цесс продолжается. Когда значения, наконец, совпадут, поиск прекращается.

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

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

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

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

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

В SQL Server 2000 реализовано несколько типов индексов:

• некластерный индекс (Nonclustered Index);

1020 Часть IV. Разработка и сопровождение баз данных • кластерный индекс (Clustered Index);

• П уникальный индекс (Unique Index).

( Замечание J При создании индекса пользователь указывает, будет он кластерным или некла стерным. Независимо от этого, он может дополнительно сделать создаваемый ин декс и уникальным.

Рассмотрим назначение и условия использования индексов каждого типа.

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

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

Данные в таблице могут находиться в неотсортированном состоянии в порядке их добавления в таблицу. Указатель на строку (RID, row locator или row identifi cator) представляет собой информацию о местоположении конкретной строки в базе данных и включает следующую информацию:

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

• Идентификационный номер страницы (ID page). Каждый из файлов данных, в свою очередь, состоит из множества 8-килобайтных страниц, на которых Глава 23. Индексы собственно и хранятся данные. Каждая такая страница имеет идентификаци онный номер, уникальный в пределах файла. Этот номер и используется для идентификации строки.

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

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

Некластерный индекс может быть создан более чем для одного столбца. В этом случае данные в индексе сначала упорядочиваются по первому указанному столбцу, затем по второму и т. д. Максимальное количество индексов на одну таблицу в SQL Server 2000 равно 249. Однако на практике не рекомендуется ис пользовать более 4—5 индексов. Максимальное количество столбцов, которое может участвовать в построении индекса любого типа, составляет 16. При этом суммарная длина всех индексируемых значений не должна превышать 900 байтов. Отметим, что это ограничение относится непосредственно к индек сируемым данным, а не к суммарным размерам столбцов. Например, в таблице могут существовать столбцы переменной длины (типы данных, имеющие при ставку var), общий объем которых больше 900 байт. Однако если общий объем значений, хранящихся в каждой строке, не превышает 900 байт, то создание индекса будет возможно. Но при этом будет выдано предупреждающее сообще ние. Также сервер будет отменять операции вставки и изменения данных, в ре зультате которых суммарный объем значений в столбцах, на основе которых построен индекс, превысит 900 байт.

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

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

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

1022 Часть IV. Разработка и сопровождение баз данных • Столбец часто включается в запрос в качестве условия поиска в разделе WHERE или HAVING. Если же столбец используется только в разделе SELECT, TO его индексирование не приведет к повышению производительности.

• Длина столбцов не превышает 10 байт. Не рекомендуется создавать индекс для слишком длинных столбцов. Обычно индексируются столбцы с иденти фикационными номерами, в качестве которых выступают значения типа Данных i n t ИЛИ b i g i n t.

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



Pages:     | 1 |   ...   | 25 | 26 || 28 | 29 |   ...   | 33 |
 





 
© 2013 www.libed.ru - «Бесплатная библиотека научно-практических конференций»

Материалы этого сайта размещены для ознакомления, все права принадлежат их авторам.
Если Вы не согласны с тем, что Ваш материал размещён на этом сайте, пожалуйста, напишите нам, мы в течении 1-2 рабочих дней удалим его.