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

Как уже упоминалось ранее, супдествует несколько факторов, влпяювцпх на то, будет ли представление обновляемылт Чтобы представлетпю было обновляемым, оно должно удовлетворять следующ^тм критериям:

О нельзя использовать агрегатные фуикцпп, ирел-южспия или ключевые слова ТОР, GROUP BY, UNION или DISTINCT;

О нельзя обновлять производные сто.тбцы (столб1П>1, созданные с применением сложных выражениГО;

О не разрешены спнскп SELECT, состоянию целиком из нетаб.шчиых выражений.

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

WITH CHECK OPTION

Обновляемое представлепнс .может быть со;иано так, чтобы оно проверяло модификацию записей на соответствие пред.чожеиию WHERE, если оно есть. Это предотврашает исчезновение записей, добавленных че])ез представление, когда оно потребуется, так как эти записи не соответствуют условия.м выборки. Чтобы представление функционировало таки.м образом, используйте при его создании предложение WITH CHECK OPTION, как здесь:

CREATE VIEw CAEIFORNIA AUTH0RS AS SELECT * FROM authors WHERE State=CA-WITH CHECK OPTION

В ;этом конкретно.м нри.мере гараитируется, что все авто1)ы, добавляемые с помощью представления, живут в Калифорнии. Напри.мер, этот оператор пе будет выполнен из-за WITH CHECK OPTION:

INSERT CALIFORNIA AUTHORS

VALUES CSeZ-SS-OgEI .Henderson, Ken,

972 555-1212,57 R1verslde,Dallas,TX,75080,1)

Server: Msg 550, Level 16, State 1, Line 1

The attempted insert or update failed because the target VIEW eitner specifies WITH CHECK OPTION or spans a VIEW that specifies WITH CHECK OPIION and one or more rows resulting from the operation did not qualify under the CHECK OPION constraint.

The statement has been terminated.

To же самое относится и к .модификации записей. Если в результате .модификации, которую вы осуществляете с использованием представления, со,злан-ного с указанием ключевого слова WITH CHECK OPTION, запись не будет удовлетворять критерию WHERE предстаплеп1И1, .модификация будет отклонена.

Производные таблицы

Как говорилось в главе 6, производные таблицы - это операторы SELECT, которые помещаются в предложение FROM других операторов SELECT вместо ссылок на таблицы. Я включил в эту главу их обсуждение для полноты картины, кроме того, они напоминают неявные или автоматические представления. Производные таблицы делают возможными некоторые типы запросов, для которых ранее требовались отдельные объекты представлений. Вот пример:

CREATE TABLE #1996 P0P ESTIMATE (Region char(7). State char(2). Population int)

[NSERT #1996 P0P EST[MATE VALUES (West. CA.31878234)

INSERT #1996 P0P ESTIMATE VALUES (South. TX.19128261)

INSERT #1996 P0P ESTIMATE VALUES (North. NY.18184774)

INSERT #1996 P0P ESTIMATE VALUES (South. PL.14399985)

INSERT #1996 P0P ESTIMATE VALUES (North, NJ. 7987933)

INSERT #1996 P0P ESTIMATE VALUES (East. NC. 7322870)

INSERT #1996 P0P ESTIMATE VALUES (West. WA. 5532939)

INSERT #1996 P0P ESTIMATE VALUES (Central,MO. 5358692)

INSERT #1996 P0P ESTIMATE VALUES (East. MD. 5071604)

INSERT #1996 P0P ESTIMATE VALUES (Central.OK. 3300902)

SELECT * FROM (SELECT TOP 5 WITH TIES State.

Region. Population=Population71000000

FROM #1996 P0P ESTIMATE

ORDER BY Population/lOOOOOO) p ORDER BY Population DESC

State Region Population

NJ North 7

NC East 7

WA West 5

MO Central 5

MD East 5

OK Central 3

Этот запрос использует производную таблицу для получения пяти штатов с наименьшей численностью населения. Затем он применяет ORDER BY во внешнем операторе SELECT, чтобы отсортировать их в порядке убывания. Если бы не было поддержки производных таблиц, этот подход потребовал бы наличия отдельного представления или временной таблицы.

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

Динамические представления

Когда вы обращаетесь к представлению, план запроса создается путем объединения первоначального оператора SELECT, который был использован для создания представления, с тем, с помощью которого вы обращаетесь к представлению-

Критерий отбора, которьн! вы указали при создапш! представления, объединяется с критериями вашето запроса, и составной критерий передается серверу для дальнейшей обработки.

Больидинство представлений, включаюпш.х критери11 отбора, используют статические критерии - логика отбора, иолучаюшаяся в результате объедипепия с оператором SELECT, обращающи.мся к представлению, никогда не изменяется, HCBaBHCtTMO от toj o, сколько раз запрашивается представление. Дина.мическая часть составного запроса обычно исходит из пользовательского оператора SELECT, а не из представления. За исключением представлений, которые прн.меняют соединения для связи с други.ми представлениями и таблицами, критерий отбора, при-.меняемый представлением для фильтрации результирующего множества, остается неиз.мениььчь Чаще всего .этого достаточно, но есть ситуации, в которых было бы удоб1Ю задействовать динамические представления - представления, критерии отбора которых изменяются в соответствии с внешии.\п1 фактора.ми.

Динамическое представление - это представление, критерий отбора которого .может из.меняться в зависимости от значений выражений в его предложениях WHERE или HAVING. Это .\южет быть довольно удобно на практике. Вместо того чтобы возвращать константы, эти выражения возвращают различные значения, в зависимости от элементов окружения или сессии. Лучпп1Й пример такого представления - представление, возвращающее результирующее .множество, основанное иа иетаблично.м выражении. Вот такое представление, возвращающее список продаж на текущую дату с использованием нетабличной функции GETDATE():

CREATE VIEW DAILY SALES AS SELECT * FROM sales

WHERE ord date BETWEEN C0NVERT(cha-(8) .GETDATEO .112) AND C0NVERT(char(8).GETDATE().112)+ 23:59:59.999

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

INSERT sales

VALUES (8042.QA879.Г,GETDATEO.30.Net 30. BU1032) INSERT sales

VALUES (6380.D4482-. GETDATE 0.11,Net 60. PS2091) INSERT sales

VALUES (6380.D4492.GETDATE() + l,53.Net 30.Р5209Г) SELECT * FROM DAILYJALES

stor id ord num ord date qty payterms title id

6380 D4482 1999-06-24 19:14:33.657 30 Net 60 PS2091

8042 0A879.1 1999-06-24 19:13:26.230 30 Net 30 BU1032

Это представление использует GETDATEO, чтобы о)раничить записи о продажах теми, у которых ord date равно текущей дате. Критерий, который на са.\юм деле обрабатывается серверо.м, будет зависеть от текущей даты. Сегодня предложение WHERE будет соответствовать сегодняшней дате, соответственно, будут показаны первые две вставленные записи. Завтра WHERE будет соответствовать завтрашней дате, будет показана третья запись. Это и есть суть дина.мическнх

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

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

CREATE VIEW DAILYJALES AS SELECT * FROM sales

WHERE ordjate BETWEEN

(CASE DATEPARTCDW.CONVERT(Chaг(8).GETDATE0.112)) WHEN 1 THEN C0NVERT(char(8).GETDATE()+1.112) WHEN 7 THEN C0NVERT(Char(8).GETDATE()-l.112) ELSE C0NVERT(char(8).GETDATE().112) END)

AND (CASE DATEPART(DW.C0NVERT(char(8).GETDATE().ll2)) WHEN 1 THEN CONVERT(char(8).GETDATE()-1.112) WHEN 7 THEN C0NVERT(char(8).GETDATE()-l.112) ELSE C0NVERT(char(8).GETDATE().112) END+ 23:59:59.999)

Вы можете при.менить другие иетабличные функции для создания таких скользящих, или динамических, представлений. Например, можно использовать SUSER SNAME(), чтобы ограничить возвращаемые записи в соответствии с именем пользователя. Для фильтрации на основании имени машины можно задействовать HOST NAME(). В любо.м случае оператор SELECT, используемый для запроса к представлению, не изменяется (в примерах выше это всегда просто SELECT *); изменяется только критерий, применяемый нрсдставление.м для фильтрации результирующего множества.

Разделение данных с использованием представлений

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

USE Northwind GO

IF (OBJECT IDCEMP VIEW) IS NOT NULL) DROP VIEW EMP VIEW

CREATE VIEW EMP VIEW AS SELECT LastName.

FirstName.

Title. Extension

FROM enpoyees-GO

SELEC FROM EMF VIEW

Здесь персональная информация - номер до.чьчипюго re;ie(j)Oiia н дага рождения сотрудника - оиупюпа в п|)елставлен)И1, чгобы создать npocToii список сотрз'дников. Вот пример горизонтального разле.клн1я;

USE Northwind GD

IF (OBJECT IDCUSA ORDERS) IS f.U; NblL, DROP VIEW USA ORDERS

IF {GBJECT ID(Uk ORDERS-; IS N01 NUiL. DROP VIEW UK DRDERS

IF (OBJECT ID(FRENCri ORDERS; IS NG NUI., ; DROP VIEW FRENCH ORDERS

CREATE VIEW USA ORDERS AS

SELECT TOP 10 EmployeeiD. C0UN4*) AS KuiiOrccrcd FROM orders

WHERE ShipCountry-USA GROUP BY EmployeeiD ORDER BY NumOrdered DESC GD

CREATE VIEW UK DRDERS AS

SELECT TOP 10 EmployeeiD, CGUNU ; AS fiu.iiOrcered

FROM orders

WHERE ShipCountry=UK

GROUP BY EmployeeiD

ORDER BY NumOrdered DESC

CREATE VIEW FRENCH DRDERS AS

SELECT TOP 10 EmployeeiD. COUNT,*; AS NuinCriieec

FROM orders

WHERE ShipCountry=France GROUP By EmployeeiD ORDER BY NumDrdered DESC GO

PRINT USA DRDERS SELECT * FROM USA DRDERS GO

PRINT UK ORDERS SELECT * FROM UK DRDERS GD

PRINT FRENCH ORDERS SELECT * FROM FRENCH ORDERS GO

USA ORDERS EmployeelD

1 3 8 6 2 7 5 9

UK ORDERS EmployeelD

4 1 3 8 2 6 7 9 5

FRENCH ORDERS EmployeelD

4 3 2 1

I

NumOrdered

NumOrdered

NumOrdered

Заключение

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

Статистические функции

Статист11ческ;ш информация подобна бикини. То, что она нока,зьи!ает, наводит на раз.мыш.чеиия. Но то, что она скрывает, жизненно нажио.

Арон Левенштейн

Среди разработчиков, как опытных, так и новичков, существует заб.чуж,ает1е, что SQL Server не подходит для сложных вычислений. Он восцришшается только как средство получения данных - SQL Server хорощо подходит для хранения и получения данных, но вся тяжелая вычислительная работа должна осуществляться на традиционных языках (3GL) програ.мхщрования. Хотя управление данны.ми и получе1И1е данных - его сильные стороны, SQL Server .может также осуществлять сложные вьитисления, включая статистическне. Если вы зпаете, что делать, найдется не много статистических вычислений, недоступных для Transact-SQL.

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

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

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

Другой момент, о котором я не упомянул, - это использование хранимых процедур для выполнения сложных вычислений. Если вы спросите разработчиков на SQL, как вычислить статистическую медиану столбца таблицы SQL Server, большинство ответит, что для этого необходима хранимая процедура. В этой процедуре, скорее всего, будет открываться некоторый курсор для поиска срединного значения столбца. Хотя это и будет работать, необходимости в этом нет. Как будет показано в этой главе, вам не гютрсбуются хранимые процедуры для выполнения большинства статистических вычислений - обычных операторов SELECT будет достаточно. Прохождение таблиц с помощью обычных циклов - это чуждый SQL подход к решению задач, и вы должны по возможности этого избегать (см. главу 13, Курсоры ). Используйте сильные стороны Transact-SQL, чтобы облегчить себе жизнь, не пытайтесь применять его для задач, для которых он не предиазначен. Попытка заставить Transact-SQL вести себя подобно традиционному языку - это ошибка: он не является традиционным языком программирования. Это то же самое, что пытаться заставить традиционной язык програм.мироваиня вести себя как язык для работы с данными. Заставить один инструмент вести себя как другой - все равно что пытаться вбить квадратный кол в круглое отверстие - скорее всего ничего не выйдет.

При выполнении сложных математических вычислений на Transact-SQL необходимо помнить, что SQL как язык не обрабатывает ошибки, связанные с округлением значений с плавающей запятой. Естестветю, это влияет на значения, возвращаемые запросами. Один и тот же запрос может возвращать различные данные в зависимости от порядка данных. Для решения этой проблемы можно использовать типы с фиксированной запятой, например decimal или numeric, вместо типов с плавающей запятой, таких как float или real. Для более подробной информации смотрите раздел Шутки с плавающей запятой главы 2.

Когда использовать CASE

Несмотря на неуклюжий синтаксис, CASE - чрезвычайно мощное оружие в арсенале Transact-SQL. Он позволяет осуществлять сложные вычисления в операторах SELECT, что paifbHie можно было сделать только с помощью загадочных функции и хранимых процедур. Некоторые из этих решений ос1юваны на тайных приемах кодирования выражений в Transact-SQL, так что количество проходов по таблице значительно сокращается. Это, в свою очередь, приводит к увеличению производительности, и код обычно выглядит более компактным по сравнению с обычными техниками кодирования. Это лучше объяснить на примере. Давайте рассмотрим создание перекрестной, или сводной (pivot), таблицы с помонию функций. Предположим, что у нас есть следующая таблица и данные:

CREATE TABLE ffEARf.SAIlb (SalesYear snailсаГеттте. Sales money)

INSERT #vEAkLY SALES VALUES ( 1Л9jl:,Е-;ЬЗ.Oi,

INSERT #Y£ARLV SALES VALUES С20С001СГ ,2.:23:, 12

INSERT SyearlyJALES VALUES С20010101.6 983. 5c

Bor как выглядит сводньп ! запрос с исиользоватюм фчшчЦиИ: SELECT

1999--SUM(Sales*(I-ABS(SIGN;YEuR;S,-i)HsVec-;-:99-; 1-. 2000 =SUM.(Sals5*;i-ABS(SIGN(Y[AR;SalesYea-;-2C00))b-. 2001 =SUM,(Sales*(I-ABSfSIGN(YEAP,(SalesYeur)-2CJi;; FROM #YEARLY SALES

1999 2000 200.

86753.0900 34231.1200 67983.5600

Обратите внимание на при.менение редких (J)yHKunii - ABS() и SIGN(). Это обычная ситуация для решений, основанных на функциях, н причина, по которой эти решения выглядят так зш1утшпю. Tep.snni характерпстпческ;гя функция был впервые введен Дэвидо.м Розенште1и10.м, Анатолием Аб1)а.\и)виче.м и Евгением Бир-гером в статьях для SQL Forum несколько лет назад для описания подобных ре-и[ений. Характеристическа>г функция, приведенная вьнпе, считается точечной характеристической функцис!! для столбца Sales-Year. Каждая ее когитя возвращает единицу, если часть SalesYear, представляющая год, равна конкретному году, и ноль - если нет. Эта единица или ноль зате.м улнюжаются на значение Sales (объем продаж) в каждой записи для получения данных о продажах за этот год или ноль. Конечный результат - каждый столбец включает продажи для каждого года в выражении - и.менпо это нам п было нужно. Чтобы понять, как характеристическая функция работает в контексте конкретного запроса, необхо-дикю мысленно преобразовать такую функцию в ее логический эквивалент. Когда характеристические функции были впервые обнаружены , их таблицы были опубликованы, чтобы помочь разработчикам иа SQL в решении такоГ! непростой задачи. Это преобразовашю необходи.мо, иото.му что проблемы, решаемые с помощью них, редко бывают интуитивно иоиятиыхит Вот почему при со,здапии сводных таблиц не следует использовать функтат ABS() и SIGN(). В этом случае на сцену выходит CASE.

С изобретением SQL-92 и CASE и(юбходп.мость в странных выражениях, подобных эти.м, практически исчезла. Используйте CASE в.место характеристических функций, когда это воз.чюжио. CASE nponie читать, проще расширять, кроме того, отпадает необходикюсть в \и>1слеиных преобразованиях выражений к тайным таблица.м и из них. Напри.мер, пот запрос, создающий сводную таблрщу, переписанный с использованием CASE:

SELECT

1999 =SUM(CASE WHEN YEAR(SaiesYear)---:999 IHEN Sales ELSE NULL END). 2000 =SUM(CASE WHEN YEAR(S3lesYear)=2000 THEN Sales ELSE NULL END). 200I =SUM(CASE WHEN YEAR(SalesYear)=2001 THEN Saes EiSE NUI L END) FROM #YEARLY SALES

1999 2000 2001

86753.0900 34231.1200 67983.5600

Это решение прозрачнее и проще для понимания, чем предыдущее с SIGN() и ABS(). Я также нахожу, что это проще читается, чем SELECT

1999 -SUM(CASE YEAR(SalesYear) WHEN 1999 THEN Sales ELSE NULL END). 2000-SUM(CASE YEAR(SalesYear) WHEN 2000 THEN Sales ELSE NULL END). 200r=SUM(CASE YEAR(Sa)esYear) WHEN 2001 THEN Sales ELSE NULL END) FROM #YEARLY SALES

Хотя такое решение и значительно лучше решения с SIGN()/ABS(), я предпочитаю сложный синтаксис CASE (searched CASE), просто потому что связь между 1999 и YEAR (SalesYear) лучше видна при использовании этого синтаксиса, хотя я и признаю, что это в значительной степени зависит от предпочтений.

Опасения, связанные с производительностью

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

Дисперсия и среднеквадратичное отклонение

Transact-SQL предоставляет девять различных агрегатных функций, все они полезны при проведении статистических расчетов. Помимо стандартных агрегатных функций, которые вы можете увидеть в большинстве SQL СУБД: SUM(), MIN(), МАХ(), COUNT() и AVG(), - SQL Server предоставляет еще четыре, непосредственно предназначенные для финансовых и статистических вычислений: STDDEV(), STDDEVPO, VAR(), VARP(). Функции STDDEV вычисляют среднеквадратичное отклонение выборки и среднеквадратичное отклонение совокупности соответственно, тогда как функции VAR вычисляют дисперсию выборки и дисперсию совокупности. Эти функции работают, как и все остальные агрегатные функции, - они игнорируют неопределенные (NULL) значения, могут использоваться с GROUP BY для создания векторных агрегатов, и так далее. Вот пример, в котором применяются встроенные агрегатные функции Transact-SQL для вычисления основных статистических параметров:

CREATE TABLE #1996 P0PJSTIMATE (Region char(7). State char(2). Population int)

INSERT #1996 P0P ESTIMATE VALUES CWesf. XA .31878234)

INSERT #1996 P0PJSTIMATE VALUES (South. TX, 19128261)

INSERT #1996 P0P ESTIMATE VALUES (North. NY.18184774)

INSERT #1996 P0P ESTI MATE VALUES (South, PL.14399985)

INSERT #1996 P0P EST1MATE VALUES CNorth. NJ. 7987933)

INSERT #1996 POP ESTIMATE VALUES (East. NC. 7322870)