Файл: Тема концепция управления данными в современных информационных системах Цель лекции.docx

Изменение и удаление таблиц

Определение таблицы можно изменить с помощью обозревателя объектов или с помощью инструкции ALTER TABLE языка Transact-SQL. Например, следующие инструкции Transact-SQL показывают, каким образом можно добавить столбец и изменить возможность столбца содержать значения NULL в таблице Sales.CustomerOrders, определенной ранее.

ALTER TABLE Sales.CustomerOrders ADD SalesPersonID int NOT NULL GO

ALTER TABLE Sales.CustomerOrders

ALTER COLUMN Notes nvarchar(200) NOT NULL GO

Чтобы удалить таблицу из базы данных, можно удалить ее в обозревателе объектов или с помощью инструкции DROP TABLE языка Transact-SQL. В следующем примере кода Transact-SQL показывается, каким образом можно удалить таблицу с помощью инструкции DROP TABLE.

DROP TABLE Sales.CustomerOrders
4. Индекс — это набор страниц, связанный с таблицей (или представлением) и используемый для ускорения получения строк из таблицы или обеспечения уникальности. Например, без индекса-указателя пришлось бы листать всю книгу страница за страницей, чтобы найти сведения по нужной теме. В Microsoft SQL Server индексы используются для указания местонахождения строки и позволяют избежать просматривания всех страниц данных таблицы.

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

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

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

При создании индекса SQL Server выполняет сканирование таблицы, выбирает значения в индексируемом столбце и записывает на индексную страницу указатели на страницы данных и идентификаторы строк для индексируемых значений.

---

В SQL Server допускаются следующие два типа индексов: кластеризованные и неклас­теризованные.

Кластеризованный индекс сортирует и сохраняет строки данных таблицы в зависимости от ключа кластеризованного индекса. Кластеризованный индекс реализован в виде сбалансированного дерева (В-дерева).Так как кластеризованный индекс определяет порядок, в котором фактически хранятся строки таблицы, у каждой таблицы может быть только один кластеризованный индекс — строки таблицы могут храниться только в одном порядке .В качестве ключа кластеризованного индекса нельзя использовать столбцы со следующими типами данных: varchar(max), nvarchar(max), varbinary(max) или xml.

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

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

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

---

Индекс можно создать, используя обозреватель объектов SQL Server Management Studio или инструкцию CREATE INDEX Transact-SQL .

Для создания индекса с помощью Transact-SQL используйте инструкцию CREATE INDEX. Для инструкции CREATE INDEX применяется следующий синтаксис:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]

INDEX index_name ON {table | view} (column [ASC | DESC] [,...n]) INCLUDE (column [,...n ])

[WITH

[PAD_INDEX = {ON | OFF}]

[[,] FILLFACTOR = fillfactor] [[,] IGNORE_DUP_KEY = {ON | OFF}] [[,] ONLINE = {ON | OFF}]

[[,] ALLOW_ROW_LOCKS = { ON | OFF}] [[,] ALLOW_PAGE_LOCKS = { ON | OFF}]]

[ON {partition_scheme (column) | filegroup | „default”}]
Код следующего примера создает некластеризованный индекс по возрастанию с именем AK_Employee_LoginID по столбцу LoginID таблицы HumanResources.Employee в базе данных AdventureWorks.

CREATE NONCLUSTERED INDEX [AK_Employee_LoginID]

ON [HumanResources].[Employee] ( [LoginID] ASC)

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

В среде SQL Server Management Studio уникальный индекс можно создать, устанавливая при создании индекса флажок «Уникальный» в диалоговом окне «Создание индекса».

В примере показана инструкция Transact-SQL, необходимая для создания некластеризованного индекса с именем AK_Employee_LoginID для столбца LoginID таблицы HumanResources.Employee в базе данных AdventureWorks.
CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_Login ID]

ON [Human Resources].[Employee] ( [Login ID] ASC)

ТЕМА 10. Хранимые процедуры и триггеры
Цель лекции: рассмотреть создание и использование представлений, процедур и триггеров.

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

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

1.Создание представлений

2.Виды процедур в SQL Server. Создание и использование хранимых процедур

3.Создание триггеров

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

---

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

- Упрощения работы пользователей с данными. Можно определить часто используемые соединения, проекты, запросы UNION и SELECT как представления, чтобы пользователям не нужно было указывать все условия и квалификацию каждый раз при выполнении дополнительной операции с этими данными.

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

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

Представление можно создать с помощью инструкции CREATE VIEW Transact-SQL или с помощью графического интерфейса структуры в SQL Server Management Studio. С помощью инструкции SELECT можно указать содержание представления, как части определения представления.

Чтобы отличать представления от таблиц, необходимо разработать согласованные правила именования. Можно, например, добавлять букву «v» или слово «view» в качестве суффикса имени каждого создаваемого представления. Этот подход позволяет легко различать таблицы и представления.

Чтобы создать представление, используйте конструктор представлений в SQL Server Management Studio или инструкцию CREATE VIEW языка Transact- SQL. Чтобы открыть конструктор представлений в обозревателе объектов, разверните базу данных, с которой будете работать, щелкните правой кнопкой узел «Представление» и выберите «Новое представление». Затем можно конструировать свое представление, используя графический интерфейс, в котором можно выбирать таблицы и столбцы для включения в представление, определять связи столбцов, ограничивать возвращаемые строки и настраивать такие параметры

---

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

Инструкция CREATE VIEW имеет синтаксис.

CREATE VIEW [schema_name . ] view_name [(column [ ,...n ])] [WITH [ENCRYPTION] [, SCHEMABINDING] [, VIEW_METADATA]]

AS select_statement [ ; ] [WITH CHECK OPTION]

Пример кода иллюстрирует создание представления HumanResources.vEmployee, которое состоит из набора столбцов из таблиц базы данных AdventureWorks.
CREATE VIEW [HumanResources].[vEmployee] AS

SELECT

e.[EmployeeID],c.[Title],c.[FirstName],c.[MiddleName],c.[Las tName]

,c.[Suffix],e.[Title] AS [JobTitle],c.[Phone],c.[EmailAddress]

,c.[EmailPromotion],a.[AddressLine1],a.[AddressLine2],a.[City]

,sp.[Name] AS [StateProvinceName],a.[PostalCode]

,cr.[Name] AS [CountryRegionName],c.[AdditionalContactInfo] FROM [HumanResources].[Employee] e

INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]

INNER JOIN [HumanResources].[EmployeeAddress] ea ON e.[EmployeeID] = ea.[EmployeeID]

INNER JOIN [Person].[Address] a ON ea.[AddressID] = a.[AddressID]

INNER JOIN [Person].[StateProvince] sp

ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr

ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
2.Хранимые процедуры представляют собой фрагменты программного кода на Transact - SQL, которые выполняются на сервере. Они могут запускаться вызываю­щим их приложением, триггерами или при проверке выполняемости правил целост­ности данных. Хранимые процедуры могут иметь параметры, обеспечивающие воз­можность передавать в нее значения и получать обратно значения, выбираемые из таблиц или вычисляемые при выполнении процедуры.

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

В сравнении с использованием динамических операторов языка SQL хранимые про­цедуры имеют следуют преимущества: высокая производительность, поскольку компилируются при первом выполнении с использованием оптимизации доступа к информации из таблиц; возможность выполнения на локальном и удаленном сервере.

По области видимости различают хранимые процедуры следующих четырех ти­пов: системные (System), локальные (Local), временные (Temporary) и удаленные (Remote).

Системные хранимые процедуры размещаются в базе данных Master, используют­ся сервером и администратором. Имена системных процедур начинаются с символов sp_. Для создания собственной системной хранимой процедуры достаточно присвоить ей имя, начинающееся с sp_ и поместить ее в базу данных Master.

---