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

в 11еко10])ын момент вы должны решить, кем 1!Ы будете - политиком пли инженером.

Вы не .можете быть и те.м и другим. Политик зааишиет восприятие от реальности. Инженер заставляет восприятие служить действительности. Это противоиоложиостп. Вы ПС можете делать это одиовремешю.

X В. Кентоп

Как я сказал в предыдущей главе, целью этой книги не является переписывание другими слова.\п1 документации к SQL Server. Я предполагаю, что вы часто обращаетесь к Books Online (BOL), как это делает большинство людей, постоянно работающих с SQL Server.

Имея это в виду, в этой главе мы ие буде.м пытаться исчерпывающе описать DML-команды (Data Manipulation Language, язык манипуляции данны.ми) Transact-SQL. Наша цель - раскрыть неочевидные особенности и показать некоторые приемы и подходы, которые не описаны в BOL. Я предпочитаю дать вам новую информацию, вместо того чтобы перефразировать доку.меитацию, которая находится от вас в нескольких щелчках мышью.

DML-операторы манипулируют данны.ми - они удаляют их, модифицируют, добавляют и выводят. Синтаксис DML Transact-SQL включает команды INSERT, UPDATE и DELETE. Технически SELECT также является DML-командой, ио этот оператор настолько всеобъемлющий и вездесущий в разработке на Transact-SQL, что е.му была предоставлена отдельная глава (смотрите главу 6, Мощный оператор SELECT*).

INSERT

Существует четыре основные формы оператора INSERT Transact-SQL; каждая из них имеет свои особенности. Вот первая и простейшая форма:

INSERT [INTO] targettable [( targetcolumnl[. targetcolumn2])] VALUES ( valuelL value2...])

Как н в случае других форм этой команды, ключевое слово INTO является опциональным. Список столбцов также является опииональны.м, если только вы не указываете значения для к01Пфетнььч столбцов. Элементы в предложешш VALUES могут варьироваться от констант до подзапросов. Вот npocToii пример INSERT:

CREATE TABLE #famobSjaycees (jc vachardS),

occupation varchar(25}.

becamefamous int DEFAuLI 0.

notes text NULL)

INSERT #famousjaycees VALUES (Julius Caesar.Military leader/dictator.

-0045.Took the Roman early retirement program)

INSERT #famousjaycees VALUES CJesus Cnrist.founded Chr-;stan:ty.

0001,Birth featured tellurian, ruminative, and tutelary visitors)

INSERT #fa,Tiousjaycees 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 Trcg)

INSERT #famousjaycees VALUES (James Cagney,Actor.

1931.This prototypical gangster made a dandy Yankee)

INSERT #famousjaycoes VALUES (Jim Croce.Singer/scnQwriter.

1972.Would that time were in a bottle because you left us way too soon)

INSERT #famousjaycoes VALUES (Joe Celko.AuthOr/lecturer.

1987,Counts eating and living indoors among his favorite hobbies)

SELECT * FROM ffamousjaycees

jc occupation became famous notes

Julius Caesar Military leaaer/dictator 45 Took the Rom.an

early retirement program

Jesus Christ Founoed Christianity I Birth featured

tellurian, ruminative, and tutelary visitors

John Calhoun 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 prototypical

gangster made a dandy Yankee

Jim Croce Singer/songwniter 1972 Would that time were in

a bottle because you left us way too soon

Joe Celkc Author/lecturer 1987 Counts eating and living

indoors among his favorite hobbies

DEFAULT и NULL

Чтобы добавить значения по умолчанию в столбцы с ограннченияднг по у.молчанию (default constraints), присоединенны.мн объектами default, в столбцы, которые могут иметь значение NULL, или в столбцы типа timestamp, используйте ключевое слово DEFAULT в.место самих значений. В результате указания DEFAULT столбцы с соответствующи.ми ограничения.ми по умолчанию получают значения по умолчанию. Если указать DEFAULT со cтoлбцaш, которые могут содержать NULL,

HO не имеют значении гю умолчанию, эти столбцы будут установлены в NULL. Столбцы тина timestamp [ихчучат следуюиии- зиачегигя timestamp базы данных.

Чтобы явно указать значение NULL для столбца, который может содержать неопределенные значения, использу11те ключевое слово NULL. Если вы укажете NULL для столбца, который не .может соде[)жать неопределенные значения (или DEFAULT д.тя такого столбца, для которого нет зиачеиия но умо.ччанию), INSERT не выполнится. Вот пример, пллкютрируюши11 при.меиеиге DEFAULT и NULL;

INSERT fffamousjaycees

VALUES (Julius Caesar.Military leader/aictator.DEF.AULT.NULL) SELECT * FRCM #famousjaycees

(результаты сокрап1еиы)

jc occupation became famous notes

Julius Caesan Militany leader/dictator 0 NULL

SET IDENTITY INSERT

Обратите вни.маиие, что вопреки указания.м Books Online вы не всегда должны указывать значения для каждого столбца в списке столбцов (или каждого столбца в таблице, если в INSERT не указан список столбцов). Identity-столбцы можно спокойно опустить в любо.м операторе INSERT - даже в тех, в которых указан список столбцов. Это справедливо вне зависимости от того, в каком месте в таблице содержится identity-столбец. Вот при.мер;

CREATE TABLE #famousjaycees

(jcid int identity. -- Добавим identity-cionCeu

jc varchar(15).

occupation varchar(25),

becamefamous int DEFAULT 0.

notes text NULL

-- Заметьте, что мы опустили его из списка значений INSERT #famousjaycees VALUES (Julius Caesar.Mi 1itany leaden/dictaton.DEFAULT.NULL) SELECT * FROM #famousjaycees

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

jcid jc occupation became famous notes

1 Julius Caesan Militany leader/dictaton 0 NULL

Опционатьными являются ие только identity-столбцы, но их нельзя указывать, если для таблицы не была включена опция SET IDENTFTYJNSERT. SET IDENTITY INSERT позволяет указывать значения для identity-столбцов. Это очень удобно при загрузке данных в таблицу, которая и.меет зависи.мые внешние ключи, ссылающиеся на ее identity-столбец.

В отличие от столбцов Tinia timestamp и столбцов со зиачения.ми по у.молчаиию, вы можете не указывать значение по у.молчаиию для identity-столбца с помощью ключевого слова DEFAULT. Вы ие можете указывать какие-либо значения для identity-столбцов, если только SET IDENTITY INSERT не была включена. Вот пример, показывающий использование SET IDENTITY INSERT;

SET lDEhTlTY lNSERT #faniou5jaycees ON

INSERT Ifamousjaycees (jcid.jc.occupation.becamefamous.notes) VALUES {I.Julius Caesar.Military leader/dictator.DEFAULT.NULL) SET 1DENTITY 1NSERT #famousjaycees OFF SELECT * FROM ffamcusjaycees

jcid jc occuoation became famous notes

I Julius Caesar Military leader/dictator 0 NULL

Обратите вии.мание на указание списка столбцов, в которые добавляются значения, - он необходим, если для identity-столбца указывается значение.

INSERT...DEFAULT VALUES

Вторая форма ко.манды INSERT позволяет указать значения но умолчанию дл; всех столбцов сразу. Она выглядит вот так;

INSERT [INTO] targettable DEFAUtT VALUES

Вот npocTori при.мер;

CREATE TABLE #famousjaycees (jc varchar(15) DEFAULT .

occupation varchar(25) DEFAULT Rock star.

becamefamous int DEFAUtT 0.

notes text NULL

INSERT #famousjaycees DEFAULT VALUES SELECT * FROM #famousjaycees

jc occupation became famous notes

Rock star 0 NULL

В этом примере значения по умолчанию указаны сразу для всех столбцов таб лицы. Как и в случае nepBoii формы команды, если вы исгюльзуете DEFAULT VALUE: со столбца.ми, которые не имеют каких-либо значени1г по умолчанию, вав INSERT не выполнится. Заметьте, что с DEFAULT VALUES нельзя использовать спи сок целевых столбцов. Если вы его укажете (даже если он будет содержать вс столбцы таблицы), INSERT не будет выполнен. Как и в случае ключевого слов DEFAULT, в результате указания DEFAULT VALUES столбцы, которые могут содер жать неопределениые значения без определенных значений по умолчанию, бу дут установлены в NULL. Дополнительной обработки identity-столбцов не требу ется - все работает так, как вы и ожидаете.

INSERT-SELECT

Команда INSERT в третьей фор.ме получает значения для таблицы из оператор SELECT. Вот ее синтаксис:

INSERT [INTO] targettable [( targetcolumnl[. targetcoiumn2])] SELECT sourcecolumnl[. sourcecolumn2] [FROM sourcetable. .]

Так как в операторе SELECT Transact-SQL необязательно указывать предлс жение FROM, данные .могут быть не только из другой таблицы:

CREATE TABLE #far,ou5jaycees2 (jc varchar(15),

occupation varchar(25),

becamefamous int DEFAULT C.

notes text NULL) INSERT #famousjaycees2 SELECT * FROM Ifamousjaycees UNION ALL

SELECT Johnny Canson.Talk snow nost1962,Began caneer as The Great Carsonr SELECT FROM #famousjaycees2

jc occupation became famobs notes

Julius Caesar Military leader/dictator -15 Took the Reman eanly

netinement program

Jesus Christ Founded Christianity 1 Birth featured

tellurian, ruminative, and tutelary visitors

John Calhoun Congressman 1625 Served as VP under two

U.S. presidents

Joan Crawford Actress 1923 Appeared in evenything

fnom Gnand Hotel to Tnog

James Cagney Actor 1931 This prototypical

gangster made a dandy Yankee

Jim Crcce Singer/songwriter 1972 Would that time were in

a bottle because you left us way too soon

Joe Celko Author/lecturer 198/ Counts eating and

living indoors among his favorite hobbies

Johnny Carson Talk show host 1962 Began career as The

Great Carsoni

В этом примере используется UNION для добавления записи, которая уже существует в ИСХОДНО!! таблице.

INSERT...EXEC

Четвертая форма команды INSERT позволяет добавлять в таблицу результирующее множество, возвращенное хранимой процедурой или операторо.м SQL. Вот ее синтаксис:

INSERT [INTO] targettable [( targetedumnl[, targetcolumn2])] EXEC sourceprocedurename - -или--

EXECC SOL statement)

A вот пример использования:

CREATE TABLE #sp who (spid int,

status varchar{30).

loginame sysname,

hostname sysname.

blk int,

dbname sysname,

cmd varchar(16))

INSERT #S3 whc EXEC sp who SELECT * FROM #sp who (результаты сокращены)

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

Расширенные хранимые процедуры

Эта форма также позволяет загружать в таблицы результаты ныполнения рас ширенных храни.мых процедур, хотя при этом добавляется выходная пнфор .мания только осгговного потока расширенной храни.мой процедуры. Вот прн.ме} использования расширенной храни.мой процедуры:

USE master

IF OBJECT IDCsp liStfile) IS NOT NULL DROP PROC sp listfile

CREATE PROCEDURE spjistfile gfilename sysname AS

IF (@filename IS NULL) RETURN(-l)

DECLARE (aexecstr varchar{8000)

SET (aexecstr=TYPE +@filename

CREATE TABLE #fi1 eContents

(output varchar(8C00))

INSERT #f11 eContents

EXEC master. .xp cmdshen Aexecstr

SEtECT * FROM #fileContents

DROP TABLE #fileContents

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

EXEC sp listfile D:\MSSOLAINSTALL\README.TXT output

************************************************

SOL SERVER 7.0 README.TXT

*****************************x*********************************

This file contains important information that you should read prior to installing Microsoft(R) SOL Server(TM) version 7.0. It also contains information about the following SQL Server topics that does not appear in SOL Server Books Online:

INSERT и ошибки

Одна интересная характеристика команды INSERT состоит в ее невосприи.мчиво-сти к фатальным ошибкам пакета команд. Ко\шнда INSERT, которая не выполня-

ется из-за ограничения или ненравильного дуолирующегося значения, не прервет выполнение пакета команд. Если в пакете ко.мапд вьпюлняется группа операторов INSERT и один из ни.х вьнюлияется неудачно, остальных это не коснется. Так оно и должно быть, иначе загрузка больших объемов данных с помощью операторов INSERT значительно усложнилась бы.

Если вы хотите, чтобы весь пакет был выиолиен неудачно, если неудачно выполнена ко.манда INSERT, проверяйте авго.матпческую пере.менную @@ERROR после каждой ко.манды INSERT и реагируйте соответственно. Вот при.мер:

CREATE TABLE #famousjaycees

(jc varchar(15) UNIQUE. -- Ог.ревеляем UNiQUE-ограничение occupation varchar(25), becamefamous mt DEFAULT C, notes text NULL)

INSERT #famousjaycees VALUES CJjius Caesar,Hi otary leader/dictator. -0045.Took the Romap early retirement program) IF (giaERROR <>0) GOTO LIST

-- Теперь попьртае.мся вставить дублирующееся значе1-.1е

INSERT #famousjaycees VALUES (Julius Caesar,Military leacer/dictator.

-0045.Took the Roman early retirement program)

IF ((aERROR <>0) GOTO LIST

INSERT #famousjaycees VALUES (Jesus Christ.Eoundea Christianity. 0001.Birth featured tellurian, rummative. and tutelary visitors) IF ((aiSERROR <>0) GOTO LISi

INSERT #famou5jaycees VALUES (John Calhoun.Cong.-essman. 1825.Senved as VP unden two U.S. pnesidents) IF ((aPERROR <>0) GOTO LIST

INSERT #famousjaycees VALUES (Joan Cnawfond.Actress, 1923.Appeaned in evenything fnom Gnand Hotel to Tnog) IF (laiaERROR <>0) GOTO LIST

INSERT #famousjaycees VALUES (James Cagney.Actor. 1931.This prototypical gangsten made a dandy Yankee) IF (?(aERROR <>0) GOTO LIST

INSERT #famousjaycees VALUES (Jim Cnoce.Singer/songwriter.

1972,Would that time were in a bottle because you left us way too soon)

IF mmoR <>o) GOTO list

INSERT #famousjaycees VALUES (Joe Celko.Authon/lectuner,

1987,Counts eating and living indoons among his favonite hobbies)

LIST:

SELECT * FROM #famousjaycee5

Senver; Msg 2627, Level 14, State 2, Line С

Violation of UNIQUE KE constnaint U0 #famou£jdycees 160F4887. Cannot insent duplicate key in object

#famousjaycees

00000000002E

The statement has been tenmmated.

jc occupation became famous notes

Julius Caesan Militany leaden/dictator -45 Took the Roman eanly

retirement pnogram

Использование INSERT для удаления повторяющихся записей

Помимо сказанного, другой интересный аспект команды INSERT - возможность удаления дублирующихся записей с помощью нее посредством уникального индекса с установленной опцией IGNORE DUP KEY, То есть, если вы добавляете множество записей в таблицу с индексом, для которого установлена опция IGNORE DUP KEY, записи, нарущающие ограничение уникатьности индекса, будут проигнорированы, не прерывая выполнение других вставок. Таки.м образом, чтобы уда7Н1Ть дублирующиеся загшси из табл1щы, вы можете создать рабочую таблицу с идентичной структурой, затем создать в этой таблице индекс с IGNORE DUP KEY, который включает все потенциальные ключи первой таблицы, и вставить в эту таблицу записи из первой таблицы. Вот пример:

CREATE TABLE #famousjaycees (jc varchardS).

occupation varchar(25).

becamefamous int DEFAULT 0.

notes text NULL)

INSERT #famousjaycees VALUES (Julius Caesar,Mi 1itary leader/dictator.

-0045.Took the Roman early retirement program)

-- В целях иллюстрации включим дублирующееся значение

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.

1931.This prototypical gangster made a dandy Yankee)

INSERT Ifamousjaycees VALUES (Jim 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)

CREATE TABLE #famous:aycees2 (jc varchardS).

occupation varcha,-(25).

becamefamous int DEFAULT 0.

notes text NULL)

CREATE UNIQUE INDEX removedups ON #famousjaycees2 (jc.occupation.oecamefamous)

WITH IGNORE DUP KEY

INSERT #famousjaycees2

SELECT * FROM #famousjaycees

SELECT * FROM #famou5jaycees2

Server: Msg 3604, Level 16. State 1, Line 0

Duplicate key was ignored,

jc occupation became famous notes

Julius Cdesdr Military leader/dictator -45 Took the Roman early

retirement program

Jesus Christ Founded Christianity

John Calhoun Congressman

Joan Cnawfond Actness

James Cagney Acton

Jim Croce Singen/songwriter

I Birth featured

tellurian, ruminative, and tutelary visitors 1825 Served as VP unden two

U,S, presidents 1S23 Appeared in everything

from Gnand Hotel to Trog

1931 This pnototypical

gangster made a dandy Yankee

1972 Would that time wore

in a bottle because you left us way too soon

1987 Counts eating and

living indoors among his favorite hobbies

Заметьте, что мы не можем включить столбец notes в индекс, так как это столбец тина text и он не может являться потенциальным ключом индекса. Несмотря па это, включение остальных столбцов все равно обеспечивает достаточную гарантию против дубликатов.

Joe Celko

Author/lectuner

INSERT и кластерные индексы

Таблица без кластерного индекса известна как таблица-куча (Ireap table), Заниси, добавляемые в таблицу-кучу, добавляются туда, где в таблице есть свободное место. Если ни в одной из существующих страниц в таблице нет свобод1юго места, создается новая страница, и записи добавляются в нее. Это может со.здать горячую точку в конце таблицы (пользователи, одновременно выгголняющие команды INSERT для этой таблицы, будут соперничать за одни и те же ресурсы). Чтобы избежать такой ситуации, вы должны всегда создавать кластерные индексы для со.здаваемых вами таблиц. Используйте уникальный ключ, распределяющий новые записи равномерно по таблице. Избегайте автоматических, последовательных ключей кластерных индексов, которые также могут привести к горячим точка.м . Переход от таблицы-кучи к таблице с кластерны.уг ипдексо.м с монотонно увеличивающимися ключами - не самое значительное улучшение. Также избегайте неуникальных ключей кластерных индексов. До SQL Server 7,0 они вызывали переполнение CTpanini при добавлении новых записей с дублирующимися ключами, те.м са.мым за.медляя операции и фрагментацию таблицы. Начиная с версии 7,0, к каждому дублирующемуся ключу кластерного индекса добавляется уникальный идентификатор ( uniqueifier , четырехбайтовое последовательное число), чтобы обеспечить его уникальность. Естественно, иа это тратится некоторое время, и необходи.хюсть в это.м отпадает, если с самого начала применять уникальные ключи. Как и с други.ми индексами, старайтесь использовать ключи, которые уравновещивали бы ваши потребности в доступе к данным и их .модификации.

BULK INSERT

В дополнение к стандартной onepauini добавления данных Transact-SQL поддер жнвает массовую загрузку данных с помощью команды BULK INSERT BULK INSER использует средство ВСР (Bulk Copy Program), которое i-ьмелось в SQL Serve на протяжении многих лет. До ее добавления в Transact-SQL ра;!работчики вы зывали внениною утилиту Ьср с помощью xp cmdshell или использовали Distri buted Management Objects (DM0) APL чтобы загружать большие объе.мы дан 1Ш1Х из Transact-SQL. С появлением команды BULK INSERT необходи.\юсть в эти? манипуляциях, по большому счету, отпала. Вот прн.мер:

CREATE TABLE famousjaycees (jc varchardS).

occupation varchar(25),

becamefamous int DEFAULT 0,

notes text NULL)

-- Предполагается, чго файл уже был создан

BULK INSERT famousjaycees FROM D:\GG TS\famousjaycees,bcp

SELECT * FROM famousjaycees

occupation

became famous notes

Julius Caesar Military leader/dictator -45 Jesus Christ Founded Christianity 1

John Calhoun Congressman Joan Crawford Actress

James Cagney Actor

Jim Croce Singer/songwriter

Joe Celko

Author/lecturer

1825 1923

1931

1972

1987

Took the Roman early retirement program Birth featured tellurian, ruminative, and tutelary visitors Served as VP under two U,S, presidents 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 living indoors among his favorite hobbies

BULK INSERT и триггеры

BULK INSERT обходит механизм триггеров SQL Server. Когда вы добавляете записи с помощью BULK INSERT, INSERT-триггеры не срабатывают. Это происходит потому, что средство ВСР в SQL Server не регистрирует добавляемые записи в журнале транзакций, если это возлюжно, и у триггеров просто нет возможности сработать. Однако существует способ решить данную пробле.му с помощью фиктивной модификацшт Подробности читайте в разделе Использование UPDATE для выполнения сЬеск-ограничеши ! далее в этой главе.