LMSERl fffd.TiOusjaycees VAuJES ( Jci, Caihoun ,Eorgresbina, , 1825, Sc-rved as VP uder 1лс U.S. oresCents)

INSERT #famousjaycee5 VALUES (Joan Crawford.Actness.1923.Appeared in everything from Grand Hotel to Trog)

INSERT #fa-nOuSjaycees i/ALUES (Janes Cagney.Actor, 1931.T.nis prototypical gangster made a dandy Yankee)

INSER Ifamousjaycees VALUES CJti Croce,Smgervsong.vriter. 1972. floold LhSt time were in a bottle because you left us .vay too scon)

INSERl Ifamousjaycees VALUES (Joe Celo.Aufcr/iectuner. 1987,Courts eating and living indoons arncng his favorite hobbies)

DECLARE JCS CURSOR DYNAMIC FOR SElECi * IROM #famousjaycees FOR UPDAIE OPEN JCS

FETCH RELATIVE 3 F.ROM jcs

DELETE #famou5Jaycee5 WHERE CURRENT OF jcs

CLOSE jcs DEALLOCATE jcs

SELECT * FRO,- #famou5jaycees jC occupation

John Calhoun Congnessman

becam.e famous note 1825

Senvec as VP uncer- two U.S. Pnesidents

jc occupation Ьесагле famous notes

Julius Caesan Militany leaden/dictaton -45 Jesus Christ Founcec Christianity 1

Joan Crawford Actress

James Cagney Actor

Jim Croce Singer/songwriter

Joe Celko

Author/lecturer

1923

1931

1972

1987

Took the Roman early retirement program Birth featured tellurian, ruminative, ana tutelary visitors Appeared in everything from Grand Hotel to Trog

This prototypical gangster made a dandy Yankee

Would that time were in a bottle because you left us way too soon Counts eating and 1iving indoors among hS favorite hobbies

TRUNCATE TABLE

Аналогично BULK INSERT, команда TRUNCATE TABLE предоставляет способ удаления записей в таблице с минимальны.м количество.м записей в журнал. Расиро-странено заблуждение, что вообще никакого журналирования не происходит. На са.мо.м деле в журнал заносятся операции освобожденргя страниц - это иеобхо-

димо, н протипном случае иы пе смогли оы 1и>то..ип1ть команду из транзакции, а затем отменить ее возле]1ствне на базу данных. Вот n])H.\iep:

USt PJDS BEjIN TRAri

SLECI CQJN; AS CojntBcfore from bdus iSJSCATE TABIC sales

SEiECT count;*) as CountAfte- FROH saes GO

Ra:3ACK TRAN

SELECT COUNT(*) AS CoL.nLAfLerRcl loacK ERCM sj:cs CCLintBefore

CouriAfter

С

CojntAfterRollback

При выпо.тнении TRUNCATE TABLE ue заиос]ггся в журнал инфор.мация об удалении кал<дон записи. Это происходит ното.му, что на са.мом деле не наблюдается удаления запнсей ~ происходит то.тько освобождение страниц, (юставляющих таблицу. Так как заппсп не удаляются, они не зашюягся в журиа.1, и DELETE-триггеры не срабатывают.

Вы увидите, что TRUNCATE TABLE вьтолияегся на.много быстрее, че.м неограниченный условием оператор DELETE шЫепате; фактически TRUNCATE TABLE выполняется мгновенно для таблиц иебо.чьшого и среднего размера. Однако существует несколько ограничснн , связанпььх с 3T0ii ко.мандой. Вы не можете использовать TRUNCATE TABLE с таблицей, иа которую ссылаются ограничения 1зиешних ключей, даже если усечение таблицы пе napyuniT никакие отношения внешних ключей (нанри.мер, если завнси.мая таблина пусгая). Вы также не .можете применять TRUNCATE TABLE с таб.тгтей, опубл1н<:ованноп для репликации. Это вызвано тем, что репликация задиштвует журнал транзакций для сннхро-низати! издателей и подписчиков, а TRUNCATE TABLE, как я сказал, не генерирует в журна^че транзакщиТ записи, связанные с удатенпем.

Обнаружение ошибок выполнения DML-операторов

обычно вы можете обнаружить ошибки ньнюлиения DML-онераторов, проверив авто.матическую переменную @@ERROR. Однако, если DML-онератор не затронул никакие записи, @@ERROR не будет установлена, так как технически это

Заключение 141

не ошибочное состояние. В.место этого вам следует проверить @@ROWCOUNT. Други.чи! слова.\и1, если ва.м необходимо рассматривать как ошибку тот факт, что DML-оператор не затронул (не нашел) И1и<акие записи, проверяйте @@ROWCOUNT после выполпепия оператора и реагируйте соответственно.

Заключение

В этой главе вы познакомились с некоторыми основны.ми асиекталш DML Transact-SQL. Вы узнали о командах INSERT, UPDATE и DELETE и о том, как они используются в реальных запросах. Вы также узнали о различиях в скорости и ограничениях, связанных с эти\и1 оператора.ми.

Мощный оператор SELECT

Элемент фэнтезп, который объясияст любопь многих программистои к играм в стиле очисти подземелье , это не огпедыиштпс .монстры п не по.1уголыс сирены; это опыт оынолисння задачи от начала и до конца без из.мсиспия нользоиатс.1ьских трсбоиапт !,

Томас Л. Хоюдэй

Как я сказал в главе 1, оператор SELECT - это рабочая лошадка языка Transact-SQL. С его помощью можно делать все - от присваивания значений переменным и получения результирующих множеств до создания таблиц. Во всех версиях SQL - это мощная бензопила . Когда-то этот оператор ири.менялся для очистки некоторых ошибочных состояний в версии SQL Server от Syba.se (с использованием функции LCT ADMI()).

Хотя очень удобно решать 75 % задач с помонпло одного ггнструмента, иногда он довольно сложен в использовании. LlncTpyMCHT с таки.чт большими воз-можностя.ми оказывается отчасти неупраиляемы.м - вы должны быть осторожны, чтобы не отрезать себе палец.

Простые формы оператора SELECT

Как я уже также отмечал в главе L оператор необязательно должен быть слож-ны.м. Вот несколько примеров для начала;

USE pubs

SELECT * EROM authors (результаты сокращены)

au id аи 1паше au fname phone

172-32-1176 White Johnson 408 496-7223

213-46-8915 Green Marjone 415 986-7020

SELECT tnie id. title FROM titles

(рез у л ьтаты с о К1)а ще и ы) titie id title

PC1G35 But Is It User Friendly?

PS1372 Computen Phobic AND Non-Prcp-c Ina-viduas: Benavon Vaniations

SELtC: One-

Вычисляемые и производные поля

в дополнение к разнообразным полям вы можете указьизагь функции, вычисления и производные поля в cihickc столбцов оператора SELECT (обычно называемом список SELECT*). Вот несколько примеров;

SELECT РК), CAST(21,99115 / 7 AS decimal (7,6)) AS RoughPi

SSPi RotghPi

3.1415926535897931 3.141593

Можно применять функщи! без параметров, иапри.мер Р1(), и (})ункции, которы.м необходи.мы пара.метры, напри.мер CAST(). Вы .можете использовать выражения, как оперирующие поля.\и1, так и пе оперирующие. Мож1ю выполнять простые вычисления и включать под.заиросы, возвращающие единственное значение;

SELECT pub name. (SELECT COUN(*) FRCK titles t WHERE t.pjb id=p,pub id) AS

NumPublished

FROM pubnshens p

pub name NumPubiished

New Moon Books 5

Binnet & Handley 7

Algodata Infosystems 6

Five Lakes Publishing 0

Ramona Publshens 0

GGG&G 0

Scootney Books 0

Lucerne Publishing 0

Производный столбец состоит из подзапроса, который возвращает едппствеп-ное значение. Этот запрос кюжст быть связанны.м (коррелированным) с внешним запросом или нет, но он обязан возвращать результирующее множество, содержащее точно один столбец и одну запись. Мы рассмотрим коррелированные подзапросы подробнее немного позже. Я создал этот запрос с производны.м полем только как пример. Лучше написать его с при.менение.м соединения, как здесь;

SELECT pub name. COUNT(t.title id) AS NumPubiished

FROM publishens p LEFT JOIN titles t ON (p.pub id = t.pubjd)

GROUP BY pub name

Это довольно paciipociраненная (тил'ЛгЧня с по.чзанросамп ~ с^нень часто и. можно переделать с нсполь.човаппем соедипепнИ. Приме|1ение соединенгп! ино гда более эффективно, потому что iin)pii4m>iii запрос не будет выполняться дл; каждой загпгси iviannoii таблицы.

SELECT TOP

До SQL Server версии 7.0 ограинчип; чнс.ю 3aiHHcii, возврап1аел1ЫХ занросо.м можно было толькч с помощью ко.манды SET ROWCOUNT. SET ROWCOUNT все ещ( доступна, по теперь есть способ лучше. SELECT ТОР п [PERCENT] [WITH TIES], где п - .это число, или процент aannceii, которые вы .хотите юлучить, - эффектнв ный способ усечь результаты запроса. Вот ггрп.мер:

SELECT TOP 10 t.tiHe. SU-Ks.qty; AS TotaiSdles EROM sales s JOIN titles t ON (s,title i.-jl.:itie ;a.; GROUP BY t.title ORDER BY Total Sales CESC

title TotaiSfiies

0

Is Anger tne Enemy? Secrets of Silicon Valley

The Busy Executives Datacase G,i:de 4£ Omons, Leeks, and Garlic: Cooking Secrets of tne MecUer-anean 40

The Gourmet Microwave 40

You Can Combat Computer Stress! 35

But Is It User Friendly? 30

The Psychology of Computer Cooking 30

Cooking with Computers: Surreptitious Balance bheets 25

Emotional Security. A New Algorithm 25

Как вы, наверное, н ноду.ма-Ш, указание опционального ключевого слова PERCENT ограничивает количество вслзврашае.мых записей процентом от полного количества записей.

Добавьте WITH TIES, есл] bi,i хотите нключить хвосты - д>-блируюциеся значения - в резул1)Тируюшее .множество. Если вы не просто урезаете результирующее множество до определенного ра:).мера, нснользовапне ТОР п логично подразумевает примене|Н1е ORDER BY. Хотя предложение ORDER BY опционально с простым ТОР п, при выборе опции WITH TIES оно необходи\ю, чтобы можно было логически разрепшть эти хвосты . Вот прн.мер:

SELECT ТОР 4 U1TH TIES t,title, SLM.s.Qty) AS lotaiSaes FROM sales s JOIN titles i ON cs,t:f!e id=t.title id; GROUP BY t,title ORDER BY Total Sale

title TotalSales

Is Anger the Enemy? 191

Secrets of Silicon Valley 50

The Busy Executives Database Guide 45

Onions, Leeks, and Garlic: Cocking Secrets of the Mediterranean 40

The Gourmet Microwave 40

Хотя мы указали TOP 4, в результате получены пять записей, потому что мы имеем дублирующееся значение в четвертой позииин. Учтите, что эта специальная обработка работает только для хвостов в конце результирующего множества. Вот почему в данно.м при.мере дублирующиеся значения во второй и третьей позидпях ие приведут к получепрно более четырех записей - будет эффект только от дублирующегося значения в четвертой позиции. Этот факт трудно понять, но в результате следующий запрос вернет то же са.мое результирующее множество, что и запрос с ТОР 4:

SELECT ТОР 5 t.t1t:e, SUM(5.qt}) AS TotalSales

EROM sales s JOIN titles t ON (s.titie icl=t.title id)

GROUP BY t title

ORDER BY TotalSaies DESC

и

SELECT TOP Ь WITH TIES t.title. SUM(s.qty) AS TotalSales FROM sales s JOIN titles t ON (s.title id=t.title id) GROUP BY t.title ORDER BY TotalSales DESC

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

SELECT t.state. t.stor nar,e. SUM(s.qty) AS TotalSales FROM sales s JOIN stores t ON (s.stor id=t.stor id) GROUP BY t.state. t.stor nanie ORDER BY TotalSales DESC. t.state, t.5tor naiiie

state stor name TotalSales

Хотя такой синтаксис поддерживается, этот запрос не делает то, что нам хочется:

- - Плохой SOL

-- не работает так. как нам хочется

SELECT ТОР 1 t,state. t.stor name. SUM(s.qty) AS TotalSales

FROM sales s JOIN stones t ON (s.ston id=t.ston id)

GROUP BY t.state. t.ston name

ORDER BY TotalSales DESC. t.state. t.ston name

state ston name TotalSales

OR Bookbeat 140

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

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

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

SELECT au iname. aufname

FROM (SELECT * FROM authors) A

(результаты сокращены)

au Iname au fnanie

Bennet Abraham

Blotchet-Hal IS Reginald

Carson Cheryl

DeFrance Michel

del Castillo Innes

Производная таблица в это.м запросе получена с использование.м синтаксис; SELECT * FROM authors. Сюда можно вставить любой допустимый! запрос, которьй' также может содержать свои производные таблицы. Обратите внимание на при менение псевдони.ма таблицы. Это обязательное требование производных таблш Transact-SQL - вы должны включать его независимо от того, ссылается запрос на другие объекты или нет.

Так как Transact-SQL 1юддерживает форму оператора SELECT без использо вания таблиц, вы также можете прн.менять производные таблицы для создани} логических таблиц из ничего, не задействуя другие объекты базы даниых. Во: пример:

SELECT * FROM

(SELECT flyweight AS WeightClass. G AS LowBound. 112 AS HighBound UNION ALL

SELECT bantamweight AS WeightClass. 113 AS LowerBound, 118 AS HighBound UNION ALL

SELECT featherweight AS WeightClass. 119 AS LowerBound. 126 AS HighBound UNION ALL

SELECT lightweight AS WeightClass. 127 AS LowerBound, 135 AS HighBound UNION ALL

SELECT welterweight AS WeightClass. 136 AS LowerBound, 147 AS HighBound UNION ALL

SELECT middleweight AS WeightClass. 148 AS LowerBound. 160 AS HighBound UNION ALL

SELECT light heavyweight AS WeightClass. 161 AS LowerBound. 175 AS

HighBound

UNION ALL

SELECT heavyweight AS WeightClass. 195 AS LowerBound. 1000 AS HighBound) W

ORDER BY W.LowBound

WeightClass LowBound HighBound

В этом примере мы создаем производную таблицу, содержащую три столбца и восемь записей. Каждый оператор SELECT представляет собой единственную запись в этой виртуальной таблице. Записи таблицы склеиваются вместе с применением нескольких операторов UNION. Таблица на само.м деле нтп'де не существует - это просто логическа51 конструкиия. Вы люжете считать производную таблицу вре.менны.м объектом VIEW - он существует в период вьпюлнения запроса, а зате.м исчезает. Поскольку оператор SELECT можно считать таблицей, это по определению означает, что результат SQL-запроса сам является таблицей - результирующей таблицей. Вот при.мер, в котором обычная таблица соединяется с производной:

CREATE TABLE #boxers (Name varchar(30). ieight flcat)

INSERT #boxers VALUES (Glass Joe. 112) INSERT fboxers VALUES (Piston Huri-icane. 176) INSERT fboxers VALUES (Bald Bull. 298) INSERl #boxers VALUES (Sugar Ray Ali. 151) INSERT fboxers VALUES (Leon Holmes, 119; INSERT fboxers VALUES (George Lisbon, 139) INSERT fboxens VALUES CLanny Leonand, 115) INSERT #boxers VALUES (Mike Mooncalf, 134) SELECT B,Name, B,Weight, W,WeightClass FROM #boxens В,

(SELECT flyweight AS WeightClass, 0 AS LowBound, 112 AS IlighBouna UNION ALL

SELECT bantamweight AS WeightClass, 113 AS LowenBounC, 118 AS HighBound UNION ALL

SELECT feathenweight AS WeightClass, 119 AS LowenBound, 126 AS HighBound UNION ALL

SELECT lightweight AS WeightClass, 127 AS LowenBound, 135 AS HighBound UNION ALL

SELECT welterweight AS WeightClass, 136 AS LowerBound, 147 AS HighBound UNION ALL

SELECT middleweight AS WeightClass, 148 AS LowenBound, 160 AS HighBound UNION ALL

SELECT light heavyweight AS WeightClass, 16! AS LowenBound, 175 AS

HighBound

UNION ALL

SELECT heavyweight AS weightClass, 195 AS LowenBound, 1000 AS HighBound) W

WHERE B.Weight BETWEEN W,LowBound and W,HighBound ORDER BY W,LowBound

Name Weight WeightClass

Glass Joe 112.0 flyweight

Lanny Leonand 115,0 bantamweight

Leor Holmes liJ.C ftjtrerwegp!.

Mike Mooncalf 134.0 licntwe.sn-L

George Listen 139 0 weHerweigfit

Sugar Ray Ali 151,0 middleweight

Bald Bull 298.0 heavyweight

Этот запрос сначала создает таблицу, содержащую список BbLNLbimjLCHHbix тейнеров и вес каждого конгейрнера (.это Liania <<обычнаЛ'> таблица). Зате соединяет производную таблицу, Г!редстав.ченную в предыдущем примере, бы разбить контейнеры по весовым категориям. Заметьте, что олпн из тейнеров не вошел в результирующее лиюжество, поскольку он не попадд! в одну из весовых категорий, определенных производной таблицей.

Конечно, этот запрос люжно значительно упростить, если использовать раторы CASE, но цель унр;окпе1П!я была !юказать .vющь производных табл1Щ. , мы создали таблицу с лнюжество.м sannceii с помощью UNION и нескол простых операторов SELECT без необход![мостн прн.мснения реальной табт. Этот при.мер иллюстрирует некоторые уиикатьнь!е возможности произво, таблиц. А здесь показана их необходимость:

SELECT s.state, st.stor r,dme,s.totalsales.rank=COUNT(*) FROM (SELECT t.State, t.storjd, SUM(s.qty) AS TotalSales FROM sales s JOIN stores t ON (s .stor id=t,storjd) GROUP BY t,state, t.storjd) s JOIN (SELECT t,state, t.stor id, SUM(s,qty) AS TotalSales FROM sales s JOIN stores t ON (s.stor id=t.storJd) GROUP BY t,state. t.stor -:d) t ON (s.state=t.state) JOIN stores st ON (s.stor Td=st,stor id) WHERE s.totalsales <- t.totalsaies GROUP BY s.state,St.stor name.s.totalsales HAVING COUNK*) <=1 ORDER BY s,state, rank

state stor name totalsaies rank

CA Barnums 125 1

OR Bookbeat 140 1

WA Doc-U-Mat: Ouality Laundry and Books 130 I

Данный запрос возвращает для каждого штата названия магазинов с .ма мальными продажами. Как от.мечатось в обсуждении SELECT ТОР и, этот за выполняет то, что не может сделать расширение ТОР и - он возврашает сг пированное ТОР п результирующее множесгво. В этом случае производная лица требуется, чтобы определить продажи для каждого магазина, не нриб к использованию объекта VIEW. П снова, производные таблицы очень пох на подставляемые представления. После подсчета продаж для каждого мага: производная таблица соединяется ca.\La с собой с помощью столбца state (ш для определения количества других магазинов в родном штате каждого маг на с количеством продаж, .меньшим, чем у дан!Юго {ни. са.мом деле мы де: все наоборот, чтобы .магазины с больших! числом продаж получили меньшие ла, то есть большие ранги). Это hhcjlo служит для ранжирования каждого зина по сравнению с другими в его штате. Предложсп1!е HAVING затем испол ет этот ранг, чтобы отфильтровать все магазгшы в штате, кро.ме лидера про. Вы можете легко изменить константу в предложении HAVING, чтобы вклю