В этом упражнении вы будете выполнять задачи по созданию и изменению таблиц. Вы создадите схемы, предоставите и удалите разрешения, и создадите вычисляемые поля. Чтобы достичь понимания этих тем, пожалуйста обратитесь к материалу курса или воспользуйтесь ссылкой http://technet.microsoft.com/en-us/library/ms162575.aspx [MSDN].

Вычисляемые и постоянные поля

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

Вычисляемые поля являются виртуальными, и по-умолчанию физически не хранятся в таблице. Их значения вычисляются каждый раз когда на них ссылаются в запросе. Ядро базы данных использует ключевое слово PERSISTED в инструкциях CREATE TABLE или ALTERTABLE что бы физически сохранить вычисляемое поле в таблицу.

В этом примере, мы создадим таблицу которая хранит информацию о сотрудниках, и мы создадим вычисляемое поле Date of Retirement для каждого сотрудника, например: DOBirth + 60 years- 1day.

Создание вычисляемого поля в инструкции CREATE TABLE

Шаг 1

Откройте Пуск, Все Программы, Microsoft SQL Server 2008 R 2 и запустите SQL Server Management Studio (SSMS).

 

Когда SSMS запустится, войдите в Database Engine используя настройки по-умолчанию и откройте новое окно запросов нажав на кнопку New Query слева.

 

Создайте новую таблицу в базе данных AdventureWorks используя следующие команды:

USE AdventureWorks

GO

CREATE TABLE [dbo].[ComputedColumnTest]

([EmpNumber][int]NULL,

[DOBirth][datetime]  NULL,

[DORetirement]AS

(dateadd(year,(60),[DOBirth])-(1)) PERSISTED)

GO

Нажмите кнопку Execute (сверху страницы на панели инструментов со знаком восклицания) или нажмите F 5 что бы выполнить команды.

 

Создайте такое же вычисляемое поле используя SSMS

Шаг 1

В SQL Server Management Studio откройте Object explorer слева (может быть ужеоткрыт). Раскройте Databases и AdventureWorks, прав ый клик на объекте Tables в базе данных AdventureWorks , и затем выберите New Table что бы создать новую таблицу.

 

Шаг 2

Создайте такую структуру базы данных:

Название поля - EmpNumber Тип данных- Int

Название поля - DOBirth Тип данных - Datetime

Поле DORetirement будет нашим вычисляемым полем, раскройте Column Properties внизу страницы, и откройте Computed Column Specification под секцией Table Designer :

 

В спецификации напишите:

( dateadd ( year ,(60),[ DOBirth ])-(1))

 

Так же как и ранее, вы можете создать постоянное поле выбрав Persisted :Yes в постоянном поле:

 

Закройте и сохраните таблицу как ComputedColumnTest 2 нажав на кнопку Закрыть( X ) сверху справа дизайнера таблиц.

 

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

 

Вставка значений в вычисляемое поле

Шаг 1

 

Вставьте значения в ранее созданную таблицу используя инструкцию INSERT INTO как показано ниже в коде. Выполните код в новомокне запросаNew Query вMicrosoft SQL ServerManagementStudio:

USE AdventureWorks

GO

INSERT INTO ComputedColumnTest (EmpNumber, DOBirth)

VALUES

(1, '1977-12-23'),

(2, '1980-01-01'),

(3, '1968-03-23'),

(4, '1988-12-12'),

(5, '1975-06-15')

GO

 

Нажмите кнопку Execute чтобы выполнить SQL код.

 

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

Шаг 2

Вы можете увидеть вычисленные значении для поля DORetirement используя предложение SELECT:

SELECT * FROM dbo . ComputedColumnTest

GO

Когда напечатаете SQL инструкции, опят нажмите кнопку Execute .

 

Схемы баз данных

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

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

Создание схемы базы данных используя SSMS

Шаг 1

Откройте SQL ServerManagement Studio и войдите на сервер баз данных( Database Engine ).

Выберите базу данных AdventureWorks и разверните папку Security из списка.

Нажмите правой кнопкой мыши на Schemas и выберите New Schema .

 

Шаг 2

На вкладке General заполните информацию для новой схемы. В этом примере мы назовем схему APerson и назначим владельцем схемы роль db _securityadmin.

Напишите имя в поле Schema name , потом нажмите кнопку Search рядом с полем Schema Owner .

 

В поле поиска, напишите : db _securityadmin и нажмите кнопку Check Names . Это должно поместить объект в квадратные скобки. Когда сделаете нажмите OK .

 

Выберите страницу Permissions на левой панели, потом нажмите кнопку Search , нажмите кнопу Browse и добавьте к схеме роль Public .

 

Нажмите OK , и еще раз OK чтобы вернуться. Теперь вы сможете выбрать необходимые разрешения; в этом примере вы будете устанавливать такие GRANT разрешения:

Execute

Insert

Select

 

Нажмите OK чтобы закрыть окно.

 

Добавление таблицы к новой схеме

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

Шаг 1

Используя инспектор объектов( object explorer) вSQL Server ManagementStudio , разверните Databases >AdventureWorks и нажмите правой кнопкой мыши на таблице ComputedColumnTest (таблица что ранее создана) и выберите Design :

 

В окне Design нажмите F 4 чтобы показать окно Properties .

 

В окне Properties , измените схему на созданную вами схему:

 

Обратите внимание, вы получите подсказку о том, что изменение схемы сбросит текущие разрешения на объект, нажмите Yes.

 

Закройте окно Design нажав правой кнопкой мыши на вкладке и выбрав Close , сохраните изменения.

 

Создание схемы базы данных используя SQL инструкции

Шаг 1

В SQL Server Management Studio откройтеновоеокно запросов( New Query ).

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

USE AdventureWorks

GO

CREATE SCHEMA APerson2 AUTHORIZATION db_securityadmin

GO

 

Примечание: Если ранее вы создали схему используя SSMS GUI и пытаетесь использовать те же данные схемы, то вам нужно изменить данные или удалить ранее созданную схему.

Шаг 2

Что бы создать таблицу в новой схеме, используйте предложение CREATE TABLE:

USE AdventureWorks

GO

CREATE TABLE APerson2.Employee

(EmpNumber INT,

FirstName NVARCHAR (40),

LastName NVARCHAR (40))

GO

 

Убедитесь что вы создали таблицу, используя предложение SELECT следующим образом:

Select * FROM APerson2.Employee

 

Создание таблицы с таким же именем в другой схеме

Шаг 1

Выполните следующий код с окне запросов:

USE AdventureWorks

GO

CREATE TABLE dbo.Employee

(EmpNumber INT,

FirstName NVARCHAR (40),

LastName NVARCHAR (40))

GO

 

Шаг 2

Вставьте новые записи в таблицу Employee , что находится в схемах dboиAPerson2

USE AdventureWorks

GO

INSERT INTO dbo.Employee (EmpNumber, FirstName,LastName )

VALUES

(1, 'FirstName_dbo',',LastName_dbo'),

(2, 'FirstName_dbo',',LastName_dbo')

GO

INSERT INTO APerson2.Employee (EmpNumber, FirstName,LastName )

VALUES

(1, 'FirstName_APerson2',',LastName_APerson2'),

(2, 'FirstName_APerson2',',LastName_APerson2')

GO

 

Step 3

Напишите запрос к таблице, что расположены в двух разных схемах

USE AdventureWorks

GO

Select * From dbo.Employee

GO

Select * From APerson2.Employee

GO

 

Изменение схемы существующей таблицы

Шаг 1

Мы можем изменить схему существующей таблицы используя опцию TRANSFER предложения ALTER SCHEMA . В этом примере вы измените схему таблицы ComputedColumnTest 2 с dbo на только что созданную схему APerson 2 . Откройте окно запросови напишитеследующийкод:

USE AdventureWorks

GO

ALTER SCHEMA APerson2 TRANSFER dbo.ComputedColumnTest2

GO

 

Вы можете увидеть изменения(обновите панель Object Explorer нажав на кнопку обновить).

Откройте таблицу APerson2.ComputedColumnTest 2 в дизайнере( Design ), нажмите F 4 что бы увидеть свойства таблицы, обратите внимание на схему:

 

Управление разрешениями

Вы можете использовать предложения GRANT,DENY, and REVOKE что бы давать или забирать разрешения у пользователя или роли.

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

 

Используйте предложение GRANT что бы предоставить разрешение на SELECT

Шаг 1

Откройтеновоеокно запросов( New Query )в SQL Server Management Studio . В окнезапросов,напишитеследующийзапрос:

USE AdventureWorks

GO

GRANT SELECT ON APerson.ComputedColumnTest TO Public

 

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

Вы можете использовать предложение DENY чтобы запретить разрешение как на объекты, так и на использование предложений.

Шаг 2

В новом окне запроса, напишите следующий код:

USE AdventureWorks

GO

DENY SELECT ON APerson.ComputedColumnTest TO Public

 

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

Шаг 1

В окне запросов, напишите следующий запрос:

USE MASTER

GO

CREATE LOGIN practicelogin WITH PASSWORD=’practice123’, CHECK_POLICY=OFF

GO

USE [AdventureWorks]

GO

CREATE USER practiceuser FROM LOGIN practicelogin

GO

EXEC sp_addrolemember N'db_ddladmin', N'practiceuser'

GO

DENY SELECT ON SCHEMA ::[dbo] TO [practiceuser]

GO

 

Логин и пользователь успешно создались, так же мы сделали пользователя членом роли ddl_admin.

Шаг 2

В окне запросов, напишите следующий код:

USE [AdventureWorks]

GO

EXECUTE AS USER='practiceuser'

GO

CREATE TABLE dbo.Practice(Id INT)

GO

SELECT * FROM dbo.Practice

GO

 

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

Предложение REVOKE используется что бы отменять ранее созданные GRANT или DENY разрешения пользователя в текущей базе данных. Вы можете использовать предложение REVOKE что бы отменять разрешение как на объекты, так и на использование предложений.

В следующем примере, предложение REVOKE отменяет разрешение на SELECT у роли Public на таблицу APerson.ComputedColumnTest:

Шаг 3

В новом окне запросов, напишите следующий запрос:

USE AdventureWorks

GO

REVOKE SELECT ON APerson.ComputedColumnTest TO Public

 

Примечание : Вы можете указать инструкцию GRANT OPTIONFOR вместе с предложением REVOKE что бы отменить WITH GRANTOPTION разрешения.

 

Передача авторизации схемы от одной роли к другой(Изменение владельца схемы)

Шаг 1

Мы можем передать владение схемой от одной роли или пользователя к другой роли или пользователю. Владельцем схемы APerson 2 является роль db_securityadmin . Мы будем передавать владение схемой роли db_owner.

Откройте новое окно запросов в SQL ServerManagement Studio . В новом окне запросов, напишите следующий запрос:

USE AdventureWorks

GO

ALTER AUTHORIZATION ON SCHEMA APerson2::[db_securityadmin] TO [db_owner]

 

Итог

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

Также попробуйте

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

·          Создать новую схему и предоставить разрешения существующему пользователю в этой схеме.

 

В этом упражнении вы будете выполнять задачи по созданию, изменению, и удалению представлений. Чтобы достичь понимания этих тем, пожалуйста обратитесь к материалу курса или воспользуйтесь ссылкой http://technet.microsoft.com/en-us/library/ms162235.aspx [MSDN].

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

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

·          Для фокусировки, упрощения и настройки просмотра данных для каждого пользователя

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

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

Создание простого представления используя предложение CREATE VIEW

В этом примере вы создадите представление в базе данных AdventureWorks , что показывает имя, фамилию, и дату приема на работу сотрудников их таблиц Employee и Contact.

Шаг 1

Присоединитесь к Database Engine и откройте новое окно запросов.

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

USE AdventureWorks

GO

CREATE VIEW hiredate_view

AS

SELECT p.FirstName, p.Lastname, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Contact AS p

ON e.ContactID = p.ContactID

GO

 

Запустите запрос на выполнение.

Шаг 2

Что бы проверить результат, используйте SELECT:

SELECT * FROM hiredate_view

GO

 

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

Свойство WITH ENCRYPTION шифрует данные в sys .syscomments что хранит текст создания представления. Использование свойства WITH ENCRYPTION предотвращает представление от публикации в рамках репликации SQL Server.  

В этом примере, вы создадите представление, что бы показать детали отклоненных заказов.

Шаг 1

В SQL Server Management Studio откройте новое окно запросов. В новом окне запросов, напишите следующий запрос что бы создать новое представление в базе данных AdventureWorks с шифрованием:

USE AdventureWorks

GO

CREATE VIEW Purchasing.PurchaseOrderReject

WITH ENCRYPTION

AS

SELECT PurchaseOrderID, ReceivedQty, RejectedQty, RejectedQty / ReceivedQty AS RejectRatio, DueDate

FROM Purchasing.PurchaseOrderDetail

WHERE RejectedQty / ReceivedQty> 0

GO

 

Запустите запрос на выполнение.

Шаг 2

Попытайтесь просмотреть SQL код представления используя sp _helptext

sp_helptext 'Purchasing.PurchaseOrderReject'

 

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

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

Используя SCHEMABINDING, предложение SELECT должно включать имена из двух частей (schema.object) таблиц, представлений или пользовательских функций, на которые ссылается. Все упомянутые объекты должны быть в той же базе данных.

В этом примере вы создадите представление hiredate_view , созданное в предыдущем примере с опцией SCHEMABINDING.

Шаг 1

В новом окне запрос, напишите следующие SQL команды:

USE AdventureWorks

GO

CREATE VIEW hiredate_view_sb

WITH SCHEMABINDING

AS

SELECT p.FirstName, p.LastName, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Contact AS p

ON e.ContactID = p.ContactID

GO

 

Запустите запрос на выполнение.

Шаг 2

Что бы увидеть работу опции SCHEMABINDING , попытайтесь удалить поле FirstNameтаблицыPerson.Contact . Запрос вернет ошибку как на скриншоте:

ALTER TABLE Person.Contact

DROP COLUMN FirstName

GO

 

Выполните запрос.

Создание представления с опцией WITHCHECK OPTION

Опция WITH CHECK OPTION проверяет все предложения на изменения данных выполненных по отношению к представлению, соответствовать критерию установленному в предложении SELECT . Когда запись изменена при помощи представления, то опция WITH CHECK OPTION гарантирует, что информация остается видимой из представления после подтверждения изменения.

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

Шаг 1

Откройте новое окно запросов в SQL ServerManagement Studio.

В новом окне запросов, напишите следующий запрос чтобы создать представление с опцией WITH CHECK OPTION в базе данных AdventureWorks:

USE AdventureWorks

GO

CREATE VIEW dbo.SeattleOnly

AS

SELECT p.LastName, p.FirstName, a.City, sp.StateProvinceCode

FROM Person.Contact p

INNER JOIN HumanResources.Employee e

ON p.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 a.StateProvinceID = sp.StateProvinceID

WHERE a.City = 'Seattle'

WITH CHECK OPTION;

GO

 

Выполните запрос.

Шаг 2

Проверьте результаты используя предложение SELECT.

SELECT * from SeattleOnly

 

Шаг 3

Что бы увидеть как работает опция WITH CHECK OPTION , попробуйте обновить данные представления для города отличного от Сиэтла. Запрос вернет ошибку, как показано ниже.

UPDATE SeattleOnly

SET City = ' Houston '

WHERE FirstName = 'Jack'

 

Изменение представления используя предложение ALTER VIEW

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

Что бы изменить представление, используйте предложение ALTERVIEW.

В этом примере вы будете изменять представление hiredate _ view , чтобы оно включало только сотрудников, которые нанимались до 2000 года.

Шаг 1

В новом окне запроса напишите следующий запрос на изменение hiredate _ view:

USE AdventureWorks

GO

ALTER VIEW hiredate_view

AS

SELECT p.FirstName, p.LastName, e.HireDate

FROM HumanResources.Employee e

JOIN Person.Contact AS p

ON e.ContactID = p.ContactID

WHERE HireDate< CONVERT (DATETIME, '20000101',101)

GO

 

Шаг 2

Используйте предложение SELECT для проверки результатов:

SELECT * FROM hiredate_view

 

Обратите внимание, что тут нет даты найма после 2000 года.

Управление разрешениями

Вы можете использовать предложения GRANT,DENY, и REVOKE что бы предоставить или забрать разрешения у пользователя или роли на представление.

Использование GRANT

В этом примере вы предоставите разрешение SELECT для представления hiredate_viewролиpublic.

Шаг 1

Откройте новое окно запроса в SQL ServerManagement Studio . В новом окне запроса напишите следующий запрос, что бы предоставить разрешение SELECT роли publicнаhiredate_view:

USE AdventureWorks

GO

GRANT SELECT ON hiredate_view TO Public

 

Шаг 2

Что бы запретить разрешение SELECT для представления hiredate_view , просто измените GRANT на DENY:

USE AdventureWorks

GO

DENY SELECT ON hiredate_view TO Public

 

Шаг 3

Аналогично, что бы отменить разрешения, используйте команду REVOKE:

USE AdventureWorks

GO

REVOKE SELECT ON hiredate_view TO Public

Пример работы с разрешениями для представлений

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

Шаг 1

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

USE MASTER

GO

CREATE LOGIN practicelogin WITH PASSWORD='practice123', CHECK_POLICY=OFF

GO

USE [AdventureWorks]

GO

CREATE USER practiceuser FROM LOGIN practicelogin

GO

EXEC sp_addrolememberN'db_ddladmin', N'practiceuser'

GO

 

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

 

Шаг 2

В новом окне запроса, напишите следующий запрос чтобы явно запретить доступ к схемам HumanResourcesи Person , т.к. на эти две схемы ссылались в представлении hiredate_view. Так же, предоставьтеразрешениена SELECT для схемыdbo:

GRANT SELECT ON SCHEMA ::[dbo] TO [practiceuser]

GO

DENY SELECT ON SCHEMA ::[HumanResources] TO practiceuser

GO

DENY SELECT ON SCHEMA ::[Person] TO practiceuser

GO

 

Шаг 3

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

EXECUTE AS USER='practiceuser'

GO

SELECT * FROM hiredate_view

GO

SELECT * FROM HumanResources.Employee

GO

SELECT * FROM  Person.Contact

GO

REVERT

GO

 

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

Итог

В этом примере, вы узнали как создавать представления с разными опциями, такими как SCHEMABINDING и WITH CHECKOPTION . Так же, вы узнали  как изменить представление и управлять разрешениями на представление.

Также попробуйте

·          Создание представления для множества таблиц и выполните инструкцию INSERT для представления

·          Назначение пользователю Drop разрешения на представление

 

В этом упражнении вы будете выполнять задачи необходимые для создания и изменения индексов. Чтобы достичь понимания индексов, обратитесь к вашему материалу курса или воспользуйтесь ссылкой http://technet.microsoft.com/en-us/library/ms188388.aspx [MSDN].

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

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

Кластеризованные индексы

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

Некластеризованные индексы

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

В этом примере, мы создадим кластеризованный и некластеризованный индекс на таблице клиентов.

Шаг 1

В SQL Server Management Studio откройтеновоеокно запроса( New Query ). В новом окне запроса, напишите следующий запрос, что бы создать таблицу Customers в базе данных  AdventureWorks:

USE AdventureWorks

GO

CREATE TABLE [dbo].[Customers]

([CustomerId][nchar] (5) NOT NULL,

[SocialSecurityNumber][nchar](10) NOT NULL UNIQUE,

[CompanyName][NVARCHAR](40)NOT NULL,

[ContactName] [NVARCHAR] (30) NOT NULL,

[City] [NVARCHAR] (15) NULL)

GO

Выполните запрос .

 

Шаг 2

Что бы кластеризованный индекс на поле CustomerId , напишите в окне запроса:

CREATE CLUSTERED INDEX idx_Customers_Custid

ON Customers ([CustomerId])

GO

Выполнитезапрос.

 

Шаг 3

Что бы создать некластеризованный индекс на поле CompanyName , напишите в окне запросов следующие команды:

CREATE NONCLUSTERED INDEX idxnc_Customers_CompanyName

ON Customers ([CompanyName])

GO

Выполнитезапрос.

 

Уникальный индекс

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

Шаг 1

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

CREATE UNIQUE INDEX idx_uniq_ssn

ON Customers (SocialSecurityNumber)

GO

 

Выполните запрос.

Отфильтрованный индекс

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

В этом примере, мы создадим отфильтрованный индекс на поле ContactName для всех клиентов в городе Seattle .

Шаг 1

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

CREATE NONCLUSTERED INDEX idx_filtered_ctnm_city

ON Customers (ContactName)

WHERE City = 'Seattle'

GO

 

Выполните запрос.

Создание индексов с включенными столбцами

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

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

·          Они не учитываются ядром базы данных при подсчете количества ключей индексов или  размера ключа индекса.

В этом примере вы создадите индекс на поле ContactName и включите в него поле City :

Шаг 1

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

 

CREATE INDEX idxnc_Customers_ContactName

ON Customers ([ContactName])

INCLUDE ([City])

 

Выполните запрос.

Коэффициент заполнения

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

В этом примере, мы создадим некластеризованный индекс на поле CustomerId с коэффициентом заполнения равным 50.

Шаг 1

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

CREATE NONCLUSTERED INDEX idx_Customers_CustomerId

ON Customers ([CustomerId]) WITH

(FILLFACTOR = 50)

GO

 

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

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

 

РазницамеждуинструкциямиCREATE INDEX иCREATE STATISTICS

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

В этом примере вы создадите статистику( CREATE STATISTICS ) для таблицы Customers и используете опцию Sample , что бы  указать приблизительный процент количества записей таблицы или индексированного представления для оптимизатора запросов, что используется когда статистика создается.

Шаг 1

Откройте новое окно запросов в SQL Server Management Studio(SSMS ). В новом окне запроса напишите следующий запрос, что бы создать статистику для таблицы Customers:

CREATE STATISTICS CustomerId

ON Customers (CustomerID, SocialSecurityNumber)

WITH SAMPLE 50 PERCENT

 

Выполните запрос.

Индексированные представления

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

Шаг 1

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

USE AdventureWorks

GO

CREATE VIEW ProductSold_View

WITH SCHEMABINDING

AS

SELECT

s.SalesOrderDetailID,

p.Name,

s.OrderQty

FROM Production.Product p

INNER JOIN Sales.SalesOrderDetail s

ON p.ProductID=s.ProductID

 

Выполните запрос.

Шаг 2

Создайте уникальный кластеризованный индекс для представления на поле SalesOrderDetailID :

CREATE UNIQUE CLUSTERED INDEX idx_ProductSold

ON ProductSold_View(SalesOrderDetailID)

GO

 

Выполните запрос.

Шаг 3

Используйте предложение SELECT с планом выполнения, что бы увидеть использование индекса. Нажмите на кнопку Include Actual Execution Plan на панели инструментов или используйте меню Query >Include Actual Execution Plan . Обратите внимание на количество показанных операторов в плане выполнения после выполнения запроса. В этом случае их двое, и запрос использует сканирование кластеризованного индекса, потому что мы не отфильтровали запрос при помощи других операторов.

SELECT * FROM ProductSold_View

 

Шаг 4

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

В новом окне запроса, напишите следующий запрос:

DROP VIEW ProductSold_View

Шаг 5

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

USE AdventureWorks

GO

CREATE VIEW ProductSold_View_Without_Index

AS

SELECT

s.SalesOrderDetailID,

p.Name,

s.OrderQty

FROM Production.Product p

INNER JOIN Sales.SalesOrderDetail s

ON p.ProductID=s.ProductID

 

Шаг 6

В новом окне запроса, напишите следующий запрос. Нажмите кнопку Include Actual Execution Plan на панели инструментов или используйте меню Query >Include Actual Execution Plan . В этом случае план выполнения показывает 4 оператора для запроса, что почти вдвое больше если сравнивать с планом выполнения для запроса использующего индексированные представления, что означает большую общую стоимость запроса чем в индексированном представлении:

SELECT * FROM ProductSold_View_Without_Index

 

Примечание:  Индексированные представления довольно полезны если лежащие в основе таблицы не изменяются. Любая из инструкций Insert, Updateили Delete для таблиц лежащих в основе индексированного представления, заставит SQL Server выполнить их и для индексированного представления. Понимание этого имеет первостепенную важность.

Итог

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

 

Также попробуйте

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

·          Создать статистику на данных выборки для представления.