WHERE CREPLAC£(REPLACE(REPLACE(d,ICENumbe;-j.-.).( ) ). ) -REPLACE(REPLACE(REPLACE(a. ICENumberl .-. ).{.).). ))

AND (REPLACE(REPLACE(REPLACE(d,ICENumber2.-.),(. ).),)

-REPLACE(REPLACE(REPLACE(a,ICENumber2,-.),(.),),))

EmpId ICENumberl ICENumber2

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

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

3 (301)555-1414 (301)555-1415

8 (301)555-1414 (301)555-1415

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

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

5 (501)555-1616 (501)555-1617

10 (501)555-1616 (501)555-1617

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

Удаление дублей

Когда дубли найдены, их yдaюниe hjhi перемещение в другую таблицу становится простой задачей. Нельзя просто заменить SELECT на DELETE, так как это приведет к удалению оригинальной записи и дубля. Это просто, ио может привести к вашему увольнению. Вместо этого можно создать курсор на основе приведенного ранее запроса и перебирать таблицу. Можно сохранять каждую пару номеров и пото.м перебрать все дубликаты, удаляя нх.

Это будет работать, но есть способ лучше. При.мените свойство SQL Server отбрасывать дублирующиеся ic/иочи при построении индекса для очистки дан-пых. Вот пример:

CREATE ГАВЕЕ #datascrub

(EmpID int identity,

ICENumberl varchar(14).

ICENumber2 varc!iar(14))

CREATE UNIQUE INDEX #datascrub ON #datascrub (ICENumberl. !CENumber2) WITH IGNORE DUP KEY

INSERT #datascrub (ICENumberl. ICENumber2j VALUES С (101)555-1212 .4101)555-1213)

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

INSERT Iddtascrub (ICENumberl, ICENumber2) VALUES ((301)555-1414,(301)555-1417)

INSERT fdatascrub (ICENumberl, ICENumber2) VALUES ((401)555-1515,(401)555-1516)

INSERT #datascrub (ICENumberl, ICENumber2) VALUES ( (501)555-1616,(501)555-1618)

INSERT fdatascrub (ICENumberl, ICENumber2) VALUES ((101)555-1211,(101)555-1213)

INSERT Idatascrub (ICENumberl. ICENumber2) VALUES (ЧгОПббб-ШЗ. (201)555-1314)

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

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

INSERT #datascrub (ICENumberl, ICENumber2) VALUES ((501)555-1б1б',(501)555-1617)

SELECT FROM fdatascrub

Server: Msg 3604, Level 16, State 1, Line 0 Duplicate key was ignored. Server: Msg 3604. Level 16, State 1, Line 34 Duplicate key was ignored.

Здесь главное - опция IGNORE DUP KEYS команды CREATE INDEX. Обратите внимание, что нельзя создать уникальный индекс на таблице, уже содержащей дубликаты, даже указав опцию IGNORE DUP KEYS. Именно поэтому приведенный код сначала создает индекс, потом вставляет данные. Как видим, попытка вставить дублирующиеся данные (в даигюм случае 1слюч определен как пара столбцов) вызывает предупреждение, но не прерывает выполнение пакета запросов.

Обратите внимание, что этот способ работает только с простыми дубликатами, не содержащими разделителей и не требующими удаления лишних символов (то есть по больше!! части с не символьными типами). Если ваши потребности так же сложны, как и в способе с применением функции REPLACE(), применение CREATE INDEX...WITH IGNORE DUP KEYS не поможет, так как SQL Server не сможет различить дубли.

Таблицы итераций

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

Заключение 541

ций. Таблица итерации - это простая таблица, содержащая последовательность чисел, используемых для итеративных типов вычислеиий, а ие для циклов. Такая таблица постоянно хранится в одной из ваших баз данных (размещение ее в базе model приведет к копированию таблзиы в базу tempdb при каждом запуске сервера) и может быть отфильтрована, как любая другая таблтща, при помощи оператора WHERE. Посмотрите иа следующи!! пример, чтобы понять, как это работает.

Допустим, необходи.мо отобразить таблицу квадратов всех чисел от единицы до ста. Если эта таблица уже сушествует: CREATE TABLE iterate (I int identity(-lOO.l))

DECLARE gloop int SET @loop=-100

WHILE ((aioop<101) BEGIN

INSERT iterate DEEAULT VALUES SET @loop=@loop+l

SELECT * FROM iterate

-100

( .)

(...)

написание запроса простое:

SELECT SQUARE=SQUARE(I) FROMiterate WHERE I BETWEEN 1 AND 100

Oh быстрый и куда более эффективный, нежели цикл WHILE. Обратите внимание на применение отрицательного начального значения столбца-идентификатора таблицы. Это позволяет выполнять вычисления и для отрицательных чисел. Хорошим дополнением SQL Server могла бы стать автоматическая таблица определенного вида (наподобие таблицы dual в Oracle), которую можно было бы указать в запросе, подобно столбцу-идентификатору с начальны.м значением и приростом. Это уменьшило бы потребность в постоянной таблице итераций.

Заключение

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

Приложение Предлагаемые ресурсы

Ниже приведен cihicok ресурсов, которые могут быть полезны для улучшения ваших знаний по SQL Server, Transact-SQL и SQL вообще. Этот список разделён на две группы: Книги и Интернет-ресурсы .

Книги

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

Bjeletich, Sharon, Greg Mable, et al 1999. Microsoft SQL Seiver 7.0 Unleashed. Sams PublLshing. ISBN: 0-672-31227-1.

Celko, Joe. 1999. Joe Celkos Data & Databases: Concepts in Practice. Morgan Kaufmann Publishers. ISBN: 1-55860-432-4.

Celko, Joe. 1997. Joe Celkos SQL Puzzles & Answers. Morgan Kaufmann Publishers. ISBN: 1-55860-453-7.

Celko, Joe. 1995./oe Celkos SQL for Smarties. Morgan Kaufmann Publishers. ISBN: 1-55860-323-9.

Date, C. J. 1994. An Lntroduction to Database Systems. Addison-Wcslev. ISBN: 0-201-38590-2.

Date, C.J. with Hugh Darwen. 1993. A Guide to the SQL Standard. Addison-Wesley. ISBN: 0-201-96426-0.

Groff, James R. and Paul N. Weinberg. 1999. SQL (The Complete Reference). Osborne. ISBN: 0-07211845-8.

Kline, Kevin, Lee Gould, and Andrew Zanevsky. 1999. Transact-SQL Programming. OReilly & Associates. ISBN: 1-56592-401-0.

Melton, Jim and Alan R. Simon. 1993. Understanding the new SQL: A Complete Guide. Morgan Kaufmann Publishers. ISBN: 1-55860245-3.

Rozenshtein, David, Anatoly Abramovich, and Eugene Birger. 1995. Optimizing Transact-SQL: Advanced Programming Techniques. SQL Forum Press. ISBN: 0-9649812-0-3.

Интернет-ресурсы

Solomon, David and Ray Rankins, el, al. 1996. Microsoft SQL Seiver 6.5 Unleashed. Sams Publishing. ISBN: 6-672-30956-4.

Soukup, Ron. 1997. Inside Microsoft SQL Seiver 6.5. Microsoft Press. ISBN: 1-57231-331-5.

Soukup, Ron and Kalen Delaney. 1999. Inside Microsoft SQL Server 7.0. Microsoft Press. ISBN: 0-7356-0517-3.

Интернет-ресурсы

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

сотр.databases.ms-sqlserver

forumsb.compuserve.com/gvforums/ defauLt.asp?srv=sqLserver

microsoft, public.sqLserver. programming

www.acm.org

www.microsoft.com/sql

www.ntfaq.com/sql.html www.sqlwire.com

www.swynk.com

www.sql.ru

Общецелевая группа новостей, посвященная Microsoft SQL Server

Посвященный SQL Server форум CompuServe (доступен также через команду GO MSSQL в программном обеспечении CompuServe)

Поддерживающая Microsoft группа новостей, посвященная задачам программирования под SQL Server, особенно касающимся Transact-SQL

Основной общедоступный сайт Ассоциации вычислительной техники (Association for Computing Machinery) - первого в мире образовательного и научного компьютерного объединения

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

FAQ (Часто задаваемые вопросы) по SOL Server

Сайт, посвященный отслеживанию новостей, касающихся SQL Server

Независимый от Microsoft сайт, содержащий полезную информацию по некоторым продуктам Microsoft, включая SQL Server

Материалы по технологиям клиент-сервер (Microsoft SQL Server, InterBase, Oracle, MySQL). Переводчики книги, которую вы держите в руках, являются активистами форума sql.ru

Алфавитный указатель

DBCC CALLFULLTEXTO, 508 оптимизации

INSERT, 364 транзакция

ROLLBACK TRAN, 305 хранимая процедура

ALTER PROCEDURE, команда, 327 #, 75, 114, 116 ##, 75, 114, 116 %, оператор, 262 &, оператор, 89 ...PROPERTYO, функция, 444 ®@CURSOR ROWS,

переменная, 289 @@ERROR, 308

@@ERROR, переменная, 125, 140 @@LOCK TIMEOUT, 362 ®@MAX PRECISION, 349 @@MICROSOFTVERSI0N,

переменная, 519 ®@NESTLEVEL, 343 @@NESTLEVEL, переменная, 351 ©©OPTIONS, 343 @@PROCID, 343

@@ROWCOUNT, переменная, 141 ®@SPID, 343 ®@TRANCOUNT, 343 ®@TRANCOUNT, переменная,

313, 320 ®@VERSION, переменная, 31 ®buffersonly, 427 ®ERROR, переменная, 345 ©execsql, переменная, 73 ®spid, 432 +, оператор, 58

<>, оператор, 163 =, оператор, 163 3GL, 55, 195

ABS(), функция, 197

ALL, предикат, 163

ALTER PROCEDURE, команда, 327

ALTER TABLE, оператор, 75

ALTER TABLE...DISABLE

TRIGGER, 351 ALTER TABLE...ENABLE TRIGGER,

команда, 351 ANSI /ISO, 286 ANSI null default, 526 ANSI/ISO SQL, стандарт, 23 ANSI DEFAULTS, 305 ANSI WARNINGS, 80 ANY, предикат, 163 ASC, 444

ASC, ключевое слово, 183 AVG(), функция, 218

В

BATCHSIZE., 366 BEGIN DISTRIBUTED

TRANSACTION, команда, 306 BEGIN TRAN, команда, 313 BETWEEN, оператор, 35 BETWEEN, предикат, 152 BLOB, binary large object, 81-82

UPDATETEXT, 86

WITH LOG, 88

WRITETEXT, 87

модификация данных, 85

обращение, 83 BLOB-индекс, 112

BLOB-ТИПЫ данных, 112 Bookmark lookup, 382, 407 Bulk Copy Program, 128 BULK INSERT, 360, 473, 493 BULK INSERT

CHECK CONSTRAINTS., 129 BULK INSERT, команда, 128, 132,

307, 364 identity-столбиы, 129

С, язык, 70, 334, 535 С++, 33

С++, язык, 334, 535 CASCADE, 109 CASE, 196

CASE, оператор, 58, 282 CASE, функция, 33, 57, 161, 220, 228 CASE-выражение, 131 CAST(), функция, 32, 143 CHAR(), функция, 272, 426 CHARINDEXO, функция, 61 check-ограничения, 132 COALESCEO, функция, 100 COBOL, 41

COLUMNPROPERTYO,

функция, 483, 528 COLUMNS UPDATED(),

функция, 351 COMMIT, команда, 44, 314 COMPUTE, 52 CONTAINSO, предикат, 486 CONTAINS(), функция, 482 CONTAINSTABLEO, функция, 489 CONVERTO, функция, 32 COUNTO, функция, 171 COUNT(*), функция, 102 CREATE DEFAULT, команда, 114 CREATE INDEX, команда, 113,

454, 540 CREATE PROC, команда, 325 CREATE PROCEDURE, команда,

323, 325, 327 CREATE RULE, команда, 114 CREATE STATISTICS, команда, 450 CREATE TABLE, команда, 108 CREATE TABLE, оператор, 248 CREATE TRIGGER, команда, 350 CREATE VIEW, команда, 114,

117, 185

CREATE, команда, 114 CROSS JOIN, 39 CUBE, оператор, 176 CURRENT TIMESTAMP, функция, 527

CURSOR STATUS(), функция, 298

Data Definition Language, язык, 107 Data Manipulation Language, язык, 119 DATABASEPROPERTYO,

функция, 461, 529 DATALENGTHO, функция, 240, 533 DATEADDO, функция, 224 DATEDIFFO, функция, 49 datetime, 46 dBASE, 30

DBCC ADDEXTENDEDPROCO, 506 DBCC ADDINSTANCEO, 507 DBCC BCPTABLOCKO, 507 DBCC BUFFERO, 507 DBCC DBRECOVERO, 510 DBCC DBREINDEXO, 361 DBCC DBREINDEX(), команда, 454 DBCC DBTABLE(dbid), 510 DBCC DES(), 511 DBCC DROPCLEANBUFFERS, 364,511

DBCC DROPEXTENDEDPROCO, 511

DBCC EXTENTINFOO, 512 DBCC FLUSHPROCINDBO, 512 DBCC FREEPROCCACHE, 368, 512 DBCC FREEPROCCACHEO,

команда, 326 DBCC FREEPROCCACHE,

команда, 364 DBCC HELP(), 506 DBCC IND(), 512

DBCC LOCKOBJECTSCHEMAO, 512

DBCC LOGO, 513

DBCC LOG, команда, 319

DBCC OPENTRANO, команда, 319

DBCC PAGE 0, 513

DBCC PERFMON, команда, 415

DBCC PROCCACHE, 368

DBCC PSS(), 427

DBCC PSS(), команда, 427

DBCC RESOURCE, 515

DBCC SETINSTANCEO, 516

DBCC SHOWCONTIG, 361 DBCC SQLPERF, команда, 415 DBCC TAB(), 516 DBCC TRACEON(), команда, 519 DBCC TRACEO\(3604), 506 DBCC UPDATEUSAGEO,

команда, 453 DBCC UPGRADEDBO, 516 DEALLOCATE, команда, 299 Decision Support System, 307 DECLARE CURSOR, команда, 286 DEFAULT VALUES, 122 DEFAULT, ключевое слово, 120, 340 default-ограничения, 110 DELETE, команда, 29, 137

курсоры,138 DELETE, оператор, 187, 244 DELETE-триггеры, 140 Delphi, язык. 334 DESC, 444

DESC, ключевое слово, 42, 183 DIFFERENCEO, функция, 68 Distributed Management Objects, 128, 494

Distributed Transaction Coordinator

(DTC), 306 DLL, 69

DML-запрос, 108 DML-команда, 82, 119, 305 DML-оператор, 82, 131, 351 ошибки выполнения, 140 DML-операция, 350 ВКЬограничешш, 358 DROP INDEX, ко.\шнда, 454 DROP TABLE, команда, 112 DROP, команда, 112 DR0P EXISTING, 113 DSS, 175

Dynamic Link Libraries, 69 E

EIS, 175

ENCRYPTO, функция, 517 Enterprise Manager, 118, 362, 418

планирование задач, 472

создание сцепариео, 474 Excel, 493

EXECO, функция, 72, 368, 435 EXEC()., 535 EXEC, команда, 325

EXEC, оператор, 453 . EXISTS, предикат, 156, 162, 486

FAST F0RWARD, 367 FETCH, 93

FILLFACTOR, 113-114, 361, 396 firehose-курсор, 273 FIRSTROW/LASTROW, 365 FORMATMESSAGEO,

функция, 534 FORMSOFO, oueparop, 489 FORWARD ONLY, 367 FREETEXTO, предикат, 486, 489 FREETEXTTABLEO, функция, 482,

489, 491 FULL OUTER JOIN, 39-40 FULLTEXTCATALOGPROPERTYO,

функция, 483

GET SID(), функция, 517 GETANSINULLO, функция, 526 GETDATEO, функция, 49, 191 GO, 327

GROUP BY. 173 GROUP BY ALL, 174 GROUPINGO, функция, 54 GUID, 90

HAVING, 228 HAVING, оператор, 180 HAVING, предложение, 41 HOLDLOCK, 85,312 HOST NAMEO, функция, 527

IDENT SEED(), функция, 529 IDENTITYO, функция, 213, 529 IDENTITYCOL, функция, 529 identity-столбец, 12), 201, 243, 280 IF, оператор, 103 IGNORE DUP KEY, 126 IGNORE DUP KEYS, 540 IN, предикат, 159, 161

оптимизация, 161

преобразование к EXIST, 158 Index Tuning Wizard, 362, 411 INDEX COL(), функция, 530

INDEXPROPERTYO, функция, 531 INFORMATION SCHEMA, 89, 436 INFORMATION SCHEMA.SCHEMA

ТА, 452 INSERT, команда, 336 ошибки, 124 удаление дублирующих записей, 126 INSERT, оператор, 28, 119, 187, 237 INSERT-DEFAULT VALUES, 122, 527

INSERT...EXEC, 123, 485

оператор, 463, 472 INSERT...EXEC(), 513 INSERT...SELECT, 122 INSERT-триггер, 132 INTO, ключевое слово, 29 ISABOUTO, функция, 490 ISAM, 274 ISAM-база, 274 ISDATEO, функция, 533 ISNULLO, функция, 100 ISNUMERICO, функция, 533 ISQL, 417

ISQL /W, утилита, 24

KEEPIDENTITY, к/почевое

слово, 129 KEYSET-курсор, 300, 367

LCT ADMI(), функция, 142 LEFT OUTER JOIN, 39-40 LIKE, 481

LIKE, предикат, 154, 486 M

MEDIANO, функция, 206 Microsoft Index Server, 481 Microsoft Search, 481 money, 80

nested triggers, 351

NET START mssearch, 485

NEWIDO, функция, 90, 530

NO ACTION, 109

nooutput, 463

NULL, 98

NULL, ключевое слово, 27 NULL-значения, 98, 157

OBJECTJDO, 517 OBJECT NAME(), функция, 435 OBJECTPROPERTYO, 486 OBJECTPROPERTYO, функция, 334,

339, 483, 497 ODBC, 25, 102 ODSOLE, 474, 493 OLAP, 175,217 OLEDB, 102 OLE-автоматизация, 493 OLTP-приложение, 386 ON, 40

on success action, 471 Open Data Services API, 334 Oracle, 31, 219 ORDER BY, 144, 162, 182 ORDER BY, предложение, 42 OSQL, утилита, 25, 466 OUPUT, ключевое слово, 341

PADJNDEX, 113 PARSENAMEO, функция, 535 PATINDEXO, 481 PATINDEXO, функция, 71, 84 PERCENT, ключевое слово, 144 Perfmon, 414

Performance Monitor, 320, 414, 507 PRINT, оператор, 309, 320, 426 printf(), функция, 347 PRINTFO, функция, 345 Profiler, 413

PWDCOMPAREO, функция, 518 PWDENCRYPTO, функция, 518

Query Analyzer, 411

интерфейс, 24 Query Analyzer, утилита, 24 QUOTENAME(), функция, 535

READ ONLY, ключевое слово, 367 READTEXT, 84 READTEXT, команда, 83 recursive triggers, 351

RETURN, команда, 342 RIGHT OUTER JOIN, 39-40 ROLLBACK TRIGGER, команда, 351 ROLLBACK, команда, 44, 314 ROLLUP, оператор, 177 Row Operations Manager, 131 ROWGUIDCOL, ключевое слово, 530 rowset functions, 489

SARG, 371

SAVE TRAN, команда, 316 SELECT *, конструкция, 31 select into/buik copy, 471 SELECT TOP n, запрос, 148 SELECT TOP n, оператор, 209 SELECT, команда, 30 SELECT, оператор, 30, 32, 40, 122,

138, 143, 147, 164, 172, 191, 232,

240-241, 253, 320 SELECT..INTO, 321, 364 SELECT...INTO, команда, 213 SET ANSI NULLS, команда, 158 SET ANSI NULLS, 102-103, 186 SET aNSI PADDING, 60 SET ANSI WARNINGS, команда, 102 SET

concat null yields null,

SET CURSOR

close on commit,

команда, 340 SET

cursor close on commit,

команда, 295 set default, 109

setidentityjnsert, 121, 244 set implicit transactions,

команда, 305, 308, 340 SET L0CK TIME0UT 362 SET NOCOUNT, 368 SET NOCOUNT ON, 332, 368 SET NULL, 109

SET QUOTEDJDENTIFIER, 186 SET ROWCOUNT, команда, 144, 211 SET TEXTSIZE, команда, 340 SET TRANSACTION ISOLATION

LEVEL, 85 SET TRANSACTION ISOLATION

LEVEL, команда, 303, 309

Show Execution Plan, 408 SIGNO, функция, 197, 202, 225 smalldatetime, 46 smalimoney , 80 SOUNDEXO, функция, 61-62 soundex-код, 66, 68 sp active jirocesses, процедура, 422 sp add jobserver, процедура, 471 sp attach db, 511 sp attach db, процедура, 466 sp autostats, процедура, 533 sp copyfi!e, 461 sp create removable,

процедура, 463 sp cycle errorlog, процедура, 511 sp dbbackup, процедура, 457 sp dbcmpt!evei, процедура, 27 sp dboption, 509 sp dboption, процедура, 3l9, 512 sp decodepagebin, процедура, 396 sp depends, 117 sp detach db, процедура, 511 sp dir, прцедура, 438 sp displayoacrrorinfo,

ыпроцедура, 498 sp dropextendedprocedure,

процедура, 511 sp enable fulltext, процедура, 486 sp execHtesc}!, 367 sp executesql, процедура, 72 sp exporttable, процедура, 494 sp find root blocker, процедура, 431 sp fulltext catalog, 486 sp ful!text column, процедура, 486 sp fulltext tablc, 486 sp generate script, процедура, 472, 480 sp getSQLregistry, процедура, 461,

471, 502 spjielp, 25

sp helptext, процедура, 323 sp hexadeciraal, процедура, 498 sp hcxstring, процедура, 336 sp importtabIe, процедура, 498 spjndexoptions, процедура, 359 spjock, процедура, 432 sp make portable, процедура, 463 sp MS marksystemobject, процедура,

334, 508 sp OACreate, 493 sp object, процедура, 444