ных, тогда как Unknown представляет логическое зпачеиие. Эта разница не,\шого трудна для понимания - особенно д.чя разработчиков-ветеранов, - и это причина, ио которой вы должны использовать ...WHERE столбец IS NULL, в.место ...WHERE столбец = NULL, если хотите, чтобы SQL вел себя разумно. (Transacl-SQL не запрещает такой! С!!нтакс1!с, но, поскольку одно значение NULL !И!К0сда не рав1!0 другому - даже ca.MO.viy себе, он )!пко!да не вернет True. Смотрите раздел ниже о работе с ANSI NULL в Transact-SQL.) Какп.м бы смещным это ни показалось, я пе хочу пускаться в философские рассужде1!ия относительно NULL-значений и их прав1гльпого иснользован1!я. Поскольку наи!а задача состоит в том, чтобы увидеть .мир данных и баз данных глазами Transact-SQL, я вс!Оду в книге буду сч1!тать значения NULL и Unknown тождественны.ми.

Рис. 3.1. Таблицы истинности трехзначной логики

NULL и выражения

Как 1!равило, применение NULL-з)aчeния в выражении !1риводит к результату, равному NULL. Например, SELECT 5-i-NULL вернет NULL а не 5, так же как и SELECT SUBSTRING(Groucho, 3, 2-i-NULL). Искл!оче!1ием из это!0 правила являются агрегатные функции.

Также NULL-значения ги1К0!-да не равны друг дру!-у; фактически NULL-значения даже не равны са.ми себе, что проиллюстрировано в следующем запросе:

CREATE TABLE Inulltest

(cl int NULL)

INSERT #nulltest VALUES (1) INSERT fnulltest VALUES (NULL) INSERT #nulltest VALUES (3)

DE.CLARE gnv irU

SELECT @nv=cl FROM #nuMtesl WHERE cl IS NULL -- Получаем NULi -/n второй запися SELECT MyN\/=cl FROM #njlltest WHERE cl=ianv -- He вернет ни одной записи

NULL и функции

Как и в случае простых выраженирй, при передаче большинству функции NULL-значенин результатом будет NULL, так что SELECT SIGN(NULL) вернет NULL, так же как и SELECT ABS(NULL) и SELECT LTRIM(NULL). Исключение составляют функции, специально предназначенные для работы с неопределенными 31шчениями. В до-полнегиге к агрегатным функциями, это функции ISNULL() и COALESCE().

ISNULLO преобразует NULL-зиачение к значению, отличному от NULL. Например,

SELECT ISNULL(cl.O) FROM #nulltest

преобразует все NULL-значения в столбце с1 к 0. Этот подход нужно использовать осторожно, пото.му что преобразование NULL к други.м значешшм может иметь побочные эффекты. Например, запрос с AVG в следующем примере не сможет проигнорировать преобразованные NULL-значения:

SELECT AVG(ISNULL(cl.O)) FROM InuHtest

Нулевые значения применяются при вычислении среднего, сильно понижая его значение. За.метьте, что параметры ISNULL() не ограничены константами. Рассмотрим пример:

DECLARE @х int.gy int SET @х=5 SET (ау=2

SELECT ISNULLCCASE WHEN %x>=\ THEN .JLL ELSE @x END. CASE WHEN (ay<5 THEN @x*@y ELSE 10 END)

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

ISNULLO в качестве параметра поддерживает даже оператор SELECT, как здесь:

DECLARE @х int.@y int SET (ах=5 SET (ау=2

SELECT ISNULLCCASE WHEN (ах>=1 THEN NULL ELSE @x END. (SELECT COUNT(*) FROM authors))

Функция NULLIFO - противоположность ISNULL(). Хотя она и пе обрабатывает переданные ей NULL-значення лучше любой другой функции, она была разработана для возвращения NULL-значення в правильных ситуациях. Она принимает два параметра и возвращает NULL, если они равны; иначе она возвращает первый пара.метр. Например,

DECLARE (Эх int.(ay int SET Ш'Ъ SET (ау=2

SELECT NULLIF(@x.gy*3)

возвращает NULL, тогда как SELECT NULL!F(Сад. gy)

вернет 5.

COALESCEO возвращает первое значение из горнзонта.тьного списка, не равное NULL:

SELECT COALESCE(@х / NULL, (?х NULL. Ox+NULL. NLIL. :/*2. g.<, (SELECT COUNTS) FROM autnors))

вернет @y*2 или 4. Как и в случае ISNULL(), иара.метра\И1 COALESCE() .Moiyr быть выражения и подзапросы, а также константы, как было показано в при.мерах.

NULL и ANSI SQL

с каждой успешной версией SQL Server все по.тнее соответствует стандартам ANSI/ISO. Используя множество настроек конфнгуращп! и современный синтаксис команд, вы можете писать Transact-SQL, переносимый иа другие ANSI-совместимые СУБД.

NULL-зпачения представляют co6oii область, в которой соответствие ANSI значительно улучшилось в версии 7.0. Для уветичения совмеспьмости было добавлено множество новых иара.метров настройки конфтурации и опций синтаксиса. Многие из них мы обсудим датее.

Что касается обработки неопределенных значений в выражениях, спецификация ANSI/ISO SQL корректно разделяет агрегацию и просто вычисление выражений (это противоречит инфор.\шцин в нескольких других замечательных книгах по SQL). Это означает, что в соответствии со стандартом, добавление NULL-значения к числу - это не то лее са.мое, что вычисление агрегатного значения столбца, который содержит и NULL и ne-NULL-значения. В первом случае результатом всегда будет неопределенное значение. Во втором NULL-значения будут проигнорированы и агрегация будет осуществлена. В соответствии со спецификацией ANSL агрегатная функция .может вернуть NULL только если таблица пуста или ие содержит ничего, кро.ме NULL-значеиий в агрегируемом столбце (COUNTO представляет собой исключение - с.\ютрите ниже). Так как в этом случае Transact-SQL следует стандарту, эти положения также ирпмеии-.мы и к нему. Например, рассмотри.м таб.тицу из начала главы:

CREATE TABLE fnulltest (cl int NULL)

и следующие данные:

INSERT Inulltest VALUES (1) INSERT Inulltest VALUES (NULL) INSERT #nulltest VALUES (3)

Запрос:

SELECT AVG(cl) FROM fnulltest

lie возвращает NULL, даже при то.м, что одно из обрабатываемых и.м значений действительно равно NULL. B.viecTO этого NULL игнорируется при вычислении среднего значения, что нам как раз и требуется. Так же дела обстоят и с функциями SUMO, MIN() и МАХ О, но не в случае COUNT(*).

Например,

SELECT COUNTC*) FROM #nulltest вернет 3, так что SELECT SUM(cl)/COUNT(*) - это не то же самое, что SELECT AVG(cl). COUNT(*) подсчитывает записи, не обращая внимания на неопределенные значения. Она включает вторую запись таблицы даже при том, что таблица имеет всего один столбец и значение этого столбца во второй записи - NULL. Если вы хотите, чтобы поведение этой функции было таким же, как и у других агрегатных функций, укажите вместо * столбец таблицы (например, COUNT(cl)). Этот синтаксис корректно ипюрирует неопределенные значения, так что SELECT SUM(cl)/COUNT(cl) возвращает такое же значение, что и SELECT AVG(cl).

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

По умолчанию, если вы соединяетесь с сервером посредством ODBC или OLEDB, установка SQL Server ANSI WARNINGS включена. Это означает, что сервер будет генерировать предупреждающие сообщения для всех запросов, в которых неопределениые значения игнорируются агрегатами. Здесь не о чем волноваться, если вы знаете о своих неопределенных значениях и они должны игнорироваться, однако в другом случае эти сообщения могут предупредить вас о проблемах с данными.

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

Другие важные настройки, связанные с обработкой ANSI NULL, - SET ANSI NULL DFLT ON/ OFF, SET ANSI NULLS и SET CONCAT NULL YIELDS NULL SET ANSI NULL DFLT ON/ OFF определяет, могут ли столбцы в новой таблице по умолчанию содержать NULL-значения. Вы можете получить значение этой настройки с помощью систе.мной функции GETANSINULL().

SET ANSI NULLS контролирует, как работает сравнение на равенство с NULL В стандарте ANSI SQL предусмотрено, что любое выражение, содержащее операторы сравнения (=, о, >= и так далее - тета -операторы, говоря языко.м Кодда) и NULL-значения, возвращает NULL Отключение этой настройки (она включена по умолчанию при соединении с помощью ODBC или OLEDB) разрешает возможность успешного сравнения на равенство с NULL

SET CONCAT NULL YIELDS NULL определяет, будет ли конкатенация строк с NULL возвращать NULL Обычно SELECT Rush Limbaughs IQ= -i-NULL возвращает NULL, но это можно отключить с помощью команды Transact-SQL SET CONCAT NULL YIELDS NULL. Обратите внимание, что эта настройка не влияет на другие типы значений. Сложение NULL с числовым значением всегда возвращает NULL независимо от CONCAT NULL YIELDS NULL

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

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

Хранимые процедуры - это то место, где особенно удобно контролировать поведенне Transact-SQL, касающееся ANSLcobmccth.mocth. Рассмотри.м следующую хранимую процедуру:

CREATE PROCEDURE ListldsByVaije gval 1nt AS

CREATE TABLE lvalues (kl int identity, cl int NULL)

INSERT lvalues (cl) VALUES (1)

INSERT lvalues (cl) VALUES (1)

INSERT lvalues (cl) VALUES (NULL)

INSERT lvalues (cl) VALUES (9)

SELECT * FROM lvalues WHERE cbiava:

DROP TABLE lvalues

Несмотря на то что во временной таблице есть записи, в которых столбец с1 равен NULL, если передать NULL в качестве единственного параметра, процедура не вернет ни одной записи, так как одно значение NULL никогда не равно друго.му. Конечно, в хранимой процедуре .можно предусмотреть специальную обработку неопределенных значений, ио этот подход очень быстро становится ненадежным по мере увеличения количества процедур с большим количество.м параметров. Например, процедуре всего лишь с двумя пара.метрами, которые могут принимать неопределенные значения, потребуется оператор IF с четырьмя уровнями вложенности, и код, необходимый для выполнения запроса, будет размножаться. Однако благодаря SET ANSI NULLS это поведенне можно изменить, вот так:

SET ANSIJULLS OFF GO

CREATE PROCEDURE ListldsByValue @val int AS

CREATE TABLE lvalues (kl int identity, cl int NULL)

INSERT lvalues (cl) VALUES (1)

INSERT lvalues (cl) VALUES (1)

INSERT lvalues (cl) VALUES (NULL)

INSERT lvalues (cl) VALUES (9)

факт что вы можете присваивать значения столбца.м с по.мощью =NULL, но не можете искать значения с помощью того же синтаксиса. Например,

UPDATE authors SET state=NULL WHERE state=CA сопровождаемы!!

SELECT * FROM authors WHERE state=NULL не работает так, как вы могли бы ожидать. Оператор SELECT пе вернет ни одной записи, даже когда несколько из них были только что установлены в NULL. То, что одно значение NULL не равно друго.му, представляет собой синтаксическую несогласованность. Я считаю, что стандарт мог бы быть более логичным, если бы присваивание осуществлялось таким образо.м:

UPDATE authors SET state TO NULL WHERE state=CA

Если бы это было разрещено, запрет =NULL был бы разумнее, но, увы, это не так.

SELECT * FROM lvalues WHERE cl=?val

DROP TABLE lvalues

SET ANSIJULLS ON GO

Тем самым мы изменяем жизнеспособность расширения =NULL Transact-SQL па период выполнения процедуры. Под жизнеспособностью я подразумеваю, что, помимо того что код не генерирует ошибки, синтаксис работает, как и предполагается. Хотя этот синтаксис технически правилен, независимо от SET ANSI NULLS, он никогда не вернет True, если включена совместимость с ANSL Как вы можете предположить из этого кода, это расширение значительно упрощает обработку параметров хранимых процедур, которые .vroiyr принимать значегше NULL, - вот основная причина, по которой эта конструкция была добавлена в язык.

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

SET ANSI NULLS OFF GO

EXEC ListldsByValue ?val=NULL GO

SET ANSIJULLS ON GO

не вернет ни одной записи, если ANSI NULLS не была установлена в OFF, когда процедура была откомпилирована. Учтите, что SET ANSI NULLS также влияет на жизнеспособность синтаксиса IN (значение, значение, NULL). Это означает, что запрос, подобный этому:

SELECT * from lvalues where (cl in (1. NUL))

не вернет записи с NULL-значеииями, если только ANSI NULLS не была отключена. Это становится понятным, если считать предикат IN короткой записью нескольких сравнений на равенство, объединенных по OR.

ПРИМЕЧАНИЕ -- -

Я не поощряю ненужные отклонения от спецификации ANSI/ISO SQL. Всегда лучше писать код, со-ответавующий стандартам, независимо от синтаксических возможностей вашей разновидности SQL. ANSI/ISO-совместимый код позволяет переносить его на другие платформы, кроме того, он легче воспринимается другими людьми. Как и в случае использования неопределенных значений, вы должны несколько раз подумать, прежде чем писать код, отклоняющийся от установленных норм, особенно при одновременной работе с несколькими СУБД.

Используйте NULL-значения, если это необходимо

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

Используйте NULL-значения, если это необходимо 105

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

CREATE TABLE #va !jes (<1 int identity, ci int NULL! INSERT lvalues (cl) VALUES (1) INSERT lvalues (cl) VALUES (1) INSERT lvalues (cl) VALUES (NULL) INSERT lvalues (cl) VALUES (9)

можно иоду.мать, что запрос

SELECT * FROM lvalues WHERE cl=i сопровождае.мый

SELECT * FROM lvalues WHERE cl<>i

вернет все записи из таблицы #values, (однако это не так. Помните, что SQL основан на трехзначной логике. Чтобы получить все записи, мы должны учесть неопределенные значения, так что необходи.мо что-то вроде SELECT * FROM lvalues WHERE cl=l OR cl IS NULL

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

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

Переменная, которой не присвоено значение, - это ие го же са.мое, что пере-.менная, содержащая NULL; если предположить, что это одно и то же, можно получить некорректные результаты. Также несколько серверов баз данных, не говоря уже о традицио!П1ЫХ языках програм.мировапия, реализуют поведение NULL полностью в соответствии с ANSI SQL, и разница между сиособа.ми обработки NULL-зиачений в различных ко.мионентах приложения может привести к путшгице.

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

Скаже.м справедливости ради, что неопределенные значения - это необ.ходимое зло во многих случаях. Точные вычисления быстро становятся чрез.мерно сложными, если нет пря.мой поддержки неопределенных значе}1ий.

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

Один приемлемый метод для избежания исгюльзовання NULL-значеннй - применение фиктивных значений для обозначения отсутствующих данных. Напри-\[ер, вместо NULL в символьных столбцах .\[Ожно при.мегнгть строки N/A или NV . Можно использовать -1 для указания на отсутствующее значение во .многах столбцах типа integer, 1900-01-01>> можно задействовать для лат, и так далее. В этих случаях удобг[0 применять функц[по NULLIF(), особенно при работе с агрегатными функцня.ми. Допустим, чтобы SUM() пропгнорнрова;[а числовые столбцы, содержащие -1, вы можете выбрать что-то вроде SELECT SUM(NULLIF(cl, -1)), потому что SUM() игнорирует NULL-3Ha4e[[vm. Вы можете создать подобные выражения и для обработки фиктивных значений других типов.

Мораль этой истории такова: NULL - это как криптоннт' для Супер.мена - он высасывает жизнь из всего, что оказывается рядо.м. Используйте NULL-значения, если ва.м это необходимо, но по возможности избегайте их.

Слово крннтонит иперпыс появилось в одной из ралиосе|)11Й о Супермене в 1943 году. Сунер.меи практически неуязвим, ему не ст)ашсп град пуль, iro перед криптоннтовой радиацией он бессилен. - Примеч. перев.

Внутренняя организация DDL

Если бы автомобильная индустрия развивалась темп же TCMiiaNHi, которыми разв>и!алась последние тридцать лет компьютерная индустрия, Rolls Royce стоил бы $2,50 и потребля;! бы литр бензина па миллион километров.

Эрб Грош

Но СП был бы игрушечного раз.мера п ломгыся бы каждые три дня. Остерегайтесь лож1П)1.х аналогт ].

Джо Селко

Авторы не ставили задачу полностью охватить в данной главе Transact-SQL DDL (Data Definition Language, язык определения данных) - это уже сделано в Books Online (BOL). Поскольку это не руководство по синтаксису, мы не будем пытаться полностью описать каждую DDL-ко.манду Transact-SQL.

Глава представляет co6()ii собрание подсказок, реко.мендаций и советов, касающихся различных те.м, связанных с DDL. Задача этой главы - дополнить Books Online, а не за.менить. Цель этой главы - заполнить некоторые пробелы в описании DDL в BOL и раскрыть некоторые темы, связанные с DDL, которые могут пригодиться в дальнейше.\г

Одна из сложностей, связанных с иаписаиие.м книг, подобных этой, - пе повторять то, что уже написано в доку.меитапии производителя, и при этом сделать издание достаточно полезным для читателей, чтобы они не жалели потраченных денег. Интерактивная доку.ментация SQL Server всегда была одной из его сильных сторон. Я предпочитаю се доку.ментащи! других производителей СУБД, с которыми регулярно работаю. Ее содержание настолько полно, что довольно сложно описать такие сравнительно обычные те.мы, как DDL, если автор хочет создать свежую, оригинальную работу. Короче говоря, множество тем уже очень хорощо раскрыто в Books Online, и, вместо того чтобы перефразировать уже сказанное, я постараюсь потратить ограниченное количество страниц этой книги на то, что ие описано в доку.ментапии.

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

TransacL-SQL, как CREATE TABLE. CREATE INDEX. ALTER TABLE и CREATE PROCEDURE. Данные ко.манды нлюют множество нюансов и ocooeiiHOcreii, которые необходн.мо знать, чтобы полностью понн.мать их.

CREATE TABLE

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

Некоторые мысли по поводу ссылочной целостности

Лучше прн.менять ссылочную целостность (RI, referenLial integrity), че.м использовать триггеры, а в.место храни.мых процедур предпочтительнее зaдeiicтвoвaть триггеры, но всему свое .место. Прн.менепие декларативной ссылочной целостности обычно прелтпочтительпее, так как она проста в испо;н>зоиангн1 и за счет ее выбора устраняется риск ошибок в трипсрах и хран1ьмых процедурах, которые могли бы нарушить целостность данных. К то.му же, декларативная ссылочная целостность обтлчно быстрее аи;ьтопшного трип-ера, поскольку она проверяется до вьнюлнения соответствуюшнх нз.менепнй. В отлнчне от нее, трштеры запускаются ш)сле записи изме!1ени|г в журнал транзакций, но до занесензш в базу данных, И.мсино это позвсъшет и.м работать со С1Н1.\н<а.\н! до и после из.менен-ных данных. Несмотря иа все это, шюгда пр1тмепенпе тршч-еров гтредпочтитель-иее из-за их большей мощности и гибкост!!.

И нет ттчего плохого в храни.мых нроцелу])ах, которые делают двойную работу, то есть тех, в которых выполняются DML-запросы, а также проверяется целостность данных. Фактически некоторые системтя работают исклточительно таким сиособо.м, то есть для каждой таблицы в базе данных создаются хранн.мые процедуры для выполнщшя операцпп INSERT, UPDATE н DELETE. Это - не табу, и встречается в тако.м сложном .мпре, каки.м яьляется разработка приложений баз даннтях.

Хранимые про1геду])ы подходят лучтне трнперов для выполнения ссылочной целостности, когда необходимо проверить целостность данных даже при налн-Ч1И! ограничен!п'. Если вы используете хранимую процедуру, скажем, для того чтобы выполнить удачение из данно11 таблицы, эта хранимая процедура может до удаления прове])пть, не будут ли нарушены какие-либо ссылкз! внешних ключей, !1 вывести соответствующее сообщение об ошибке, если необходимо,

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

Не забьншйте, что речь идет об Microsoft SQL Server 7.0. В MS SQL Server 2000 существуют также INSTEAD OF-триггерьт - Примеч. перев.