Индексы таблиц в MS SQL Server: назначение, типы, способы создания. Использование индексов.

Индексы-служебная таблица

· Ускоряет поиск

· Ускоряет фильтрацию

· Ускоряет сортировку

· Замедляет добавление, удаление, изменение

Автоматически создаются для ключевых полей

Хранятся в таблице sys.indexes

 

Типы индексов:

· Кластерный – определяет порядок хранения записей

· Некластерный

 

Статистики- информация о данных в столбцах таблицы

Методы поиска в индексе:

· Блочный

· Дихотомия

· Хеширование

 

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

CREATE [UNIQUE][ CLASTERED | NONCLASTERED]

INDEX <имя индекса> ON <имя таблиц и..>

(<имя столбца> [ASCIDESC] [,..])

[WITH <опции> ]

[ON <имя файловой группы>/

<имя схемы>

(<имя столбца, по которому разделяются записи индекса>)

Опции индекса: <опции индекса>::=

FILLFACTOR= <% заполнение страниц индекса при его создании>

DROP_EXISTING={ON | OFF} – существование

/CNORE.DUP.KEY={ON |OFF}

[STATISTICS NORECOMPUTE] – без обновления статистики

CREATE NONCLUSTERED INDEX LFIO ON

Authors (au_lname, au_fname)

WITH (FILLFACTOR = 80)

 

Удаление:

DROP INDEX < имя индекса> ON <имя таблицы> или

DROP INDEX < имя таблицы>, <имя индекса>

 

Перестроение индекса:

ALTER INDEX {<имя индекса>| ALL} ON <имя таблицы>

{REBUILD [<параметры>] |DISABLE}

 

Статистика столбцов и индексная статистика. Операторы создания обновления статистики.

Неплотные индексы

Поиск статист. Групп -> вычисление номера строки

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

· Опции БД auto create statistics

· Вручную

CREATE STATISTICS <имя индекса\статистики>

ON <имя таблицы\представления> (<столбец> [,…n])

UPDATE STATISTICS

Получение информации по статистике

DBCC SHOW_STATISTICS (authors,..ruct)

Использование индексов:

FROM <имя таблицы>

[WITH (INDEX (<имя или ID индекса> [,..n]..))]

 

Правила и умолчания в Transact SQL. Примеры создания и использования.

Правило – сохраняемое в БД поименованное логическое выражение, предназначенное для проверки принадлежности данных области возможных значений. Правила использует логические выражения, аналогичные условиям в параметре WHERE.

В выражении для правила записывается формальный параметр, задаваемой локальной переменной в виде @< имя переменной >

В SQL Server 2005 правила создаются только из T- SQL

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

При проверке правила параметр заменяется проверяемым значением, и если выражение оказывается истинно, то проверяемое значение принимается в базу, или переменную, иначе – отвергается.

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

Создание и использование правил:

CREATE RULE [ <имя схемы> ]. < имя правила >

AS < логическое выражение >

Например, CREATE RULE my_rule AS @pp like '[0-9][0-9][0-9]' проверяет, содержит ли строка из трех символов только десятичные цифры.

CREATE RULE Today AS @a <= GETDATE ()

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

SP_BINDRULE ‘< имя правила >’, ‘< имя столбца или типа >’

[, ’FUTUREONLY’].

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

Пример присоединения: sp_bindrule ‘Today', ‘T1.BD'

• Отсоединение правила

SP_UNBINDRULE ‘< имя столбца или типа >’

[, ’FUTUREONLY’].

FUTUREONLY - отсоединение только для проверки будущих данных, не отсоединяет от столбцов, использующих этот тип

Удаление правила из БД

• DROP RULE < имя правила > --

• Умолчания - поименованные константные выражения, используемые при отсутствии явного задания данных.

• Умолчания базы данных - вносят значения в поля таблицы и данные пользовательского типа, если эти значения не были заданы операторами, создающими данные

Создание умолчаний:

• Создание умолчания выполняет оператор:

CREATE DEFAULT < имя умолчания > AS < константное выражение >

Пример создания умолчания на вчерашнюю дату:

CREATE DEFAULT YeserDay As GETDATE () - 1

Связывание умолчания с объектом базы выполняется системной процедурой:

SP_BINDEFAULT ‘< имя умолчания >’, ‘< имя столбца | типа >’

[, ‘<FUTUREONLY >’]

SP_BINDEFAULT ‘YeserDay’, ‘My_type’, FUTUREONLY

-- 1. создается тип TypeYeserDay

Create Type TypeYesterDay FROM DateTime

Go

-- 2. создается умолчание (вчерашняя дата)

CREATE DEFAULT YeserDay As GETDATE () - 1

Go

-- 3. умолчание присоединяется к типу

SP_BINDEFAULT 'YesterDay', TypeYeserDay

-- 4. создается таблица, столбец F2 с типом, имеющим умолчание

create table T1(F1 int identity(1,1) primary key,

F2 TypeYesterDay)

-- в таблицу добавляется строка «без данных»

Insert T1(F2) Values (default)

select * From T1

F1 F2

----------- -----------------------

1 2008-10-27 10:39:44.000(это вчерашняя дата)

-- особенности умолчания в типе для переменной

declare @D TypeYesterDay

select @D

-----------------------

NULL

 

Представление (view) базы данных. Операторы создания и удаления представлений. Примеры. Ограничения при использовании представлений для внесения изменений в БД.

• Представление – виртуальная таблица, созданная операторами SELECT и UNION ALL SELECT из таблиц и других представлений БД.

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

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

• В представлении оператор SELECT не может использовать опции:

• INTO

• ORDER BY -- не действует

• Хотя таблица - представление физически не хранится, в нее можно вносить изменения (с определенными ограничениями), которые будут приводить к изменениям данных в реальных таблицах.

Создание:

Конструктором в диалоге Management Studio

Оператором Transact SQL:

Имена столбцов в списке обязательны только для вычисляемых или одноименных столбцов.

CREATE VIEW < имя представления > [(< имя столбца > [,…])]

[WITH [ENCRIPTION] [, SCHEMABINDING] ]

AS SELECT .......

[ Union All SELECT .......

......................... ]

[WITH CHECK OPTION]

Опция ENCRIPTION - шифрует текст оператора SELECT, чтобы защитить его от просмотра и выяснения имен объектов БД.

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

С опцией SCHEMABINDING имена таблиц в операторе SELECT должны включать имена схем (владельцев) и недопустима * в Select.

Опция WITH CHECK OPTION запрещает внесение во view таких изменений данных, при которых изменяется множество строк самого представления.

Пример

Написать SQL оператор, создающий представление книги_авторов_Oakland для доступа к книгам (названия, типы, фамилии и имена авторов), для авторов, проживающих в городе Oakland. Представление должно быть защищено от разрушающих структурных изменений БД

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

CREATE VIEW dbo.книги_авторов_Oakland

WITH SCHEMABINDING

AS

SELECT dbo.authors.au_lname, dbo.authors.au_fname,

dbo.titles.title, dbo.titles.type

FROM

dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id

INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id

WHERE (dbo.authors.city = 'Oakland');

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

SELECT * FROM dbo.книги_авторов_Oakland

WHERE (dbo.книги_авторов_Oakland.type = 'business')

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

1. оператор SELECT не должен содержать параметры DISTINCT, GROUP BY и агрегатные функции (SUM, AVG, …);
2. операторы INSERT или UPDATE, изменяющих данные в базе через представление, должны обрабатывать только одну таблицу. Т.е. через представление, построенное на двух и более таблицах, одним оператором можно изменять поля или добавлять строки только в одну из таблиц;
3. Изменяемые поля не должны быть:

• Вычисляемые,

• Использующие агрегатные функции;

 

Хранимые процедуры базы данных. Операторы Transact-SQL для создания, исполнения и модификации процедур. Пример создания и использования.

 

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

Хранимые процедуры являются средством распределения вычислительной и обработки данных непосредственно на сервере

Хранимая процедура создается в контекстно установленной базе данных оператором USE< имя БД >

Поэтому при создании в имени процедуры не указывается имя сервера и имя базы данных

Исходные данные для процедуры:

объекты базы,

входные параметры (аргументы);

Результат процедуры:

изменения в БД,

возвращаемые наборы строк (таблиц, создаваемых оператором Select),

выходные параметры (скалярные значения)

Оператор создания и сохранения процедуры в БД имеет вид:

CREATE PROCEDURE [< схема >.]< имя процедуры > [;< № верс >]

[@< имя форм.парам. >< тип данных > [=< умалч. знач. >] [OUTPUT] ] [,..... ]

[WITH [ RECOMPILE ] [, ENCRIPTION ] ]

AS

<операторы Transact SQL >

................................................

Описание процедуры доступно через представление sys.procedures, а код в таблице SYSCOMMENTS

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

Процедура S_Title выводит названия книг (поле Title) из таблицы Titles, относящиеся к определенному разделу знаний (поле type), задаваемому входным параметром:

CREATE PROCEDURE S_Title @t char(2)

AS

select title from titles

where

type Like '%' + RTRIM (@t) + '%‘

Встроенная функция RTRIM (@t) удаляет из параметра

@t остаточные пробелы

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

что и автор с заданным au_id

Оператор, который выводит сведения об авторах:

Select * From authors where

city = (Select city From authors where au_id = @ID)

В процедуре необходимо предусмотреть параметры:

1. @ID – входной параметр – au_id автора

2. @count (типа Int) - выходной параметр. Для вывода числа авторов, живущих в том же городе, что и заданный автор

3. Оператор, создания процедуры:

4. create procedure ProcAuthorsCity

5. @ID nvarchar(11), @count int output

6. AS

7. Begin

8. Select * From authors where

9. city = (Select city From authors where au_id = @ID)

10. set @count = @@ROWCOUNT -- число строк, -- обработанных последним SQL оператором

11. End

Для исполнения процедуры из файла скрипта или другой процедуры применяется оператор:

[ EXEC [ UTE ] ] [@< имя переменной >=]

[ [< сервер >.] <БД>.] < схема >.]< процедура > [; < № версии >]

[@< имя факт. парам. >=] < знач. факт. парам. >] [OUTPUT]

[,... ]

[WITH RECOMPILE]

Если в обращении к процедуре задано имя переменной, то ей будет присвоен возвращаемый статус процедуры

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

Если номер версии процедуры не задан, то по умолчанию исполняется процедура с номером 1.

Значение фактического параметра задается константой или переменной соответствующего типа.

Выходные параметры задаются переменной с опцией OUTPUT.

Опция WITH RECOMPILE требует перестроения плана процедуры перед данным выполнением

Вызов хранимой процедуры. Пример

Вызов процедуры S_Title, выводит названия книг (поле Title) из таблицы Titles, относящиеся к определенному разделу знаний

-- @q - возвращаемый статус

-- @t - входной параметр

declare @q int

exec @q = S_Title @t = ‘busin‘ -- /* ключевая форма*/

-- т.к. @t char(2), то будет передан параметр ‘bu‘

-- или

exec @q = S_Title ‘busin'

-- при позиционной форме передачи параметров

select @q -- по умолчанию 0

Задание. Выполнить процедуру ProcAuthorsCity,

возвращающей количество и все сведения об авторах находящихся в том же городе, что и автор с заданным au_id

Declare @к int, @ReturnCode int

execute @ReturnCode = ProcAuthorsCity '274-80-9391', @к output -- факт. параметры

select @к As [К-во], @ReturnCode As [Код возврата]

-- результат вызова

au_id au_lname au_fname phone

----------- ----------------------------------------

213-46-8915 Red Marjorie 415 986-7020 309

274-80-9391 Straight7 Dean 415 834-2919 5420

К-во Код возврата

----------- ------------

5 0

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

Для сохранение строк, создаваемых хранимой процедурой, в таблице БД используется оператор Insert – добавления строк в существующую таблицу:

INSERT [ TOP (< выражение >) [ PERCENT] ]

[INTO] < табл./представл ение>

[(< список столбцов >) ]

{ VALUES ({ DEFAULT | NULL | < выражение >}[,…]) |

EXECUTE (< строковое выражение >) |

EXECUTE < имя хранимой процедуры>......

}

Изменение хранимой процедуры

Для внесения изменений в текст процедуры:

F оператором DROP удалить ее из БД, а затем повторно создать оператором CREATE PROCEDURE. При этом придется повторно задавать разрешения на исполнение.

F Оператор ALTER PROCEDURE или редактор процедур в Management Studio