BULK INSERT и ограничения (constraints)

в отличие от т])1ит-с])ов, лскла])атив1И)1е ограничения можно заставить выио.1-няться, если включить оииню BULK INSERT CHECK CONSTRAINTS. По умолчанию игнорируются все декларативные 01раииче1Н1я, кроме ограничений уникальности, так что, ec.Hi вам необходимо, чтобы оста.тыиле 01раи1И1ения также выио.1-нялись во Bpe.vHi операции .массов(л'1 .загрх'зки, включите .эту опцию. Учтите, что это может значительно замедлить выполпе.чие onepannir.

BULK INSERT и identity-столбцы

Enie одно замечание о BULK INSERT: по у,\10лчати1Ю данная команда заново создает значения identiiy-столбцов при заг!)узке данных. Очевидно, что, если вы загружаете да1ип.1е в таблицу завнси.мы.ми ссылка.\П1 впеппньх ключеГц это южeт привести к катастрофе. Чтобы реипггь проб.че.му, вюпочиге в команду BULK INSERT ключевое слово KEEPIDENTITY.

UPDATE

UPDATE имеет две основные формы. Первая из ппх используется для модификации таблицы с по.моппло статических snanennii, втора>1 - для .моди(})икац1П1 таблицы значения.\И1 из друг011 таблицы. Вот н])п.ме]) nepnoii формы:

UPDATE #fanousjaycees SET jc=Johnny Cash,

occupationSingor/songwritc-r ,

becamefamous-1955,

notes=Began career selling aop T.d.nces ccc-io-door WHERE jc=John Calhoun

SELECT * FROM #famousjaycees

jc occupation becaiiic fainous note;

Julius Caesan Military leader/dictatDr -45 lock the Roman eorly

notineinont program

Jesus Christ Founded Chmstanity ; Bmtn featured

tellurian, ruminative, and tutelary visitors

Johnny Cash Singer/songw-ite 155 Began career selling

appliances doon-to-coor

Joan Crawford Actress 152-3 Appeared in everything

fnom Grand Hotel to Trcg

James Cagney Actor 193: Tfps prototypical

gangster made a danoy Yankee

Jim Cnoce Singen/songwriten 1972 Would that time wene in

a bottle because you left us way too soon

Joe Celko Author/lecturer 1987 Counts eating and

living indoons among nis favonite hobbies

А вот BTopoii пример:

CRtATE TABLE IseiTii famobsjayiec-s (JC varchardS).

cccupation va-char(25),

becamefanoLis int DEFAULT 0.

notes text NULL)

INSERT #se[rii fanousjaycees VALtES CJonn Candy.Actor.

1981. /our melliferous lifeivss all-too b e)

INSE.RT #semifamousjaycees WLUES (John Cusack.Actor,

1984,Uttered, Go that way, very fast )

INSERT #sem famousjaycees VALUES (Joan Cusack,Actress,

1987,Uncle Fester s avaricious eme fatale)

UPDATE f

SET jc=s,jc,

occupations,occupation,

becamefamous=s,becamefancu5,

notes=s,notes FROM #famousjaycees f

JOIN #semifamousjaycees s ON (f,beccfr,efamous=s,becamefamous) SELECT * FROM #famousjaycees

jc occupation oecame famous notes

Julius Caesar Military leaoer/dictator -45 Took the Roman early

retirement program

Jesus Christ Founded Christianity I Birth featured

tellurian, ruminative, and tutelary visitors

John Calnoun Congressman 1825 Served as VP under two

U,S, presidents

Joan Crawford Actress 1923 Appeared in everything

from Grand Hotel to Trog

James Cagney Actor 1931 This prototypcal

gangster maae a dandy Yankee

Jim Croce Smger/songwriter i972 Would that time were in

a bottle because you left us way too soon

Joan Cusack Actress 1987 Uncle Festers

avaricious femme fatale

Обратите внимание иа при.менение псевдонима д.тя ссььткп на модифицируемую таблицу. Настоящее название таблгщы приведено в предложении FROM. Также взгляните на соединение двух таблиц. Оно созлано с помощью обычного синтаксиса соединений ANSI SQL-92 и позволяет легко находить значения в исходной таблгше команды UPDATE.

Проблема Хэллоуина

Ситуация, когда модифицированная запись пере.меп(ается в списке молифици-pye.vibix занпсеЙ! и поэтому ошпбочно модифицируется несколько раз, известна как проблема Хэллоуина. На заре СУБД эта пробле.ма была повсеместной, потому что производители выполняли группу модификаций по одной заииси за

один р^з. Ec;iii изменится к.чючево!! сто.тбеи. гю которому бы.чи отсортированы записи, может возникнуть ситуация, когда Bainicb 11е])едв1П1ется куда-го в другое место в группе записей, воз.можио, ниже. 1де она снова будет мод1и{)ицнроваиа. Рассмотри.м, напри.мер, такой кол;

UPD.ATE saes SET qty-qty*;.5

Если се])вер не обрабатывает ч-акую ситуацию, и если результнруюи1ее множество отсортировано ио убывап1ио по столбцу qty. каждая .модификация может привести к тому, что запись иередвипется ниже в результ1грующем \пюжестве и будет модифицирована несколько раз при прохождении таблицы UPDATE - классический случай 11робле.\н>1 Х.эллоуина. К счастью, SQL Server распознает ситуации, когда .\южет возникнуть пробле.ма Х.эллоунпа, п авто.матпческп обрабатывает пх. Row Operations Manager определяет, когда \югут возникнуть пробле.\н>1 с перемещением записей н другие типы ошибок, такие как проблема Хэллоуппа (например, .модификация первичных и внешних ютючей), и предпринимает меры, чтобы их избежать.

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

Обратите внимание, что отложенные модификации (deferred updates), подход, использовавшийся в SQL Server до версии 7.0 для решения проблем с перемещением записей, больше не используется. Во многих случая от него было больше вреда, чем пользы, так что большинство программистов, работающих с SQL Server, остались довольны, когда их не стало.

Может показаться, что ко.мбииация хюдифпкашш первич1юго к.чюча и UPDATE-т])иггера, увеличат вероятность возиикповепия проблемы Хэллоуина. В котше конпов, триггер будет видеть данные по мере .модификации, правильно? Неправильно. Триггеры SQL Server выполняются один раз для оператора, а не для заниси и имеют доступ только до и после к снимкам данных, а не к промежуточным состояниям, которые они могут иметь в процессе модификации. Это .может показаться странны.\г, так как триггеры, кажется, выполняются совместно с DML-оиератором, который запускает их, ио это пе так. Код триггера не компилируется в плаи выполнения ко.манды INSERT, UPDATE или DELETE, которая запускает его. Вместо этого он компилируется и кэшируется отдельно, чтобы его .\южпо было использовать повторно, независимо от того, что заставило его выполниться. План выполнения DML-oneparopa реализует переход к любым триг-гера.м, которые он запускает, непосредствепио перед завершением работы или после ее заверпшиия. Учтите, что это недействительно для ограничений. Шаги для каждого ограииче1И1Я таблииы добавляются напрямую в плаи выполнения DML-оператора.

UPDATE и CASE

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

SELECT Litle -d. type, pnce FROM titles

UPDATE titles

SET price=price*CASE title WHEN business THEN 1.5

WHEN mod cook THEN .8

WHEN trad cook THEN ,6

WHEN psychology THEN .5

WHEN popular comp THEN 1.75

ELSE .75

SELECT title id. type, price EROM titles

Применение UPDATE для выполнения check-ограничений

Если вы используете BULK INSERT или какие-либо другие механиз.мы массовой загрузки, которые предоставляет SQL Server, для зафузки данных в таблицу

с ассоцпироианпым INSERl-tpiiiicpom. иы замечите, чло чрпггер ие будет запушен. Можно застаиить BULK INSERT о6])абагЫ1!ать декларативные ограничения, но вы увидите, ччо это значительно снижает п])оизводиче.чьиость операции. Вероятно, операция будет выполняться значительно быстрее, если 1н-норировать ограничения таблицы во вре.мя за1-рузки данных. Как ва])иа1гг, можно проверять ограничения и запускать чрпгге])ы вручную посте выпо.чпешш операщиг Для этого потребуется отдельны!! кол .тля каж'дого огра!1![чен!1я и триггера, и пр!1дется постараться, чтобы не допустить ошибок. Другой. !i воз.можио, лучн!!и 1 способ - выполнить фикти1!ную .мо.Ч!!ф1гкацп!о таб;[иц!)1 1!осле 3ai!epi!!e!i![4 опера!НИ1. Эта фиктивная модиф!!кацпя устапа!з.ч11вает з!1ачен!1я столбцов равны.ми са.\И!м себе. Это заставляет тр!1ггерь! с])аботагь, а огра!1!1че1!!1я - провериться. Есл!1 любая ИЗ записей содержит ие!1равиль!!ые лаи)!ые. онераго]) UPDATE выполн!!тся i!e-удачно. Вот пр!1мер:

CREATE TABLE famousjayceos

(jc varchardS) CHECK (LEFl (jC ,3)<>Jce;,

-- ycia:iab;v,uaef cr.ec.:-огрспичеиие

occupation varchar(2E),

becamefamous mt DEFAJL: 0.

notes text NULL) - Предполагаеюя, чю файл уме б^л создан BULK INSERT famousjaycees FROM D:\G3 TS\dr,o.sjaycees.Dcp -- Проверим, есть ли неправильнь.е flcrr.bie SELECT * FRCM famousjaycees -- Теперь выполним ф'/ктвну* мсд/.оик.си.-ю UPDATE famousjaycees

SET jc=jc. occupafonoccjpation, becaiefcr.osLecdir.efdmous. cotesmotes

occupation

becdme tamous notes

Junius Caesar Military eacor.cctator -45

Jesus Christ Founded Christianity ;

Jonn Calhoun Congnessman 1825

Joan Crawford Actress 1923

James Cagney Actor 1931

Jim Croce Smger/sor.gwnter 19/2

Joe Celko Author/lecturer 1987

Took the Roman early netirement program Birtn featured telbrian. ruminative, and tutelary visitors Served as VP unden two U,S, presidents Appeared in everything from Grand Hotel to Trog

This prototypica) gangster made a dar.dy yankee

Would that time were in a bottle because you left us way too scon Counts eating and livirg indoons amiong his favorite hobbies

Server; Msg 547. Level 15, State 1, Line 1

UPDATE statement confl ;cted witn COLUMN CHECK constraint CK famousjaycee jc 5E8A0973. The conflict occurrec m database tempdb, table famousjaycees, column jC .

The statement has been terminatec.

Как видите, сообще)И1е об ошибке показывает базу да[иплх. таблицу и столбец, в которых находятся неиравнльные данные, так что у вас есть информация, чтобы найти нх.

Ограничение количества записей, обрабатываемых командой UPDATE

Вы можете использовать настройку ТОР. ко.манды SELECT, чтобы офаничить количество 3anHceii, обрабатываемых оператором UPDATE. Эта команда SELECT включается как производная табл1ща в предлол<ение FROM команды UPDATE и соединяется с модифицируемой таблицей, как ниже;

-- Посмотрим, как выглядит таблица перед модификац/ей (огран,ниваес 10 записями дня краткости)

SELECT ТОР 10 au lname. au fname. contract EROM authors ORDER B au id UPDATE a

SET a.contract=0

EROM authors a JOIN (SELECT TOP 5 aujd EROM authors ORDER BY au id) u Oh (a.au 1d=u,au id)

- Теперь показываем таблицу r.ocr.e модификацией (ограничиваен 10 записями для краткости)

SELECT ТОР 10 аи тате, аи fnair.e. contract FROM authors ORDER BY aj id

Перестановка значений столбцов с помощью UPDATE

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

использовать эгп переменные в самом оиерагоре UPDATE. Так как выражения в TransacL-SQL обрабатываются слева направо, вы можете присвоить значение псремен1юи в начале списка SET, а зате.м повторно при.менить ее, чтобы присвоить зпаченне столбцу. Напри.мер, .можно переставить значения двух сто.чб-цов, вот так:

гш~£ TABLE sarnpes (<1 int identity.

saiiipl flcat OEFAUL (randi ;*iCOO;. sai:ip2 ЛоаЕ DEFAULT (rand( )*;00C)

INSERT Isaraes DEFAULT VALUES INSERT #sam3,eb DEEAULE VALUES INSERT #samoes DEFAULT VALUES INSERT #samples DEFAULT VALUES INSERT #san-,Dies DEFAULT VALUES INSERT #samples DEFAULT VALUES INSERT fsaroies DEFAULT VALUES

SELECT * FROM #sampes

DECLARE (as/<ap float

UPDATE samples SET (aswap-satnpl,

sampl=samp2,

saiiip2=(as ap

SELECT * FRCM fsanipleE

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

UPDATE fsatnples SET sampl=samp2, satiip2=sampl

UPDATE и курсоры

Вы можете использовать команду UPDATE, чтобы модифицировать записи, возвращенные обновляемыми курсорами. Это осуществляется с помощью предложения WHERE CURRENT OF команды UPDATE. Вот пример:

CREATE TABLE #famousjaycees (jc varchardS). occupation varchar(25), becamefamous int DEFAULT 0, notes text NULL)

INSERT #famousjaycees VALUES (Julius Caesar.Military leader/dictator. -0045.Took the Roman early retirement program)

INSERT #famousjaycees VALUES CJesus Christ,Founded Christianity 0001,Birth featured tellurian, ruminative, and tutelary visitors) INSERT #famousjaycees VALUES (John Calhoun,Congressman,1825,Served as VP under two U.S. presidents)

INSERT #famousjaycees VALUES (Joan Crawford,Actress.1923,Appeared In everything from Grand Hotel to Trog)

INSERT #famousjaycees VALUES (James Cagney,Actor,193],This prototypical gangster made a dandy Yankee)

INSERT #famousjaycees VALUES CJim Croce,Singer/songwriter,1972,Would that time were in a bottle because you left us way too soon)

INSERT #famousjaycees VALUES (Joe Celko,Author/lecturer,1987,Counts eating and living indoors among his favorite hobbies)

DECLARE jcs CURSOR DYNAMIC FOR SELECT * FROM #famousjaycees FOR UPDATE OPEN jcs

FETCH RELATIVE 3 FROM jcs UPDATE #famousjaycees SET jc=Johnny Cash, occupat1on=Singer/songwriter, becamefamous-1955.

notes=Began career selling appliances door-to-door WHERE CURRENT OF jcs

CLOSE jcs DEALLOCATE jcs

SELECT * FROM Ifamousjaycees jc occupation

John Calhoun Congnessman

became famouS notes

1825

Served as VP under two U.S. presidents

jc occupation

Julius Caesar Military leaoer/dictator -45

Jesus Christ Founded Chr.stianity 1

Johnny Cash Singer/songwniten 1955

Joan Cnawfond Actness 1923

James Cagney Actor 1931

Jim Cnoce Singen/songwniter 1972

Joe Celko Autnor/lecturer 1987

became famous notes

Took the Roman early retirement program Birth featured tellurian, ruminative, and tutelary visitors Began career selling appliances door-to-door Appeared in everything from Gnand Hotel to Tnog This pnototypical gangsten made a dandy Yankee Would that time wene in a bottle because you left us way too soon Counts eating and 1iving indoons among his favonite hobbies

DELETE

Как и ее коллега INSERT, команда DELETE и.меет несколько 4орм- Я не буду рассказывать обо всех этих формах - они достаточно похожи на формы INSERT и UPDATE, так что их использование должно быть очевидны.м.

Однако необходимо обсудить несколько аспектов этой ко.манды. Во-первых, в дополиение к ограничению записей, уд;ыяе.мых DELETE, с по.\ющью констант и переменных в предложении WHERE, вы можете также ссылаться иа другие таблицы. Далее представлена ко.манда DELETE, которая основывается на соединении с другой таблицей. Она удаляет загиюи клиентов из таблицы Customers базы данных Northwind, у которых нет заказов в таблинс Orders:

SET NOCOUNT ON USE Northwmd GO

BEGIN TRAN

SELECT COUNT(*) AS TotalCustomersBefore FROM Customers DELETE с

FROM Customers с LEFT OUTER JOIN Orders о ON {cCustomerlDo.CustomerlD) WHERE o.OrderlD IS NULL

SELECT COUNT(*) AS TotalCustomersAfter FROM Customers GO

ROLLBACK TRAN

Как и в счучае команды UPDATE, количество sannceii, на которые возде1 1ств DELETE, можно ограничить с по.\!Ощыо расширения ТОР п оператора SELECT:

select tcp 10 oriij\m as Before erom sales crofr BY ord nu,Ti delete s

fro;- sales s join (select top Б ord nL.iii erom sales order BY cra nun; a on (s,ord nuni=a,crd num)

select Г0? 10 ord nL;m as After from sales order BY orcjium Beore

4Z3LL922

423LL930

6871

722a

A2975

D4482

D4i82

04492

N914008

N914014

After

D4482

D4482

D4492

N914008

N914014

F2121

P2I21

P2121

P3087a

P3087a

DELETE и курсоры

Вы .можете использовать ко.манду DELETE, чтобы удалять записи, возврашеннь обновляе.\ш1.\и1 курсорами. Подобно ко.манде UPDATE, это осуи1ествляется с Н( мошыо предложения WHERE CURRENT OF. Вот пример:

CREATE TABLE #famousjaycees (jc varchardS), occupation varchar(25). becanefamous int DEFAULT 0. notes text NULL)

INSERT #famousjaycees VALUES (Julius Caesar.Military leader/dictator.-0045.Took th Roman early retirement progra.m)

INSERT #famousjaycees VALUES (Jesus Christ.Founded Cnnst lani ty .0001.B: rLh feature tellurian, ruminative, and tutelary visitors)

Го31СS f- Oiiie г s Бе f о г е 91

lcta :Cub:omer-sAft,er