Функция DATABASEPROPERTYO подобна COLUMNPROPERTY() п том, что позпра-шет информацию о свойствах базы данных. Эта фу1и<ция принимает два пара-[етра: имя базы данных и имя свойства. Вот несколько примеров использова-ия функции DATABASEPROPERTYO:

SELECT

DATABASEPROPERTY(pubs.IsBulkCopy). DATABASEPROPERTY(pubs.Version). DATABASEPROPERTY(pubs.IsAnsiNullsEnabled). DATABASEPROPERTYCpubs,IsSuspect). DATABASEPROPERTY(pubs.IsTruncLog)

1 515 0 0 1

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

Функция TYPEPROPERTY() возвращает информацию о свойствах типов дан-1ых, Эта функция принимает два параметра: имя типа данных и строку, указы-иющую имя необходимого свойства. Тип данных может быть системным или юльзовательским. Вот запрос, применяющий функцию TYPEPROPERTY():

SELECT TYPEPROPERTY(id.AllowsNull)

Функции идентификаторов

[DENT SEED() и IDENT INCR() возвращают начальное значение и шаг приращения для столбцов идентификаторов. Каждая функция принимает один параметр - имя таблицы, с которой предстоит работать (указывается таблица, а пе столбец, гак как в таблице может быть только один столбец-идентификатор).

Ниладическая (то есть функция, которая пе требует указания скобок после своего имени) функция IDENTITYCOL возвращает значение столбца-идентификатора указанной таблицы. Ее можно применять в выражениях SELECT для получения значений столбцов-идентификаторов без указания их имен, напри.мер:

CREATE TABLE #testident (kl int identity, cl int DEEAULT 0) INSERT #testident DEFAULT VALUES INSERT #testident DEFAULT VALUES INSERT #testident DEFAULT VALUES SELECT IDENTITYCOL FROM #testident

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

Функция IDENTITYO позволяет создавать таблицы, содержащие новые столбцы-идентификаторы используя конструкцию SELECT...INTO. Ранее для этого требовалось добавить столбец-идентификатор после создания таблицы при помощи конструкции ALTER...TABLE. Вот пример, используюнгий 4)ункцию IDENTTFYO:

их Зак 70

SELECT AuthorIcl=IDENTITY(iRt). aujname. aujname INTO #testiclent FROM authors USE tempdb

SELECT C0EUMNPR0PERTY(0BJECT ID( Kestident). AuthorId. Isldentity)

Хотя, строго говоря, функция NEWIDO не имеет ничего общего со столбцами-идентификаторами, она сходна с IDENIllY() в том, что генерирует уникальные идентификаторы. Она возвращает значение типа uniqueidentifier и наиболее часто используется для генерации значения по умолчанию в столбцах. Вот пример:

CREATE TABLE #testuid (kl uniqueidentifier DEFAULT NEWIDO. k2 int identity) INSERT #testuid DEFAULT VALUES

INSERT ftestuid DEFAULT VALUES

INSERT #testuid DEFAULT VALUES

INSERT ftestuid DEFAULT VALUES

SELECT * FROM #testuid

kl k2

F4F407B5-244F-11D3-934F-005004044A19 1 F4F407B6-244F-11D3-934F-005004044A19 2 F4F407B7-244F-11D3-934F-005004044A19 3 F4F407B8-244F-11D3-934F-005004044A19 4

Тип данных uniqueidentifier соответствует типу GUID в Windows. Это уникальное значение, которое гарантированно уникально среди всех сетевых компьютеров в мире. Можно использовать ключевое слово ROWGUIDCOL для назначения одного поля таблицы типа uniqueidentifier в качестве глобального идентификатора записи. Как только это будет сделано, станет возможно применить ROWGUIDCOL, как и IDENTITYCOL, для ссылки на столбец таблицы, подобно этому:

CREATE TABLE #testguid (kl uniqueidentifier ROWGUIDCOL DEFAULT NEWIDO.

к 2 int identity)

INSERT #testguid DEFAULT VALUES *

INSERT #testguid DEFAULT VALUES INSERT #testguid DEFAULT VALUES INSERT #testguid DEFAULT VALUES SELECT ROWGUIDCOL FROM ftestguid

Индексные функции

Функция INDEX COL() возвращает имя указанного столбца индекса. Ее можно использовать для перебора индексов таблицы, отображая каждьн набор ключевых столбцов по .мере продвижения. Вот код примера, демонстрирующи! применение INDEX COL():

SELECT TableName=OBJECT NAME(id). IndexName=name. KeyName=INDEX COL(OBJECT NAME(id).indid.1) - Получить первый ключ FROM sysindexes

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

TableName IndexName KeyName

authors UPKCL auidind au id

authors publishers titles titles titleauthor titleauthor titleauthor stores sales sales sales

aur.inind UFKCL pubind UPKCL titleiGind titleind UPKCLJaind auidind titleidind UPK store,d UPKCL sales titleidind

au iname pub id titlo id tite au id au id title :d stor 1d stor id titlejd

WA Sys payteriris lAl payterms

Обратите внимание, что системное представление INFORMATION SCHEMA. KEY COLUMN USAGE дает доступ к той же информации. Опрос представления KEY COLUMN USAGE представляет собой соответствующи!! ANSI SQL-способ получения информации о столбцах индексов. Еще одно его преимущество состотгг в том, что он устойчив к изменениям .между вьптусками SQL Server в системных таблицах, на основе которых он построен.

Функция INDEXPROPERTYO, подобно функциям COLUMNPROPERTY() и DATABASEPROPERTYO, возвращает информапию о схеме. Как и COLUMNPROPERTY(), она принимает три параметра: идеитифтнсатор таблицы, иа которой создан иидекс, имя индекса и строку, указывающую иа тип инс[)ор.мации, которую требуется гюлучить.

SELECT

TableName=CAST(OBJECT NAHE(id) AS varchandS)). IndexName=CAST(name AS VARCHAR(20)).

KeyName=CAST(INDEX COL (OBJECT NAME (i d). 1 ndd. 1) AS VARCHAR(30)). Clusteredl =CASE INDEXPROPERTYdd.name. IsClustened) WIEN 1 THEN Yes ELSE No END.

Unique? -=CASE INDExPROPERTY{id,name. IsUnique) WHEN I THEN Yes ELSE No END EROM sysindexes

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

TableName IndexName

KeyName

Clustened? Unique?

authons UpKCL au--dind au 1d Yes Yes

authons aunmind aujname No No

publishers UPKCL pub;nd pub id Yes Yes

titles UPKCLJitleidind titlejd Yes Yes

titles titleind title No No

titleauthor UPKCL taind au id Yes Yes

titleauthor auldinri au id No No

titleauthor titleidind title id No No

stores UPK stoneid stor 1d Yes Yes

sales UPKCL sales stor 1d Yes Yes

sales titleidind title ld No No

sales WA Sys paytenms lAl paytenms No No

Снова ггредпочтительиее исполызовать систе.чпгое ггредставлеиие KEY COLUMN USAGE для получения информации такого типа, иежелгг огграшивать таблицу sysindexes непосредственно.

STATS DATE() возвращает дату последггего обновления индексной статистики для указанного индекса. Это удобгго для оггределегигя времени, когда следует

выполнить команду UPDATE STATISTICS для индексов ба.зы данных. Можно легко написать запрос, проверяюини! дату последнего обновления статистики для каждого индекса и выполняющий ко.манду UPDATE STATISTICS для тех индексов, у которых статистика устарела. Вот запрос, который это делает:

DECLARE с CURSOR FOR SELECT

Tab! eName=OBJECTJAME(1 d). IndexName=name.

StatsUpdated=STATS DATE(id. 1ndi d) FROM sysindexes WHERE

OBJECTPROPERTY(id.IsSystemTable)=0 AND indid>0 AND ind1d<255

DECLARE ©tname varcharOO). ©iname varcharOO). ©dateupd datetime

OPEN с

FETCH с INTO ©tname. ©iname. ©dateupd

WHILE (©©FETCH STATUS=0) BEGIN

IF (SELECT DATEDIFF(dd.ISNULL(©dateupd. 1900010r).GETDATE()))>30 BEGIN PRINT UPDATE STATISTICS +©tname+ +©iname EXEC(UPDATE STATISTICS +©tname+ +©iname)

FETCH с INTO ©tname. ©iname. ©dateupd

CLOSE с DEALLOCATE с

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

UPDATE STATISTICS authors UPKCl. auidind UPDATE STATISTICS authors aunmind UPDATE STATISTICS publishers UPKCL pubind UPDATE STATISTICS titles UPKCLJitTeidind UPDATE STATISTICS titles titleind UPDATE STATISTICS titleauthor UPKCL taind UPDATE STATISTICS titleauthor auidind UPDATE .STATISTICS titleauthor titleidind UPDATE STATISTICS stores UPK storeid UPDATE STATISTICS sales UPKCL sales UPDATE STATISTICS sales titleidind

Конечно, можно достичь тех же результатов при помощи систе.чпюй хранимой процедуры sp updatestats. Она обновляет статистику для каждого индекса каждой таблицы базы данных, но может быть, вы не захотите обновлять всю статистику, возможно, потребуется действовать избирательно. Обновление статистики на больших таблицах может занять много времени, иоэто.му вы будете выполнять данную операцию только для таблиц, которые выигрывают от этого, и тогда, когда загрузка системы мала. Помните также, что SQL Server может поддерживать статистику по нидекса.м автоматически, что уменьшает потребность в применении UPDATE STATISTICS. Можно включить автоматическую гене-

19990229

Данный запрос возвращает 19990229, так как 1999 не был високосным, это означает, что 29 февраля - неверная дата.

Функция DATALENGTHO возвращает длину данных, хранимых в столбце, в отличие от длины самого столбца. Хотя эта функция может быть использована с любым типом данных, чаше всего она применяется со строковыми, двоичными и BLOB-полями, поскольку они могут иметь переменную длину. Длина данных фиксированного типа данных (такого как int) никогда не меняется, вне зависимости от значения поля. DATALENGTH() имеет больше применений, чем можно предположить, основная часть которых связана с форматированием данных. Вот код примера из предыдущей главы, оригинально применяющий функцию DATALENGTHO:

CREATE TABLE #аггау (kl int identity, arraycol varchar(8000)) INSERT #array (arraycol) VALUES CLES PAUL +

BUDDY GUY +

JEFF BECK +

JOE SATRIANI )

INSERT #array (arraycol) VALUES (STEVE MILLER +

EDDIE VAN HALEN+ TOM SCHOLZ )

INSERT #a may (arraycol) VALUES (STEVE VAI +

ERIC CLAPTON + SLASH + JIMI HENDRIX + JASON BECKER + MICHAEL HARTMAN)

UPDATE #array

рацию статистики для заданной таблицы при помощи процедуры sp autostats или для всей базы при помощи процедуры sp dboption.

Функции для работы с данными

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

CREATE TABLE #testis (cl char(B) NULL) INSERT #testis VALUES С19990131) INSERT #testis VALUES (OOOOISD INSERT #test1s VALUES (19990229) INSERT #testis VALUES (20000229) INSERT #test1s VALUES (19990331)

INSERT #test1s VALUES (20000331)

SELECT * FROM #test1s WHERE ISDATE(cl)=0

SET arraycol =

LEFKarraycol. (3*15))+MUDDY WATERS +

RIGHT(arraycol.CASE WHEN (DATALENGTH(arraycol)-(4*15))<0 THEN 0 ELSE DATALENGTH(arrayco1)-(4*l5) END) WHERE kl=2

SELECT Elementl-Element2 Element3= Element4-Elements-Element6=

FROM #array

SUBSTRINGOrraycol SUBSTRINGOrraycol 6UBSTRING(arraycol 6UBSTRING(arraycol SUBSTRING(arraycol SUBSTRINGOrraycol a

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

.(0*15)+1.15) .(1*15)+1.15) .(2*15)+1.15) .(S-lSj+l.lS) .(4*15)+1.15) .(5*15)+1.15)

Elements

Element4

EES PAUL STEVE MILLER STEVE VAI

BUDDY GUY EDDIE VAN HALEN ERIC CLAPTON

JEFF BECK TOM SCHOLZ SLASH

JOE SATRIANI MUDDY WATERS JIMI HENDRIX

Необычные строковые функции

При помощи функции FORMATMESSAGEO можно форматировать строки, подобно функции printf(). Она принимает параметр, указывающий идентификатор необходимого сообщения, из таблицы master..sysmessages, и список аргументов, которые необходимо вставить в сообщение. FORMATMESSAGEO работает подобно RAISERROR(), но не 1?ызываст ошибку. Вместо этого она возвращает итоговое сообщение в виде строки, с которой можно делать далее все что угодно. К сожалению, FORMATMESSAGEO может работать только с сообщениями из sysmessages. Ее нельзя использовать для форматирования простых текстовых строк. Вот способ, позволяющий обойти это ограничение:

DECLARE ©msg varchar(60). lamsgid int. iapub id varchar(lO). (?inprint int

SELECT iam5gid=ISNULL(MAX(error)+l.999999) FROM master.,sysmessages WHERE error > 50000

SELECT @pubjd=CAST(pub id AS varchar). (ainprint=CDUNT(*) FROM titles GROUP BY pubjd

-- Get the last one

BEGIN TRAN

EXEC sp addmessage (Smsgid. 1.Publ isher: %s has %d titles in print SET gmsg-FORMATMESSAGE(Ipmsgid. @pub id. 131npri nL) ROLLBACK TRAN SELECT lamsg

New message added.

Publisher; 1389 has 6 titles in print

Данный способ добавляет строку в таблицу sysmessages в ра.мках транзакции с единственной целью - манипуляций с этой строкой при помощи FORMATMESSAGEO. Как только строка отфор.матирована, транзакция откатывается, что приводит к тому, что сообщение удаляется из таблицы sysmessages. В конечном счете это позволяет форматировать строки без необходимости постоянно держать их в таблице sysmessages.

Конечно, использовано много кода для raKoii npocToii задачи. Логично было бы обобщить способ при помощи пере.мепхения приведенного выще кода в храии.мую процедуру. К сожалению, это не просто, так как Transact-SQL не поддерживает переменное количество параметров для храии.мой процедуры - нельзя указать неограниченное число иара.метров разного тина, что необходи.\ю для применения воз.можностей функции FORMATMESSAGE().

Систе.мная процедура xp sscanf() предоставляет сходную функциональность и .может обрабатывать nepe.vieniroe число параметров, но, к сожалению, поддерживает только строковые параметрьг Она поддерживает переменное количество параметров потому, что является не обычной хранимой, а расширенной хранимой процедурой, которые пишутся не на Transact-SQL. Они находятся в dll вне сервера и обычно пишутся на С или C-i-i-. На данный .момент, вероятно, будет быстрее преобразовать нестроковые данные в строковые и вызвать xp sscanf() или задействовать простую конкатенацию строк для соединения в строку сообщения.

Функция PARSENAME() удобна для извлечения различных частей имени объекта. И.мена объектов SQL Server состоят из четырех частей: [server. ][database. ][owrier. ]Gbject

Любую из этих частей .можно получить при по.\юши функцтш PARSENAME(), подобно этому:

DECLARE @objname varcharOO)

SET @objname= KHEN.master. dbo. sp wno

SELECT ServerName=PARSENAME(taobjname,4). DatabaseName=PARSENAME(@objname.3). OwnerName=PARSENAME(@objname.2), ObjectName=PARSENAME(@objname.1)

ServerName DatabaseName OwnerName ObjectName

KHEN master dbo sp who

Функция QUOTENAMEO обрамляет строку двойными кавычками, одинарными кавычками или квадратными скобкахщ. Она особенно удобна при построении SQL для выполнения через ЕХЕС(). Вот код примера из предыдущей главы, который использует QUOTENAME() для со,здания SQL кода, исполняемого через ЕХЕС():

CREATE TABLE farray (kl int identity, arraycol varchar(8000)) INSERT #array (arraycol) VALUES CLES PAUL +

BUDDY GUY +

JEFF BECK +

JOE SATRIANI )

INSERT #array (arraycol) VALUES (STEVE MILLER +

EDDIE VAN HALEN+ TOM SCHOLZ )

INSERT #array (arraycol) VALUES (STEVE VAI +

ERIC CLAPTON +

SLASH +

JIMI HENDRIX +

JASON BECKER +

MICHAEL HARTMAN) DECLARE @arrayvar varchar(800C), @select stmnt varchar(8000)

DECLARE @к int. 01 int. @1 int. @с int DECLARE с CURSOR FOR SELECT * FROM #array

SET @select strint= SELECT SET @c=0

OPEN с

FETCH с INTO @k, (aarrayvar

WHILE (@@FETCH STATUS=0) BEGIN SET (ai=0

SET @l=DATALENGTH(@arrayvar)/15 WHILE (@i<@l) BEGIN

SELECT @select strint=@select stmnt+Guitarist+CAST(@c as varchar)+ = 40U0TENAME(RTRlM(SUBSTRING(@arrayvar. (@i*15)+l. 15)) .) + . SET @i=@i+l SET @c=@c+l

FETCH с INTO @k, (aarrayvar END

CLOSE с DEALLOCATE с

SELECT (Pselect stmnt=LEF r(@select strint, DATALENGTH((aselect strint) -1)

EXEC(@select strint ) (результаты сократхдены)

GuitaristO Guitaristl Guitarist2 Guitarists Guitarist4

LES PAUL BUDDY GUY JEFF BECK JOE SATRIANI STEVE MILLER

Очистка даы ных

Задача очистки да1-1НЬ1Х, полученных из источников вне SQL Server, типична. Мигрируют ли даииь1е из старой систе.мы, генерируются ли аппаратно или создаются иным путем, очень часто требуется проверять их на ошибочные значения.

Первый шаг в удалении ошибочных данных - найти их. С этой целью давайте рассмотрим проблему нахождения дублей среди записей таблицы. Недостаточно просто вернуть дублирующиеся заииси - это было бы тривиально при наличии D нашем распоряжении GROUP BY и агрегатных функций SQL. Нам необходимо найти carviH записи, содержащие дублирующиеся значения, чтобы с ними можно было работать. Может потребоваться удалить их, перенести в другую таблицу, испрадзить и так далее. Предположим, что мы и.меем следующую таблицу с идентификаторами служащих и номерами телефонов на случай крайней необходимости- Заказчики считают, что в таблице могут быть повторения и что самый лучший способ найти их - использовать телефонные номера. Вот таблица:

CREATE TABLE #datascrub (EmpID int Identity .

ICENumberl varchar(14), ICENuriber2 varchar(14))

INSERT #datascrub (ICENumberl, ICENumber2) VALUES ((101)555-1212.(101)555-1213)

INSERT #datascrub (ICENumberl, ICENumber2) VALUES ((201)555-1313,(201)555-1314)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((301)555 -1414.(301)5551415)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((401)555-1515.(401)555-1516)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((501)555-1616.(501)555-1617)

INRT #datascrub (ICENumberl. ICENumber2) VALUES ((101)555-1211.(101)555-1213)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((201)555-1313.(201)555-1314)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ( (301)555-1414.(301)555-1415)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((401)555-1515.(401)555-1516)

INSERT #datascrub (ICENumberl. ICENumber2) VALUES ((501)555-1616.(501)555-1617)

Наиболее очеБИДный способ нахождения дублей - Быполнение перекрестного соединения таблицы с самой собой;

SELECT d.EmpId. d,ICENumberl. d.ICENumber2 EROM #datascrub d CROSS JOIN #datascrub a WHERE (d.EmpIdoa.EmpId)

AND (d.ICENumberl=a.ICENumberl)

AND (d.ICENumber2=a.ICENumber2)

EmpId ICENumberl ICENumber2

2 (201)555-1313 (201)555-1314

4 (401)555-1515 (401)555-1516

7 (201)555-1313 (201)555-1314

9 (401)555-1515 (401)555-1516

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

SELECT d.EmpId. d.ICENumberl. d.ICENumber2 FROM #datascrub d

WHERE EXISTS (SELECT a.ICENumberl. a.ICENumber2 FROM #datascrub-a WHERE a.ICENumberl=d.ICENumberl

AND a.ICENjriber2=d.lCENumber2 GROUP BY a.ICENumberl. a.ICENumber2 HAVING COUNK*) >=2)

Этот способ испо.11ьзует GROUP BY и COUNT() для нахождения дублирующихся значений в подзапросе и потом сопоставляет найденные значения со внещни.м запросо.м для ограничения результируюи1его набора. В итоге получается тот же са.мый набор данных, что и у предыдущего запроса, но перекрестное соединение не требуется. С малы.мн таблица.\и1 разнти между этими запроса.мн не будет за.метна. Способ с соединение.м таблицы с собой может оказаться быстрее на малых таблицах, подобных приведенной. Однако че.м больше становится таблица, тем заметнее разигща в скорости выполненги в пользу второго варианта. Оптимизатор способен при.менить предикат EXISTS для возврата из вложенного запроса, как только будет найдена хотя бы одна запись, удовлетворяющая наложенным условиям. Еще лучший способ замещает подзапрос производной таблицей:

SELECT d.EnipId, d. ICENumberl. d.ICENuriber2

FROM i?datascrub d. (SELECT t,ICENumberl. t.!CENumber2 FROM #datascrub t GROUP BY t,ICENumberl. t.ICENumber2 HAVING COUNT(*) >=2) a WHERE (d.ICENumberl=a.ICENumberl) AND (d.!CENumber2=a.ICENumber2)

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

Ранее было сказано, что первый подход обладает двумя фуидаментальны.ми недостатками. Первый - неэффективность. Мы реин-слн его. Второй заключается в то.м, что ИИ исходное решение, ни приведенные после, не находят всех дублей. Чем это вызвано? Дубликаты замаскированы немного более тонко, нежели могло показаться на первый взгляд. Посмотрите вни.мательио на выражения INSERT. Обратите внимание на что-то необычное в телефонных номерах. Точно! Некоторые из них отфор.матированы некорректно, что скрывает потеициашные дубли от 1ШШИХ процедур. Для того чтобы найти все дубликаты, необ.ходимо стандартизировать фор.матирование столбцов, которые будут рассматриваться на пред.мет дублирующихся записей. Лучший! сгюсоб сделать это - убрать фор-.матировапие. Это распространенная ситуацгш и, в зависи.мости от данных, такой способ .может быть важным для удачной очистки. Вот переработанный запрос, учитывающий возможность некорректного форматирования. Обратите внимание на различие в выходных наборах:

SELECT d.EmpId. d.ICENumberl, d.!CENumber2

FROM fdatascrub d, (SELECT ICENumberl=REPLACE(REPLACE(REPLACE(t,ICENumberl.

-, ).

(.).) (.).)

(,).) (.).)

ICENumber2=REPLACE(REPLACE(REPLACF(t.ICENumber2, FROM #datascrub t

GROUP BY REPLACE(REPLACE(REPLACE(t.ICENumberl.-

REPLACE(REPLACE(REPLACE(t. ICFNumber-2 HAVING COUNT(*) >=2) a