поля chartdepth каждой записи. Прежде че.м возвратить результат, он задействует производную таблицу и некоторые ирие.мы агрегиро1!ания для удаления повторяющихся значений. Производная таблица нужна для того, чтобы избавиться от необходимости включения ссылок на поля employee name и chartdepth в агрегатные функции при группировке по полю employee. Чтобы удалить повторы из результата, следует выполнять гругппфовку по полям employee или employee name. Если включить поле chartdepth в условие rpyinnipoBKn GROUP BY, часть повторяющихся зпаченнй останется в результате запроса из-за разных значений поля chartdepth.

Альтернативный подход

Способов представления деревьев в Transact-SQL люжет быть несколько. Еще одно успешное решение этой задачи заключается в организации цикла по базовой таблице с отдельной обработкой каждого узла и иснользование.м временной таблицы для отслеживания того, какие узлы были обработаны. Рассмотрим пример кода, который задействует эту методику для представления многоуровневой иерархии;

CREATE TABLE DINOSAURS (OrderNo int PRIMARY KEY. OrderName varchar(30). PredecessorNo int NULL REFERENCES DINOSAURS (OrderNo))

INSERT DINOSAURS VALUES (1.Amphibia1) INSERT DINOSAURS VALUES (2.Cotylosauri.1) INSERT DINOSAURS VALUES (3.Pelycosauria.2) INSERT DINOSAURS VALUES (4.Therapsida.2) INSERT DINOSAURS VALUES (5.Chelonia.3) INSERT DINOSAURS VALUES (6,Sauropterygia.3) INSERT DINOSAURS VALUES (7. Ichthyosauna,3) INSERT DINOSAURS VALUES (8.Squamata.3) INSERT DINOSAURS VALUES (9.Thecodontia.3) INSERT DINOSAURS VALUES (10.Crocodi1ia.9) INSERT DINOSAURS VALUES (11,Pterosauria.9) INSERT DINOSAURS VALUES (12.Saurichia.9) INSERT DINOSAURS VALUES (13.Ornithischia.9)

CREATE TABLE #work (Ivl int. OrderNo int)

CREATE TABLE #DINOSAURS (seq int identity, Ivl int. OnderNo int) DECLARE @1vl int. @curr int

SELECT TOP 1 @1v1=l. (acurr=OrderNo FROM DINOSAURS WHERE OrderNo-PredecessorNo INSERT INTO #work (Ivl. OrderNo) VALUES (@1vl. @curr)

WHILE (@lv1 > 0) BEGIN

IF EXISTS(SELECT * FROM #work WHERE 1vl=@lv1) BEGIN SELECT TOP 1 @curr=OrderNo FROM #work WHERE 1vl=@1v1

INSERT #DINOSAURS (Ivl. OrderNo) VALUES (@lvl. (Scurr) DELETE #work

WHERE 1v1=@lv1 and OnderNo-iacurr INSERT #work

SELECT OrderNo

FROM DINOSAURS WHERE PredecessorNo=@cjrr AND PredecessorNo <> OrderNo

IF (@(aROWCOUNT > 0) SET @1v1=@1vM END ELSE

SET @1v1=@lvM

SELECT Dinosaur Orders-REPLICATE(CHAR(9).1v1)+1.0rderName

FROM #DINOSAURS d JOIN DINOSAURS i ON (d.OrderNoH.OrderNo) ORDER BY seq

Dinosaur Orders:

Amphibia

Cotylosauri

Pelycosauria Chelonia Sauropterygia Ichthyosauri a Squamata Thecodontia

Crocodilia Pterosauria Saurichia Ornithischia

Therapsida

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

Как и в предыдущих запросах, для сортировки записей данный пример использует автоинкрементное поле. Вызовы функции REPLICATE(CHAR(9)) применяются для форматирования возвращаемого результата.

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

Вывод листьев дерева

Вместо вывода иерархии целиком .может возникнуть необходимость перечислить только узлы-листья. Узел называется листом (leaf node), если у него нет дочерних узлов. Поиск листьев достаточно прост: необходимо найти узлы, кото-

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

SELECT Grunls=s.employee name FRCM staff s WHERE NOT EXISTS

(SELECT * FROM staff t WHERE t.supervisor-s.employee)

Grunts

HARPO ZEPPO LARRY CURLY CURLY JOE

Табулированные списки

Хотя это не совсем то же самое, что дерево или иерархия, табулированный список реализует псевдоиерархическую зависи.мость посредством своего форматирования. Несмотря на то что такие списки по большей части находят примитивное при.менение, всегда полезно знать средства, доступные для форматирования наборов данных, независимо от того, придется ли вам их использовать. Вот при.мер кода, возвращающего табулированный список имен и фа.милии из таблицы authors демонстрационной базы данных pubs: SELECT authors-

CASE WHEN au fname=(SELECT MIN(au fname) FROM authors WHERE aujname=a.aujname) THEN au 1name ELSE

END+CHAR(13)+CHAR(9)+au fname FROM authors a

authors

Bennet

Abraham Blctchet-Halls

Reginald Carson

Cheryl DeFrance

Michel del Castillo

Innes Dull

Ann Green

Marjorie Greene

Morningstar Gringlesby

Burt Hunter

Sheryl Karsen

Livia Locksley

Charlene MacFeather

Stearns McBadden

Heather OLeary

Michael Panteley

Sylvia Ringer

Albert

Anne Smith

Meander Straight

Dean Stringer

Dirk White

Johnson Yokomoto

Akiko

Обратите внимание на применение функции CASE для ограничения вывода фамилий одной записыо. Нанри.мер, фа.милию Ringer носят два человека с именами Albert и Anne, но са.ма фамилия выводится только один раз. Обратите также внимание на применение вызовов CHAR(13) (перевод строки) и CHAR(9) (табуляция) для создания новых строк и формирования отступов в результирующем наборе данных. С помощью функции CHAR() можно добиться хорошего эффекта при форматировании результатов запросов. Комбинируя ее с CASE, вы можете выполнить те же основные операции форматирования, которые раньше были прерогативой генераторов OT4etoB и дополнительных средств разработки приложений.

Заключение

Хотя в Transact-SQL нет прямой поддержки иерархических структур, с помощью минимального количества кода можно формировать иерархические наборы данных. Применение объединений таблиц с самими собой и изобретательный метод использования функций C.4AR() и REPLICATE() дают широкие возможности генерации результатов в иерархической форме.

Курсоры

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

марки или слогана.

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

X. В. Кентоп

Курсоры - это механиз.м постепенного доступа к записям в таблице или резуль-тирующе.м множестве - по одно!! записи за один раз. Это идет вразрез с нормальной обработкой данных в SQL Server - курсор разделяет результирующие множества на отдельные записи; получение записи из курсора - это аналог возвращения ОДНО!! ЗШН1СИ с 1ЮМощью опсратора SELECT. В отличие от традиционных результирующих множеств курсор автоматически отслеживает свою позицию и предоставляет достаточно механизмов для перемещения в его результирующем .множестве.

Курсоры также предоставляют удобные средства .модификации результирующих множеств позиционны.м способом и возвращения указателей на резул5>ти-рующие .множества с по.мощью неременных. Обычно я советую забыть о курсорах. Если вы можете решить задачу с помощью инструментов Transact-SQL для работы с множества.ми, так и делайте. Довольно редко решения с помощью курсоров превосходят методы на основе множеств. Стандартные результирующие множества SQL Server (также известные как firehose-курсоры) использовались на протяжении многих лет для решения множества различных вычислительных задач - для решения большинства задач в обычных базах данных нет необходимости применять курсоры, хотя для некоторых лучше задействовать курсоры, че.м .множества.

Пожариы!! рукав (англ.). - Примеч. перев.

Курсоры и ISAM

Те, кто переносит приложения, использующие ISAM или локальные базы данных, на SQL Server, часто относятся к этому довольно несерьезно, - они не делают боьще изменений, чем необходимо, чтобы приложение заработало на новой СУБД. Часто для этого применяются поверхностные решения: замена способа навигации по записям ISAM (например, команды SKIPxBase) циклами по курсорам Transact-SQL. ISAM-записи и курсоры SQL Server - это не одно и то же, и любая попытка рассматривать реляционную СУБД как ISAM-продукт обречена на провал.

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

Заручившись поддержкой ангела-хранителя и не слишком внимательно изучив код, я принял задачу, наивно полагая, что разработчики создавали приложение в достаточно реляционной и разумной манере. Не ожидая подвоха, я думал, что они, где это возможно, обрабатывают записи как множества для экономии времени и кода, потому что даже самая простая локальная СУБД поддерживает различные способы обработки множеств (включая ее собственный диалект SQL). Конечно, я не ожидал, что код будет превосходным, но предполагал, что они правильно использовали инструменты. После беседы с авторами, которые произвели на меня благоприятное впечатление, я пустился в рискованное предприятие. , ,

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

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

Indexed Sequential Access Method - индексно-последовательный метод доступа (метод размещения записей в базе данных с использованием ключа, обеспечивающий быстрый поиск). - Примеч. перев.

Таблицы были деиормализоваиы, ио не с иелыо увеличения нроизводнтелвно-сти, а иросто истому, что разработчики не придумали ничего лучше. Не делалось попыток реализовать многопользовательскую работу, приложение было спроектировано (или, наоборот, 1Н1как ие проектировалось) строго как однопользовательское. Короче, с точки зрения ар.хитектуры это был thxhii ужас, и даже на ISAM приложение работало только б^шгодаря упорству разработчиков, а не ошибкоустойчивости приложения.

Итак, после этого радостного опыта, я начат переписывать приложение. Конечно, я .\юг выбрать легкий путь, просто перенеся приложение на SQL Server, по существу, превратив сервер в очень хороший ISAM-сервер. Я .мог бы задействовать ирактически весь существующий код, независихю от того, как плохо он был паписан. Каждый построчный доступ можно было преобразовать в эквивалентную курсорную онерапию SQL Server. Я использовал бы SQL Server способами, для которых он не иредназначался, и мог бы воздержаться от решения .многих реляционных и других нробле.м, бе.зду.мно собирая различные разрозненные куски вместе, создавая ирогра.м.много Франкенштейна . Я .мог бы это сделать - на самом деле ускорив работу и обрадовав руководство, - но я просто не мог так поступить. Мой опыт подсказывал, что обычно существует оптималь-ньи 1 способ создания ирогра.м.много обеспече1Н1я, - все .мои инстги1кты, обучение и знания твердили, что это не тот случай.

Было очевидно, что приложение необходи.\ю заново спроектировать, чтобы оно .чюгло нормально работать на SQL Server или любой другой реляционной СУБД. Острая необходи.мость в написании приложения заново была вызвана радикальными различиями между ISAM-пpoдyктa.vиI и СУБД и те.м, что оно изначально было плохо спроектировано. То, что нрограм.ма, кажется, работает правильно, не означает, что она создана правильно, так же как до.м .может казаться надежным, пока вы не начнете его надстраивать. Следует больше заботиться об архитектуре приложения, чем о его соответствии сиюлигнутны.м требованиям клиента. Конечно, главная задача - сделать клиентов счастлршыми, но это не должно осуществляться за счет таких долгосрочных показателей, как расширяемость, функциональная совместимость, производительность, .масштабируе.\юсть, многопользовательский доступ и иоддерживае.мость.

Это .\южет показаться просто техиически.ми McvienraMH, но клиенты заботятся о таких вещах независи.мо от того, знают oini о них или нет. Они на са.мом деле влияют на них косве1иго, если даже не напрямую. Реа-лизовать новую функциональность, которая может казаться простой обычному пользователю - например, преобразова1И1е однопользовательского приложения в многопользовательское, - .может быть сложно или вообще невоз.можно, если приложение с са.мого начала было неправильно спроектировано. Если проектировщик не задумывался о мно-гопользовательско.м доступе, то для многопользовательской работы приложение, скорее всего, придется переписать. Это переписывание выливается в задержки выпусков, а пользователям приходится ждать необходи.мой функциональности. Дизайн приложения влияет на конкретных людей реальными способами. Красоту оценивает не пользователь, а проектировщик.

Самое смешное, что большинство проблемных решений не и.мели смысла ни на ISAM-платформе, ни при использовании SQL Server. SQL Server просто рас-

крыл большинство этих дефегсгов. Из-за их акцента на ошибкоустойчивость и производительность реляционные СУБД реже прошают приложениям неправильное поведение, чем ISAM-продукты. Я не жалуюсь на это - считаю, что это хорошо.

Разработчики не должны создавать плохие приложения независимо от серверной части. Перенос ISAM-приложений на SQL SeTveT, - это не простая задача даже для хорошо спроектированных приложений. Быстро сделать перенос, заменив ISAM-доступ на курсоры SQL Server - в большинстве случаев плохой подход. Необходимо иметь мужество, чтобы сказать: перенос займет определенное время; приложение следует заново спроектировать или переписать - чаще всего это лучший выход. Повторно изобретать колесо хорошо, даже необходимо, если колесо до этого было квадратным. Делайте серьезные преобразования, когда перемещаете приложения на SQL Server - считайте, что это фундамент вашего приложения, а не просто другой инструмент. Предоставьте выполнять быстрые преобразования тем, кто говорит: никогда нет времени сделать что-то хорошо, но всегда находится время, чтобы переделать .

Типы курсоров

Существует четыре типа курсоров, поддерживаемых Transact-SQL: FORWARD ONLY, DYNAMIC, STATIC и KEYSET. Основное различие между ними заключается в способности определять изменения в данных, в то время как курсор перемещается по ним, и в иснользуе.мых ресурсах (блокировки, место в tempdb и так далее). В зависимости от типа созданного вами курсора изменения в его данных могут быть либо показаны, либо нет во время перемещения по нему. В дополнение к новым значениям столбцов, эти измене}шя могут влиять на то, какие записи возвращаются курсором (членство), а также на порядок записей. Открытие курсора может также привести к тому, что все его результирующее множество (или значения ключей) будет помещено во временную таблицу, а это, в свою очередь, может привести проблемам с ресурсами в tempdb. В табл. 13.1 приведена вводная информация по различным типам курсоров и их атрибутам.

Таблица 13.1. Типы поддерживаемых Transact-SQL курсоров и их атрибуты

Курсоры без возможности прокрутки (Forward Only)

Курсор без возможности прокрутки (этот тин используется по умолчанию) возвращает записи последовательно. Он не требует места в tempdb, изменения в данных видимы, как только эти данные будут выбраны. Вот пример:

CRtAF TABLt Сетр (kl int ideniiLy, cl int NULL

INSERT #terTip DEFAULT VALUES

INSERl ftemp DEFAULT VALUES

INSERl #temp DEEAULl VALUES

INSERT #temp DEFAULT VALUES

DECLARE с CURSOR FORWARD ONLY

FOR SELECi kl. cl FRCM #temp

OPEN с

FETCH с

UPDATE #temp

SET cl2

WHERE kl-3

FETCH с

FETCH с

SELECT * IRCM #t,enip CLCSE с OEALLOCAIE с GO

DROP TABLE #temp kl cl

Динамические курсоры

Как и курсоры без возможности прокрутки, динамические курсоры отражают изменения в записях, как только эти записи достигнуты. Для них не требуется дополнительное место в tempdb. В отличие от курсоров без возможности прокрутки, динамические прокручиваемые (scrollable) курсоры позволяют обращаться к записям не только последовательно. Иногда их называют чувствительпьши (sensitive) курсора.ми, иото.му что они чувствительны к изменеиия.м исходных данных. Вот при.мер:

CREATE TABLE #temp (kl int identity, cl int NULL)

INSERT ftemp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE с CURSOR DYNAMIC

FOR SELECT kl. cl FROM #temp

OPEN с

FETCH с

UPDATE #temp SET cl=2 WHERE kl=l FETCH с

FETCH PRIOR FROM с SELECT * FROM #temp CLOSE с DEALLOCATE с GO

DROP TABLE #temp kl cl

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

Статические курсоры

Статические курсоры возвращают результирующее множество только для чтения, которое не отражает изменения данных. Это противоположность динамических курсоров, хотя они и прокручиваемые. Как только статический курсор открыт, изменения его исходных данных не отражаются курсором. Это происходит потому, что его результирующее множество полностью копируется в tempdb при первоначальном открытии. Статические курсоры иногда называются курсорами-снимками, или нечувствительными {insensitive) курсорами, пото.му что они не чувствительны к изменениям их исходных данных. Вот пример:

CREATE TABLE #temp (kl int identity, cl int NULL)

INSERT #temp DEFAUtT VALUES

INSERT #temp DEFAUtT VALUES

INSERT #temp DEFAULT VALUES

INSERT #temp DEFAULT VALUES

DECLARE с CURSOR STATIC

FOR SELECT kl. cl FROM #temp

OPEN с -- Результирующее множество полностью копируется в tempdb UPDATE #temp SET cl=2 WHERE kl=l