ограничением, и вате при;10жение не оулет контролировать, какое сооощеиие увидит пользователь. В случае удатеинй, которые нарушают ссылки внеиии].\ тючей, триггеры иа удаление тшкогда ие обработают эти удаления, поскольку они будут откачены ограничение.м до того, как триггер их увпдит. Хотя это вовсе не означает, что в своих приложениях баз данных вы должны использовать только один из этих методов для обеспечения ссылочной целостности - большинство CHCTe.vi используют их вместе. Нет ничего необычного в тол], что декларативная ссылочная целостность составляет львиную до.тю с.хе.мы, с дополнительным использованием триггеров и хранимых процедур, где это необходимо.

Внешние ключи (foreign keys)

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

Ссылочные действия ANSI (ANSI Referential Actions)

Спецификация ANSI SQL-92 определяет четглре воз.можных де11ствня, когда осуществляется попытка модификации данных; N0 ACTION, SET NULL, SET DEFAULT и CASCADE. Из них только первое, N0 ACTION, напрямую по.тдерживает SQL Server. Напри.мер, если вы попытаетесь выполнить удалетиге или .модификацию, которая может нарушить ссылку внешнего ключа, SQL Server отменит это из.\[ене-ние и прервет ко.маиду - конечный результат ваишх .модификаций - N0 ACTION.

Хотя SQL Server не поддерживает папря.мую остальные три ссылочных действия, вы можете реализовать их с по.мощью храни.\[ых процедур или триггеров. Триггеры, например, очень удобны для реализащн! каскадных удалений или .модификаций. Хранимые процедуры хорошо применять для реатизации действий SET NULL и SET DEFAULT, поскольку триггеры ие .могут напрямую из.менять модифицируемую запись.

Исключение, связанное с NULL

SQL Server строго выполняет ограничения внешних к/ночей, с 0Д1И1М примечательным исключением. Если столбец в подчиненной таблице может принимать зпачеиие NULL, это значение доиусти.мо независимо от того, есть ли в основной таб;пще запись со значением NULL. В этом смысле NULL обходит механизм orpaini-чения ссылочной целостности SQL Server. Это станет понятнее, если вы будете ду.мать о NULL, как о значении, которое пропущено, а не как о действительном значении столбца.

Требование наличия уникального индекса

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

Даже при to.vi, что default-oгpaн^шeИIШ не могут содержать подзапросы, они могут быть достаточно сложными. Вот пример, в которо\[ определяется default-ограничение, которое устанавливает случайное число в качестве значения но у.молчанию:

CREATE TABLE #rand (kl int identity, cl float DEFAULT (

(CASE (CAST(RAND()-,5 AS int)*-l) WHEN 0 THEN I ELSE -I END)*(CAS! (RAND()

100000 AS -int) % 10000)*RAND()

INSERT #rand DEFAULT VALUES INSERT #i and DEFAULT VALUES INSERT #rand DEFAULT VALUES INSERT #rand DEFAULT VALUES INSERT Irand DEFAULT VALUES INSERT #rand DEFAULT VALUES INSERT ffrand DEFAULT VALUES INSERT Irand DEFAULT VALUES INSERT #rand DEFAULT VALUES INSERT #rand DEFAULT VALUES

SELECT * FROM #rand

kl cl

1 -121,89758452446999

2 -425,61113508053933

Невозможность использования TRUNCATE TABLE

Наличие в таблице ограиичетпи ! по внетпиему ючючу препятствует использованию TRUNCATE TABLE. Это справедливо незазисн.мо от того, нарушит ли удаление записей связь, установленную вненп1Т1.\1 ключо.м. Заппсп, удаляемые TRUNCATE TABLE, не записываются в журнал транзакций, так что становятся невоз.можны.\П1 любые операции, ориентированные на записи (нанри.мер, проверка ограничений внешних ключей). По этой же причине (так как TRUNCATE TABLE оперирует целыхнт страница\нт, а не отдельными записями) TRUNCATE TABLE па.мпого быстрее DELETE.

Default-ограничения

Default-ограничення устанав;ншают значентш столбцов по y.v[oлчaнпю. Это .может быть больше, чем просто зиачетитя-коистанты, - они .\югут состоять из CASE-выражений, функций и других типов скалярных выражентн ! (но не подзапросов);

CREATE TABLE #testdc (cl int DEFAULT CASE WHEN SUSER SNAME()-JOE THEN I ELSE 0 END) INSERT #testdc DEFAULT VALUES SELECT * FROM #testdc cl

3 3918.15546838766/5

4 9335.2668236:734:2

5 54.46389C64C027664 5 -5.0169085346410522

7 -5430.63417246276

8 915,9835973796487

9 28.109161998753301

10 741.79452047043048

Выр;1жеине (CASE (CAST(RAND()-F.5 AS int)*-l) WHEN 0 THEN 1 ELSE -1 END) делает случайным знак генерируемого числа, разрешая и положительные и отрицательные значе1иш, тогда как выражение (CAST(RAND() * 100000 AS int) % 10000)*RAND() генерирует целое значение от О до 9999.

Применение этих экзотических выраженн!! не ограничено числовы.чт столб-ца.\ш. Вы можете определять запутанные выражения но з'молчанию также и для других типов столбцов. Вот пример, в которо.м определяется случайное зиаче!Н1е для числового столбца и случайная строка для столбца типа varcliar:

CREATE TABLE #rand (kl int identity, cl float DEFAULT (

(CASE (CAST(RAND()-.5 AS lnt)*-l; WHEM 0 THEN 1 ELSE -1 LND)*(CAST(RAND() *

100000 AS mt) % lOOOORAND)

c2 vanchar(30) DEFAULT REPLICATE(

CHAR((CAST(RAND() * 1000 AS Int) % 26) - 97) -bCHAR((CAST(RAND() * 1000 AS mt) X 26) + 97) +CHAR((CAST(RANDO * 1000 AS Int) % 26) + 97j +CHAR((CAST(RAND() * 1000 AS mt) % 26) + 97) +CHAR((CAST(RAND() * 1000 AS int) X 26) + 97). (CAST(RAND() * 100 AS int) X 6)-1)

kl cj c2

1 643.18693338310379 mhbxmmhDxm

2 4836.4599252204198 yagrfyagrf

3 5720.9159041469775 hxqnphxqnphxqnphxqnp

4 370.00067169272609 fidbmfldbm

5 3952.0816961988294 gpmcn

6 5106.5869548550918 lekyhieKyhiekyhiekyh

7 -3909.4806439394761 asgdw

8 1416.8140454855652 pweudpweuapweudpweud

9 -3440.4833748335254 xtojg

10 44.783535689721887 yiymb

Стоит обсудить этот метод создания зиачернш по умолчанию столбца типа varcliar. Все начинается с создания строки из пяти си.мволов в нижнем регистре (операция %26 возвращает число между О и 25, тогда как 97 - это А5СП-код буквы а, добавляя это полученное число к 97 и преобразуя его к снмвольно.му значению, мы получае.м си.мвол между а и z). Затем эта строка из пяти си.мволов копируется от 1 до 6 раз (операция %6 возвращает значение между О и 5, которое мы затем увеличивае.м на 1) для создания строки с максимальной длиной 30 символов - определенная нами длина столбца.

Удаление объектов

Хотя это и не документировано, вы можете уд;ыять несколько объектов заданного типа одновременно с номоптью соответствующей команды DROP. Например, чтобы уда^тить несколько таблиц, вы .можете использовать единственную команду DROP TABLE, указав удаляемые таблицы через запятую. Этот метод .\южно также прн.менять для .хранимых процедур, нредставлений и других типов объектов:

USE tempdb GO

CREATE PROC ftestl as

SELECT 1

CREATE PROC #test2 as

SELECT 2

CREATE PROC #test3 as

SELECT 3

DROP PROC ftestl. #test2. #test3 GO

CRLATE VIEW testl as

SELECT 1 1

CREATE VIEW test2 as

SELECT 2 2

CREATE VIEW test3 as

SELECT 3 3

DROP VIEW testl, test2. Test3 GO

CREATE INDEX

Существует несколько ограничений, связанных с нндекса\н1 SQL Server, о которых стоит рассказать. Это разухшые ограничения, и о нн.х вы должны похн-гить при проектировании своих баз данных.

Невозможность создания индексов по столбцам типа Bit или BLOB

Во-первых, вы не можете создавать индексы по столбцам типа bit, text, ntext или image. За исключением типа bit, все это BLOB-типы данных, поэтому, согласно логике, вы не можете создавать по ни.м обычные индексы (о создании BLOB-индексов см. главу 18, Полнотекстовый поиск ). Причина, по которой нельзя создавать индексы по столбца.м типа bit, также очевидна. Предназначение индекса - поиск записи в таблице. SQL Server строит сбалансированные деревья (В-деревья), используя уникальные значения данных индекса. Если столбец

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

Чтобы до конца понять, почему столбец только с дву.мя различны\И1 значе-ния\и1 бесполезен в качестве ключа индекса, представьте, что вы - частный сыщик и должны найти иропавщего человека, и все, что ва.м известно, это его иол. Половина людей .мира будет соответствовать это.му описанию. Это очень .vnioro пропавщих людей!

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

Другое ограничение SQL Server, связанное с индекса.\П1, - невозможность создания индексов по вычисляемым столбца.м'. SQL Server ие позволяет создавать индексы по вычнсляе.мы.м столбцам, так как вычисляемые столбцы на самом деле пе существуют в базе данных - они не храпят реа.тьных данных. Вычисляемый столбец в таблице подобен столбцу в представлеиин - они оба создаются по запросу, иначе ofHi не существуют. Поскольку не существует постоянных данных для индексирования, индексы по вычисляемым столбца.м запрещены.

PAD INDEX

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

IF INDEXPROPERTY(0BJECT lD(tUles).type1nd.-lsClu5tered) IS NOT NULL DROP INDEX titles.typeind

CREATE INDEX typeind ON titles (type) Wllh PAD 1NDEX, FILLFACTOR = 10

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

DROP EXISTING

В SQL Server 7.0 больше не поддерживаются такие опции ко.манды CREATE INDEX, как SORTED DATA и SORTED DATA REORG. В.место них появилась новая опция DROP EXISTING. DROP EXISTING позволяет вам удалить и снова создать индекс за один щаг. DROP EXISTING предоставляет специальные улучшения производительности для кластерных индексов, поскольку зависи.мые от них некластерные

Помните, что речь идет об SQL Server 7.0. В SQL Server 2000 можно создавать индексы гго иычисляе.чгы\г столбнам, правда, с пекоторы.чги офаиичения.чги. - Примеч. перев.

индексы перестраиваются только один раз и только в том случае, когда изменяются значения ключа кластерного индекса. Если данные уже отсортированы в правильном порядке, DROP EXISnNG не пересортировывает данные, но сжимает их с иснользование.м текущего значения FILLFACTOR (предоставляя такую же основную функциональность, как и старая опция SORTED DATA REORG).

Так как пересоздание кластерного индекса и зависящих от него неютастерных индексов с по.мощью DROP EXIST[NG выполняется за один таг, эта операция по своей сути ато.марна - либо все индексы будут созданы, либо ни один из них. В случае набора операторов DROP INDEX/CREATE INDEX для осуществления этих же действий вся операция должна быть заключе}1а в транзакцшк

Временные объекты

SQL Server поддерживает два типа временных объектов - локальные временные объекты и глобальные временные объекты. Локальные имеют префикс ггз одного символа #; глобальные - из двух ##.

Неиспользуемые временные объекты больше не существуют

Начиная с SQL Server 7.0, команды CREATE VIEW, CREATE DEFAULT и CREATE RULE больше не поддерживают создание временных объектов. До версии 7.0 вы .\к)гли создавать эти объекты, но не могли ничего с ни.ми сделать - это не слишком полезно. Теперь ситуация исправлена, так что, чтобы создать представление, значение по у.молчанию или правило в tempdb, вы должны сначала сменить контекст текущей базы данных на tempdb, а затем выполнить соответствующую команду CREATE.

Невозможно создавать объекты в других базах данных

Помимо сказанного, эти три оператора CREATE не позволяют использовать квалифицированные названия объектов - название, которое вы указываете, должно быть неквалифицированным, состоящим из одной части идентификатором объекта. Если вы хотите создать объект в tempdb, необходимо снача^та переключить контекст баз данных. Конечно, смена базы данных на tempdb для создания объекта означает, что вы должшл будете полностью квалифицировать объекты, на которые ссылаетесь, и которые находятся где-то в другом месте. Это ограничение не касается команды CREATE TABLE, которая напрямую поддерживает создание объектов в других базах данных.

Временные хранимые процедуры

Как и в случае таблиц, вы .можете создавать вре.менные хранимые процедуры, добавляя к названию процедуры префикс #. Вы можете создавать глобаль}1ые вре.менные процедуры, добавляя к их названию ##. Эти храни.мые процедуры мож-

Временные объекты 115

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

Увеличена длина названий временных таблиц

До версии 7.0 SQL Server выдавал ошибку, если вы пытатись указать локальную временную таблицу с длиной названия более 20 сн.мволов. Это было исправлено, и теперь названия локальных временных таблиц могут быть длиной до 116 си.мволов.

Глобальные временные статусные таблицы

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

SET NOCOUNT ON

DECLARE @statusid int

CREATE TABLE ##jobstatus

(statusid int identity.

start datetime.

finish datetime NULL.

description varchar(50),

complete bit DEFAULT 0)

INSERT ##jobstatus VALUES (GETDA1E().NULL,Updating Index stats for pubs.Oj SET (astatusid=№IDENTITY PRINT

SELECT description AS JOB CURRENTLY EXECUflNG FROM ##jobstatus WHERE

statusid=@statusid

EXEC pubs..sp updatestats

UPDATE ##jobstatus SET finish-GErDATE(). complete=l WHERE statusid=@statusid

INSERT ##iobstatus VALUES (GETDATE{).NULL,Updating index stats fon nonthwind.0) SET (astatusid=(a(aiDENTITY PRINT

SELECT descniption AS JOB CURRENTLY EXECUTING EROM ##jobstatus WHERE

statusid=(astatusid

EXEC nonthwind sp updatestats

UPDATE #jobstatus SET finish=GETDATE(), ccmplete-1

WHERE statusid=(astatusid

SELECT * FROM ##jobstatus

DROP TABLE ##jobstatus (результаты сокращены) JOB CURRENTLY EXECUTING

Updating index stats fon pubs

Updating dbo.authons

Updating dbcpublishens

Updating dbo,titles

Updating dbo.employee

Statistics for all tates have been updated.

JOB CURRENTLY EXECUTING

Updating index stats for northwind

Updating dbo,employees Updating dbo.categories Updating dbo.customers

Statistics for all tables have been updated.

Sid start finish description complete

1 1999-07-24 16:26:40 1999-07-24 16:26:49 Updating index stats for pubs 1

2 1999-07-24 16:26:41 1999-07-24 16:26:49 Updating index stats for northwind 1

Именование объектов и зависимости

Неквалифицированные и.мена объектов разрешаются с номошью следующего процесса:

1. SQL Server проверяет, являетесь лн вы владельцем объекта с указанным и.ме-нем в текущей базе данных.

2. Затем он проверяет, является лн пользователь DBO влщгелъцем таблицы с ука-занны.м и.менем в текущей базе данных.

3. Если указанный вами объект и.меет префикс #, сервер проверяет, являетесь ли вы владельцем лoкaJьнoй временной таблицы или пронедуры с этн.м именем.

4. Если указанный вами объект имеет префикс ##, сервер проверяет, существует ли глобальная временная таблица или процедура с таким именем.

5. Если название объекта начинается с sp и вы используете его в допусти.мо.м контексте для хранимых процедур, сервер снача-та проверяет текущую базу данных, а затем - базу данных master, чтобы определить, являетесь ли вы или DBO владельцем таблицы с указанным именем.

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

Временная смена контекста базы данных

Вы можете временно сменить контекст базы данных, в котором будет выполняться системная хранимая процедура, добавив при вызове к ее названию название базы данных, в которой вы хотите ее выполнить. То есть, даже если процедура находится в базе данных master, вы .можете работать с ней, как если бы она находилась в другой базе данных, как показано здесь: EXEC pubs.,sp spaceused

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

Индексы временных таблиц

Благодаря огложе111ю.\1у разрешению имен в SQL Server 7.0 вы .можете теперь ссылаться на индексы В1)еменных таб.ищ по их названию в пределах хранп.чю!! процедуры, которая создает их. В версии 6.5 и более рапиих вы должны были ссылаться на них ио но.ме)у. Так как в SQL Server 7.0 названия объектов не преобразуются в их впутрениие идентнс1)икаторы до тех (юр, пока процедура не будет запущена, теперь вы можете ссылаться па индексы вре.мениых таблиц по названию, тем же самы.м способом, как иа индексы постоянных таблиц.

Опасайтесь представлений^ которые нельзя использовать

Существует небольшая CTpaiHiocTb ко.манды CREATE VIEW SQL Server, из-за которой вы .можете со.здавать представления по таблииа.м, к которым у вас пет доступа. Никакое сообщение об ошибке не генерируется, и кажется, что операция CREATE VIEW отработала за.мечательно. Однако ошибка возникает при попытке (юлучнть доступ к тако.му представлению, так что оно в основно.м бесполезно. Поскольку во вре.мя ко.\1пиляцин не генерируется сообщение, стоит проверить, даны ли достаточные права на объекты, используемые в представлении, прежде чем запускать его в работу.

Зависимости объектов

Механизм зависимостей объектов SQL Server (который исгю.аьзует храии.мую процедуру sp depends) са.м по себе несовершенен, так что вы не должны полагаться на него для получения точной инфор.\шции о зависи.чюстях. Оригинальная идея заключалась в то.м, чтобы зависи.мости объектов хранились в таблице sysdepends в каждой базе данных. Тем са.мым гарантировалось, что ин(зормация о зависимостях является полной и доступной. К несчастью, все это не работает так, как надо. Данный .vrexannB.M и.меет .множество фунда.меитальных недостатков. Среди них;

О не отражаются объекты вне текущей базы данных;

О если объект с зависи.мы.мп oбъeктa.vиl удаляется, ипфор.мация о его зависимостях удаляется в.месте с ним;

О пересоздание объекта, который и.меет зависи.мые объекты, не восстанавливает и пе пересоздает информацию о его зависимостях;

О из-за отложенного разрешения и.меи SQL Server вы увид1гге и1и})ор.маиию о зависи.мостях только для тех объектов, которые иа са.мом деле существовали, когда объект был создан;

О единственный сгюсоб обеспечить актуальность информации в sysdepends -периодически удалять и пересоздавать объекты в базе данных в порядке их завис!Ьмостеп.

Лично .мне этот .механиз.м всегда казался небрежным - как чья-то за1юзда-лая мысль, которую кто-то включил в продукт пря.мо перед поставкой, не проверив ее должным образом. Лучше избегать использования sp depends. Это касается

и отчета о зависимостях объектов в Enterprise Manager. On так же ненадежен как и sp depends.

Заключение

в 3t01i главе изложены различные советы, реко.мендации и приемы, связанные с DDL. Некоторая информация и некоторые методы, представленные здесь, довольно обыкновенны, другие менее очевидны. За исчерпывающей пнфор.ма-цией по DDL Transact-SQL обратитесь к Books Online.