SQL Server 2000 Design Study Guide

SYBEX Book Index MCSE: SQL Server™ 2000 Design Study Guide Marc Israel J. Steven Jones Index Copyright © 2001 SYBEX Inc., 1151 Marina Village Parkway, Alameda, CA 94501. World rights reserved. No part of this publication may be stored in a retrieval system, transmitted, or reproduced in any way, including but not limited to photocopy, photograph, magnetic or other record, without the prior agreement and written permission of the publisher. ISBN: 0-7821-2942-0 SYBEX and the SYBEX logo are either registered trademarks or trademarks of SYBEX Inc. in the USA and other countries. TRADEMARKS: Sybex has attempted throughout this book to distinguish proprietary trademarks from descriptive terms by following the capitalization style used by the manufacturer. Copyrights and trademarks of all products and services listed or described herein are property of their respective owners and companies. All rules and laws pertaining to said copyrights and trademarks are inferred. This document may contain images, text, trademarks, logos, and/or other material owned by third parties. All rights reserved. Such material may not be copied, distributed, transmitted, or stored without the express, prior, written consent of the owner. The author and publisher have made their best efforts to prepare this book, and the content is based upon final release software whenever possible. Portions of the manuscript may be based upon pre-release versions supplied by software manufacturers. The author and the publisher make no representation or warranties of any kind with regard to the completeness or accuracy of the contents herein and accept no liability of any kind including but not limited to performance, merchantability, fitness for any particular purpose, or any losses or damages of any kind caused or alleged to be caused directly or indirectly from this book. SYBEX Inc. 1151 Marina Village Pkwy. Alameda, CA 94501 USA Phone: 510-523-8233 www.sybex.com 2942Ind.qxd 7/27/01 10:57 AM Page 769 Index Note to the reader: Throughout this index boldfaced page numbers indicate primary discussions of a topic. Italicized page numbers indicate illustrations. allocation Symbols data allocation, 99–103, 99 extent allocation, 96–98 @@ERROR system function, 393–394 tracking, 103–107, 104 @@IDENTITY global variable, 158–160 alternate keys, 16 .LDF log files, 67 ANSI NULL default option, 155 .MDF primary data files, 67 applications .NDF secondary data files, 67 application access and design, 129 application roles, 664–666 Decision Support System (DSS), 129 Numbers OnLine Transaction Processing (OLTP), 129 ARITHABORT, 328 1NF (First Normal Form), 32–35, 32, 33, 34 artificial keys, 17 2NF (Second Normal Form), 35–36, 36 atomic columns, 29 3NF (Third Normal Form), 36–39, 38 attributes, See also keys 4NF (Fourth Normal Form), 39–40 datatype attributes, 23–24 5NF (Fifth Normal Form), 40 defined, 6, 7 defining, 7–9 autoparametization, 745 A access to databases, 659–664 adding new users, 660–662, 660, 661, 662 B adding roles and users, 663–664 B, meaning of in SQL Server, 291 basics, 659–660 B-Trees, 199 user-defined roles, 662–663 back up issues, and filegroups, 127 accessing data. See data, accessing Balanced Trees, 199 accessing servers. See under SQL Server 2000 batch optimization, 702–703 ACID properties, 396 batches defined, 382–383 AFTER triggers, 407–408 BCNF (Boyce/Codd Normal Form), 39 aggregates BCP (bulk copy program), 588–607, See also aggregate operators, 464–468 bulk copy exercise using, 468–469 BCP syntax, 589–594 aliases BCP utility, 600–601 ease in reading code and, 458 format files, 594–600 specifying, 450–451 binary attributes datatypes, 23–24 2942Ind.qxd 7/27/01 10:07 AM Page 770 770 BINARY BASE64 option – compilation, stored procedures and BINARY BASE64 option, 503 basics of, 291–294, 292, 293 binary data, isolating, 126 creating, 321–324, 322 Binary Large objects. See BLOBs (Binary Large optimizing queries and, 719 Objects) Codd, E. F., 28 binary strings, 182 collation BLOBs (Binary Large Objects) changing in columns, 172–173 filegroups and, 203 creating databases and, 79–80 table storage and, 182 table character columns and, 163–165, 165 text in row OFF, 199–202, 200, 201, 202 Unicode vs., 164 text in row ON, 202–203 columns blocking, 634–635 adding, 174–176 Boyce/Codd Normal Form (BCNF), 39 altering, 171–174 bulk copy collation of character columns, 163–165, 165 BCP (bulk copy program), 588–607 computed BCP syntax, 589–594 basics, 188–190 BCP utility, 600–601 creating indexes on, 326–328 format files, 594–600 creating statistics on multiple, 331–332 BULK INSERT, 602–607 default values, 227–234 BULK INSERT syntax, 602–605 basics, 227–228 using, 606–607 default constraints, 228–231, 230 business rules, 28 default objects, 232–234 derived columns, 45–46, 45 dropping, 174–176 granting rights to, 674–675 C GUID columns, 162–163 candidate keys, 16 identity columns defined, 540 cascading deletes inserting rows with selected, 538–539 defined, 258 names, and creating tables, 153 enforcing, 260 partitioning columns, 373–375 triggers and, 260 redundant columns, 42, 43–45 cascading updates relational tables and, 29–30 defined, 258 system datatypes in, 176–184 enforcing, 260 date and time, 181 triggers and, 260 listed, 177–180 CD-ROM that comes with this book, numeric, 180–181 xxxiv–xxxvi, xxxv special, 182–183 character attributes datatypes, 23 strings, 181–182 character strings, 181 synonyms, 183–184 check rules, 234–240 text, ntext and image column storage, check constraints, 235–239 198–203, 200, 201, 202 rules, 239–240 updating multiple, 548–550 checkpoint process, 72 user-defined datatypes in, 184–187 Chen, Peter command line utilities defined, 589 definition of entities and, 7 compatibility levels Entity/Relationship model and, 5 basics, 118–120 clustered indexes in SQL Server 2000, 383 accessing data and, 299–302, 300, 301 compilation, stored procedures and, 387–389 2942Ind.qxd 7/27/01 10:07 AM Page 771 composite indexes – data access, analyzing and optimizing 771 composite indexes extracting in XML format, 507 creating, 325–326 functions for transforming, 476–479 described, 296 masking complexity with views, 361 COMPUTE clause, 467–468 modifying using XML, 559–568 conceptual design modifying rows with OPENXML, 564–567 aspects of, 28 using OPENXML, 559–563 importance of, 3 placement and database performance, 129–130 vs. relational model, 5 retrieving using views, 368–369 connections, 609–610 updating constraints, 659–664, See also foreign keys using cursors, 556–558 default constraints, 228–231 using views, 368–369 defining at table creation, 228–230, 230 data access, analyzing and optimizing, 698–767 defining for existing tables, 230–231 analyzing queries, 703–716 unique constraints, 246–252 execution plan analysis, 709–714 defining at table creation, 247–251, 249 exercise in, 714–716 defining at table modification, 251–252 SET FORCEPLAN, 704 vs. default objects, 233 SET NOEXEC, 704–705 vs. triggers, 25 SET SHOWPLAN_ALL, 705–707 correlated subqueries, 473 SET statements basics, 703–704 Create Database statement SET STATISTICS IO, 708 options, 73–94 SET STATISTICS PROFILE, 709 automatic shrinking, 80–81 SET STATISTICS TIME, 709 collation, 79–80 SETSHOWPLAN_TEXT, 707–708 filenames, 73–74 exam essentials, 757–758 manual shrinking, 82–86, 82, 85 key terms, 757 size and growth, 74–79, 77, 78 optimizing queries, 717–732 script, 68 changing an index, 723–726, 724, 726 CREATE INDEX statement, 320–322 indexes and, 717–721 creation scripts, and Generate SQL Scripts limiting the result set, 721–723, 722, 723 utility, 69 query hints and, 726–731 cross joins, 462 optimizing stored procedures and triggers, crow’s feet, 12, 12 744–746 CUBE and ROLLUP, 466–467 Profiler, 731–744 cursors, 480–489 basics, 731–732, 732 basics, 480 creating traces using, 738–739, 739 cursor declarations, 484–488, 555–556 functions and use, 733–738, 735, 736, 737 modifying data inside, 555–558 Index Tuning Wizard, 741–744 retrieving data from, 488–489 lock detection, 740–741 using, 481–484 replaying saved trace files, 739–740 query optimizer, 699–703 basics, 699–700 batch optimization, 702–703 D single statement optimization, 700–702 data, See also datatypes review questions and answers, 758–767 allocation, 99–103, 99 stored procedure debugger, 746–755 allocation tracking, 103–107, 104 exercise in debugging, 749–755, 752, binary data, isolating, 126 753, 754 2942Ind.qxd 7/27/01 10:07 AM Page 772 772 data, accessing – data, importing and exporting using, 746–749, 748 access through URLs using template files, summary, 755–756 508–511 data, accessing, 442–532 access through URLs using XPATH, 514 cursors, 480–489 access through URLs using XSL, declarations, 484–488 511–513, 513 definition and uses, 480 description, 500–501 retrieving data from, 488–489 exercise in extracting data in XML using, 481–484 format, 507 data transforming functions, 476–479 using SELECT with, 502–506 distributed queries, 489–500 data files, See also filegroups; files about, 489–490 databases and distributed partitioned views, 496–500, 497 adding to, 92, 124 linked servers, 490–494 creating databases and, 66–72, 66 OPENROWSET function, 494–496 removing files, 93 exam essentials, 516–517 defragmenting, 339–341 heterogeneous, 495–496 filenames, and database management, indexes and, 297–306 73–74, 73 clustered indexes, 299–302, 300, 301 placement heaps, 297–299, 298, 299 data placement, 129–130 nonclustered indexes, 302–306, 304, 305 default placement, 67 key terms, 518 placement and performance, 127–131 review questions and answers, 519–532 shrinking, 80–90 SELECT statement and, 443–475 automatically, 80–81 aggregate operators, 464–465 files directly, 87–90, 89 COMPUTE clause, 467–468 manually, 82–86, 82, 85 cross joins, 462 with Enterprise Manager, 86–87, 90 CUBE and ROLLUP, 466–467 size and growth options, 74–75 DISTINCT command, 470–471 space management and, 94–107, 95, 96, exercise in joining tables, 463–464 99, 104 exercise in using aggregates, 468–469 allocation tracking, 103–107, 104 exercise in using queries, 453–455 data allocation, 99–103 GROUP BY clause, 465 extents, 95–98, 95, 96 HAVING clause, 466 pages, 98–99, 99 inner joins, 456–458 types, 67 limiting returned results, 451–453 vs. log files, 107 ORDER BY clause, 446–447 data, importing and exporting, 588–624 outer joins, 458–461 bulk copy program (BCP), 588–601 scalars, 471–472 BCP syntax, 589–594 single table SELECT, 444–445, 445 BCP utility, 600–601 specifying aliases, 450–451 format files, 594–600 subqueries, 472–475 BULK INSERT, 602–607 UNION command, 470 BULK INSERT syntax, 602–605 WHERE clause, 447–449 using, 606–607 SQL Server 2000 and, 442–443 Data Transformation Services (DTS), 607–617 summary, 515–516 basics, 608–609, 608 XML and, 500–514 connections, 609–610 2942Ind.qxd 7/27/01 10:07 AM Page 773 data integrity – database logical modeling 773 packages, 608, 612–613 single rows with identity fields, 540–541 tasks, 610–612 single rows with selected columns, 538–539 using, 613–617, 614, 615, 616 inside cursors, 555–558 exam essentials, 618 key terms, 573 key terms, 618 review questions and answers, 574–586 review questions and answers, 619–624, 622 summary, 573 summary, 617–618 UPDATE statement, 544–551 data integrity, 222–286 all rows, 545–546 basics, 222 multiple columns, 548–550 check rules, 234–240 sets of rows, 547–548 check constraints, 235–239 views, 550–551 rules, 239–240 using distributed queries, 568–572 default values, 227–234 using linked servers, 569 basics, 227–228 using OPENROWSET, 570–572, 571, 572 default constraints, 228–231, 230 using OPENXML, 559–568 default objects, 232–234 deleting rows, 566–567 exam essentials, 268 inserting rows, 564–565 foreign key constraints, 252–267, 252 new rowset views, 561–564 cascading foreign key constraints, 260–265, SP_XML_PREPAREDOCUMENT, 559–561 264, 265 updating rows, 565–566 creating and using exercise, 257–258 Data Read and Write Log process, 70, 70 defining at table creation, 253–260 Data Transformation Services (DTS), 607–617 defining at table modification, 266–267 basics, 608–609, 608 real world scenario, 259 connections, 609–610 implementing, 223–227 packages, 608, 612–613 key terms, 268 tasks, 610–612 primary keys, 240–246 using, 613–617, 614, 615, 616 basics, 240–241 database logical modeling, 2–61 defining at table creation, 241–245, 244 denormalization process, 41–47, 41, 42 defining at table modification, 245–246 adding derived columns, 45–46, 45 review questions and answers, 269–286, 269, adding redundant columns, 43–45, 44 271, 273, 278, 279 partitioning tables, 46–47, 46 summary, 267 designing database systems, 2–5 types, 22–28 Entity/Relationship (ER) database model, 5–28 domain integrity, 22–24, 222 basics, 5–7 enterprise integrity, 28, 223 defining entities and attributes, 7–10, 9 entity integrity, 24, 223 domain integrity, 22–24 referential integrity, 24–28, 223 enterprise integrity, 28 unique constraints, 246–252 entity integrity, 24 defining at table creation, 247–251, 249 keys, 16–21 defining at table modification, 251–252 referential integrity, 24–28 data, modifying, 534–586 relationships, 10–16, 11, 12, 13, 14, 15, 16 DELETE statement, 551–554 exam essentials, 48–49 exam essentials, 574 key terms, 49 INSERT statement, 535–544 relational models and normalization, 28–40 multiple rows, 542–544 advanced normalization, 39–40 single rows insert, 535–538 2942Ind.qxd 7/27/01 10:07 AM Page 774 774 database objects, creating and maintaining – databases, creating and managing First Normal Form (1NF), 32–35, 32, recovery model and, 116 33, 34 table of options values, 114–115 relational tables, 29–31 database physical modeling, 64–147 Second Normal Form (2NF), 35–36, 36 creating and managing databases. See data- Third Normal Form (3NF), 36–39, 38 bases, creating and managing review questions and answers, 50–61, 50, 52, exam essentials, 133 55, 56, 58 file placement and performance, 127–131 database objects, creating and maintaining, filegroups, 120–127 357–440 basics, 120–121, 121 exam essentials, 421–422 creating, 121–125, 122, 123 key terms, 421 maintenance and performance, 125–127 review questions and answers, 422–440, 423, key terms, 132–133 429, 435 review questions and answers, 133–147 stored procedures, 378–395 summary, 132 creating and altering, 380–384 databases definition and advantages, 379–380, 380 access, 659–664 error handling, 392–396 adding new users, 660–662, 660, 661, 662 executing, 386–392 adding roles and users, 663–664 using parameters, 384–385 basics, 659–660 summary, 420 user-defined roles, 662–663 transactions, 396–398 adding files and filegroups, 124 triggers, 406–420 altering, 90–94 AFTER, 407–408, 419 monitoring activity with Profiler, 733–734 AFTER INSERT, 419 names, changing, 93–94 changing firing order, 419 options values, 114–115 definition and advantages, 406–407 shrinking, 80–90 DELETE, 410–412, 411 automatically, 80–81 disabling, 419–420 manually, 82–85, 82 INSERT, 409–410 using Enterprise Manager, 85–87, 85 INSTEAD OF, 413–418 size, increasing, 86–87 multiple, 419 databases, creating and managing, 64–120 performance considerations, 418 creating simple databases, 65–72, 65 UPDATE, 412–413, 412 Create Database statement, 68 Web, 419 data and log files, 66–72, 66, 70 user-defined functions (UDFs), 399–405 with Enterprise Manager, 65–66, 65, 76–79, creating and altering, 400–404 77, 78 definition and advantages, 399–400 data files and, 66–72, 66 using, 404–405 database options, 113–120, 113 views, 359–378 compatibility levels, 118–120 creating and altering, 362–367 database options value, 114–115 defined, 360, 360 recovery Model, 116 indexed views, 369–372 managing, 73–94 partitioned views, 372–378, 372 altering databases, 90–94 uses, 361 collation, 79–80 using, 367–369 filenames, 73–74, 73 database options, 113–120, 113 shrinking databases and files, 80–90, 82, compatibility levels, 118–120 85, 89 default ANSI NULL, 155 size and growth options, 74–75 2942Ind.qxd 7/27/01 10:07 AM Page 775 datatypes – Enterprise Manager 775 space management, 94–112 logical design, See also relational models data files, 94–107, 95, 96, 104 and normalization log files, 107–113, 109, 110, 111 designing database systems, 2–5 datatypes dirty pages defined, 72 attributes of, 23–24 disks, and log files, 131 changing in columns, 172 DISTINCT command, 470–471 creating tables and, 154 distributed partitioned views, 496–500, 497 system datatypes in columns, 176–184 distributed queries, 489–500 date and time, 181 about, 489–490 listed, 177–180 distributed partitioned views, 496–500, 497 numeric, 180–181 linked servers, 490–494 special, 182–183 OPENROWSET function, 494–496 strings, 181–182 using to modify data, 568–572 synonyms, 183–184 linked servers, 569 user-defined datatypes in columns, 184–187 OPENROWSET, 570–572, 571, 572 date and time, values in columns, 181 distribution statistics, and indexes, 306–311 DBCC LOG statement, 108–109 domains DBCC SHOW CONTIG, 335–336 defined, 22 DBCC SHRINKDATABASE, 88–89 domain integrity, 22–24, 222 DBCC SHRINKFILE, options, 87–90 DSS (Decision Support System) application, 129 deadlocks, 635–638 DYNAMIC cursors, 486 Decision Support System (DSS) application, 129 declarative integrity, 223 default constraints, 228–231, 230 default objects, 232–234 E default values, 227–234 edge tables, 563 basics, 227–228 ELEMENTS option, 503 default constraints, 228–231, 230 ENCRYPTION option, 364 default objects, 232–234 enterprise integrity, 28, 223 deferred name resolution defined, 383 Enterprise Manager defragmenting data files, 339–341 altering files, 91–93 delete rules, 25 adding, 92 DELETE statement, 551–554 modifying, 91–92 DELETE triggers, 410–412, 411 removing, 93 deletes, and fragmentation, 338–339 cascading foreign key constraints, creating, denormalization process, 41–47, 41, 42 262–265, 263, 264, 265 derived columns, adding, 45–46, 45 collation and, 164, 165 redundant columns, adding, 43–45, 44 columns tables, partitioning, 46–47, 46 defining default values, 229–230, 230 derived columns, adding, 45–46, 45 warning on altering, 171 derived tables, 473 data and log files, defining, 67 design databases conceptual design, See also Entity/Relationship adding new users, 660–662, 660, 661, 662 (ER) database model creating, 65–66, 65, 76–79, 77, 78 aspects of, 28 options, 113–115, 113 importance of, 3 shrinking, 85–87, 85 vs. logical, 5 datatypes (user-defined), creating and vs. relational model, 5 managing, 186–187, 187 2942Ind.qxd 7/27/01 10:07 AM Page 776 776 entities – external fragmentation filegroups databases adding, 124 logical modeling, 48–61 changing default, 123, 123 objects, creating and maintaining, 421–440 creating, 121, 123, 123, 168–169, 168 physical modeling, 133–147 files indexes, 342–355 adding, modifying and removing, 91–92, 93 locking, 646–652 shrinking, 88–90, 89 security plan, developing, 686–695 locks, viewing, 639–641, 639, 640 tables, creating and maintaining, 204–219 logins, adding, 657–659, 657, 658, 659 tips for taking the exam, xxxi–xxxii rights, viewing, 670–671, 671 types of questions, xxvi–xxxi, xxviii tables Web sites for, xxxvii–xxxix creating simple, 152, 152, 155–156, 155 exclusive locks, 632 defining placement, 169–170, 170 execution plans modifying, 171 analyzing query execution, 709–714 unique constraints, defining, 248–249, 249 basics, 709–710 entities query operations, 710–714 attributes and, 7–10 query optimizer and, 701–702 defined, 6 stored procedures and, 386 entity integrity, 24, 223 exercises, listed, xix–xx entity integrity, 24, 223 EXPLICIT mode, and XML, 505–506 Entity/Relationship (ER) database model, 5–28, 21 exporting. See data, importing and exporting basics, 5–7 extended properties defining entities and attributes, 7–10, 9 metadata and, 195 domain integrity, 22–24 tables and, 190–195, 191, 194 elements of, 6 Extensible Markup Language(XML), See also enterprise integrity, 28 XML Path Language entity integrity, 24 modifying data using OPENXML, 559–568 keys, 16–21 deleting rows, 566–567 foreign keys, 19–21, 19 inserting rows, 564–565 primary keys, 16–19, 18, 19 new rowset views, 561–564 referential integrity, 24–28 SP_XML_PREPAREDOCUMENT, 559–561 relationships, 10–16 updating rows, 565–566 many-to-many relationships, 14–15, 15 options, 503 one-to-many relationships, 13, 14, 14 SQL Server 2000 and, 500–514 one-to-one relationships, 13–14, 13 description, 500–501 recursive relationships, 16, 16 extracting data in XML format, 507 ER. See Entity/Relationship (ER) database model integrating using XPATH, 514 errors, handling with stored procedures, 392–396 integrating using XSL, 511–513, 513 exam preparation using SELECT with, 502–506 adaptive exam format, xxix using template files to access SQL Server data through a URL, 508–511 accessing, 516–532 Extensible Stylesheet Language (XSL) accessing, analyzing and optimizing, defined, 501 757–767 extracting data in XML format and, importing and exporting, 618–624 511–513, 513 integrity, 268–286 extents, 95–98, 95, 96 modifying, 584–586 external fragmentation, 333–336, 334 2942Ind.qxd 7/27/01 10:07 AM Page 777 FAST_FORWARD cursors – HAVING clause 777 creating tables and, 257–258 F table-level constraints, 254–256 defining at table modification, 266–267 FAST_FORWARD cursors, 487 Entity/Relationship model and, 19–21, 19, 21 FETCH statements exercise to create and use, 257–258 retrieving data from cursors and, 488–489 nonclustered indexes and, 720 using cursors and, 484 real world scenario, 259 fields format files, 594–600 field lengths, 598–600 field lengths and field terminators, 598–600 field terminators, 598–600 prefix lengths, 598 filegroups using, 595–596 BLOBs and, 203 forms. See normal forms creating databases and, 120–127, 121 FORWARD_ONLY, 486–487 adding filegroups, 124 fragmentation and index maintenance, 332–341 automatically creating filegroups, 121–124, defragmenting data files, 339–341 122, 123 deletes and, 338–339 backing up and, 127 fragmentation types, 333–336, 333, 334 creating tables and, 165–169 inserts and, 336–337, 337 basics, 165–167, 166, 167 updates and, 337–338, 338 creating databases using new filegroup, FROM clause, subqueries in, 473 168–169, 168 full functional dependency defined, 31 defined, 120 full outer joins, 460–461 maintenance and performance, 125–127 functions. See user-defined functions (UDFs) RAID and, 169 filenames, database management and, 73–74, 73 files, See also data files; filegroups; log files database files G modifying, 91–92 GAMs. See Global Allocation Maps placement and performance, 127–131 Generate SQL Scripts utility, 69 format files, 594–600 Global Allocation Maps (GAMs) field lengths and field terminators, 598–600 defined, 98 prefix lengths, 598 table of usage, 105 using, 595–596 global cursors, 485 removing from databases, 93 globally unique identifiers. See GUIDs (globally fill factor, and indexes, 328–331, 329, 330 unique identifiers) First Normal Form (1NF), 32–35, 32, 33, 34 GROUP BY clause, 465 fixed point numeric attributes, 23 GUIDs (globally unique identifiers), See also floating point numeric attributes, 23 ROWGUIDCOL FOR REPLICATION option, 382 creating and managing GUID columns, foreign keys, 252–267, 252 162–163 basics, 19–21, 19 defined, 161 cascading foreign key constraints, 260–265, 263, 264, 265 defined, 19, 19 defining at table creation, 253–260 H cascading deletes and updates and, 258–260 column level constraints, 253–254 hardware, importance of powerful, 128 HAVING clause, 466 2942Ind.qxd 7/27/01 10:07 AM Page 778 778 heaps – intent locks heaps definition and advantages, 288–296 accessing data and, 297–299, 298, 299 clustered, 291–294, 292, 293 and indexes, 289–291, 289, 290 composite, 296 defined, 289 heaps, 289–291, 289, 290 hints nonclustered, 294–296, 294, 295 index hints, 727 unique, 296 join hints, 726–727 exam essentials, 342–343 lock hints, 728–731 fragmentation and maintenance, 332–341 query hints, 726–731 defragmenting data files, 339–341 query processing, 727–728 deletes, 338–339 horizontal partitions defined, 675 fragmentation types, 333–336, 333, 334 inserts, 336–337, 337 updates, 337–338, 338 Index Tuning Wizard, 741–744 I key terms, 342 IAMs (Index Allocation Maps) defined, 98 optimizing queries and, 717–721 IDENT_CURRENT, 158–160 basics, 717–718 identifiers, See also unique identifiers by changing, 723–729, 724, 726 conforming column names to, 153 clustered indexes, 719 identifier rules, 153 nonclustered indexes, 719–721 identifying relationship, 20–21 physically separating from tables, 126 identity, See also unique identifiers reindexing tables, 341 defined, 156 review questions and answers, 343–355, 343, Identity, 163 344, 349, 350 identity columns, 161, 540 statistics and, 306–320 identity values, 158–160, 541 creating statistics, 331–332 identity columns, 161, 540 distribution statistics, 307–311 identity fields, 540–541 index choice, 312–315, 312, 315 identity values, 158–160, 541 statistics maintenance, 315–320 IIS (Internet Information Server) defined, 501 summary, 342 image columns, storage, 198–203, 202 inline table-valued UDFs, 402–403 importing. See data, importing and exporting inner joins, 456–458 Index Allocation Maps (IAMs) defined, 98 insert rules, 24–25 index hints, 727 INSERT statement, 535–538 Index Tuning Wizard, 741–744 multiple rows, 542–544 indexed views, 369–372 single rows, 535–538 indexes, 288–355 single rows with identity fields, 540–541 accessing data and, 297–306 single rows with selected columns, 538–539 clustered indexes, 299–302, 300, 301 INSERT triggers, 409–410, 409 heaps, 297–299, 298, 299 inserts, and leaf level fragmentation, nonclustered indexes, 302–306, 304, 305 336–337, 337 creating, 320–331 INSTEAD OF triggers clustered and nonclustered, 321–324, 322 INSTEAD OF DELETE, 415 composite, 325–326 INSTEAD OF INSERT, 414–415 fill factor, 328–331, 329, 330 INSTEAD OF UPDATE, 416 on computed columns, 326–328 integrity. See data integrity unique, 324–325 intent locks, 631–632 2942Ind.qxd 7/27/01 10:07 AM Page 779 intermediate levels defined – login 779 intermediate levels defined, 291 linked servers internal fragmentation accessing data and, 490–494 defined, 333, 333 modifying data with, 569 determining, 334–336 literals. See scalars negative effect of deletes on, 333, 338–339 local cursors, 485 Internet Explorer 5, and viewing XML lock hints documents, 506 queries and, 728–731 Internet Information Server (IIS) defined, 501 servers and, 643–644 locking, 626–652 described, 626 exam essentials, 647 key terms, 646 J lock manager, 627–634 join hints, 726–727 basics, 627–629 joins levels of locking, 633–634 cross joins, 462 lock modes, 629–633 exercise in joining tables, 463–464 options, 638–644 inner joins, 456–458 lock hints and servers, 643–644 outer joins, 458–461 lock isolation levels, 642 full outer joins, 460–461 viewing locks, 638–642, 639, 640, 641 left and right outer joins, 459–460 review questions and answers, 647–652 summary, 645–646 transactions and locking, 634–638 locks exclusive locks, 632 K lock detection, 740–741 key range locks, 634 lock hints and queries, 728–731 keys, See also foreign keys; primary keys SP_LOCK result set, 641–642 alternate keys, 16 types of, 631–633 artificial keys, 17 log files, See also transaction logs candidate keys, 16 creating databases and, 66–72, 66 KEYSET cursors, 486 default placement, 67 functioning of, 69–70, 70 placement and performance, 130–131 placement, 130–131 shrinking, 87–90, 89 L size and growth options, 74–75 latches, 633 space management and, 107–113, 109, 110, lazy writer process, 72 111 leaf levels log records and, 110–111 defined, 291 size of, 109–110 fragmentation and inserts, 336–337, 337 transaction logs, 107–108 inserts as cause of fragmentation, 336 vs. data files, 107 storage of computed columns and, 327 logical design. See database logical modeling left and right outer joins, 459–460 login legacy syntax, 460 databases and, 660 SQL Sever 2000 and, 655–659, 657, 658, 659 2942Ind.qxd 7/27/01 10:07 AM Page 780 780 mainframes, real world scenario – OPENXML for modifying data First Normal Form (1NF), 32–35, 32, M 33, 34 Second Normal Form (2NF), 35–36, 36 mainframes, real world scenario, 601–602 Third Normal Form (3NF), 36–39, 38 managing databases. See databases, creating normalized logical models, 38, 38 and managing NOT FOR REPLICATION manual shrinking, 82–86, 82, 85 constraints and, 265 many-to-many relationships, 14–15, 14, 15 when defining identities, 161 materialized views In Oracle, 370 ntext columns, and table storage, 198–203, 202 MCSE (Microsoft Certified System Engineer) NULL values described, xxi changing in columns, 173 exam requirements, xxiv–xxvi defining columns and, 154–155 types of questions, xxvi–xxxi nullability, changing columns,’ 173–174 metadata numeric datatypes defined, 365 attributes datatypes, 23 extended properties and, 195 in columns, 180–181 Microsoft Certified System Engineer. See MCSE (Microsoft Certified System Engineer) min LSN, 110–111, 110, 111 mixed extents, 95, 96 O multistatement table-valued UDFs, 403–404 Object Browser, 747 object rights, 667–685 basics, 667–671, 671 N ownership chains, 684–685 stored procedures, 677–680 natural order defined, 444 tables, 671–675 non-key attributes defined, 7, 9 triggers, 680–681 non-leaf levels defined, 291 user-defined functions (UDFs), 681–684 nonclustered indexes views, 675–677 accessing data and, 302–306, 304, 305 Object Search, 747 creating, 321–324, 322 objects, See also database objects definition and advantages, 294–296, 294, 295 default objects, 232–234 optimizing queries and, 719–721 OLTP (OnLine Transaction Processing) normal forms, 31–39 application, 129 Boyce/Codd Normal Form (BCNF), 39 one-to-many relationships, 13, 14, 14 First Normal Form (1NF), 32–35, 32, 33, 34 one-to-one relationships, 13–14, 13 Second Normal Form (2NF), 35–36, 36 OnLine Transaction Processing (OLTP) Third Normal Form (3NF), 36–39, 38 application, 129 Fourth Normal Form (4NF), 39–40 OPENROWSET function Fifth Normal Form (5NF), 40 data accessing and, 494–496 normalization process modifying data using, 570–572, 571, 572 advanced normalization, 39–40 OPENXML for modifying data, 559–568 Boyce/Codd Normal Form (BCNF), 39 basics, 561–564 Fourth Normal Form (4NF), 39–40 deleting rows, 566–567 Fifth Normal Form (5NF), 40 inserting rows, 564–565 defined, 30 new rowset views, 561–564 normal forms, 31–39 2942Ind.qxd 7/27/01 10:07 AM Page 781 OPTIMISTIC option, and cursors – queries 781 SP_XML_PREPAREDOCUMENT, 559–561 placement updating rows, 565–566 data files OPTIMISTIC option, and cursors, 487 default placement, 67 options. See database options; various options performance and, 127–131 ORDER BY clause, 446–447 log file placement, 130–131 OSQL, 79 tables, defining placement, 169–170, 170 outer joins, 458–461 positional updates defined, 556 ownership chains, 684–685 primary data files (extension .MDF), 67 primary filegroups defined, 120 primary keys clustered indexes and, 321, 719 P defined, 16, 18, 19, 240 packages in DTS, 608, 612–617 defining, 241–246 creating, 613–617, 614, 615, 616 at table creation, 241–245, 245 defined, 612 at table modification, 245–246 pages relational model and, 16–19 dirty pages defined, 72 procedural integrity, 224 types of, 98–99, 99 procedure caches defined, 386–387 parameters, and stored procedures, 384–385 Profiler, 731–744 partitioned views, 372–378, 372 basics, 731–732, 732 advantages and disadvantages, 376–377 creating traces using, 738–739, 739 conditions for, 375–376 functions and use, 733–738, 735, 736, 737 defined, 372 Index Tuning Wizard, 741–744 distributed partitioned views, 496–500, 497 lock detection, 740–741 horizontal partitions defined, 675 replaying saved trace files, 739–740 partitioning columns, 373–375 properties partitioning columns, 373–375 ACID, 396 partitioning tables, 46–47 extended horizontal, 47 metadata and, 195 vertical, 46–47, 46 tables and, 190–195, 191, 194 partitions, vertical partitions defined, 445, 445 performance data placement and, 129–130 disk performance, 112 Q files data files placement and, 127–131 queries filegroups maintenance and, 125–127 analyzing, 703–716 log file placement and, 130–131 execution plan analysis, 709–714 performance enhancers exercise in, 714–716 SELECT command and, 449 SET FORCEPLAN, 704 stored procedures as, 380, 380 SET NOEXEC, 704–705 views as, 361 SET SHOWPLAN_ALL, 705–707 triggers and performance loss, 418 SET statements basics, 703–704 Performance Monitor, 112 SET STATISTICS IO, 708 permissions. See object rights SET STATISTICS PROFILE, 709 physical design. See database physical modeling SET STATISTICS TIME, 709 physical model defined, 28 SETSHOWPLAN_TEXT, 707–708 2942Ind.qxd 7/27/01 10:07 AM Page 782 782 Query Analyzer procedures – recursive relationships distributed queries, 489–500 rows about, 489–490 deleting sets of, 553–554 distributed partitioned views, 496–500, 497 inserting single, 537–538 linked servers, 490–494 removing from tables, 552 OPENROWSET function, 494–496 updating, 545–548 using to modify data, 568–572, 571, 572 updating with OPENXML, 565–566 exercise using, 453–455 scripts and queries, opening, 79 importance of understanding processing, 71 single table queries, creating, 453–455 inserting multiple rows with, 542–543 stored procedures, granting rights to, 678–680 optimizing, 717–732 tables changing an index, 723–726, 724, 726 granting rights to, 672–673 indexes, 717–721 granting rights to columns in, 674–675 limiting the result set, 721–723, 722, 723 joining, 463–464 query hints, 726–731 UDFs, granting rights to, 682–684 Query Analyzer procedures, See also Transact- views SQL procedures creating distributed partitioned views, aggregates, using, 468–469 497–499 application roles granting rights to, 676–677 creating and assigning rights, 665–666 query hints, 726–731 using, 665–666 query optimizer, 699–703 columns basics, 699–700 creating computed columns, 189–190 batch optimization, 702–703 granting rights to columns in tables, single statement optimization, 700–702 674–675 query plans, 386–387 GUID columns, creating and managing, query processing hints, 727–728 162–163 query trees, 386–387 inserting selected, 539 updating multiple, 549–550 constraints cascading foreign key constraints, creating R and using, 260–262 RAID check constraints, creating and using, data placement and, 129–130 238–239 log file placement and, 131 foreign key constraints, creating and using, real world scenarios 257–258 delete dilemma, 259 primary key constraints, creating and using, new database analysis, 4–5 244–245 operation order issue, 26–27 unique constraints, creating and using, using Index Tuning Wizard, 744 250–251 using stored procedures, 391–392 data using triggers to capture an audit trail, 681 deleting with OPENXML, 566–567 working with mainframes, 601–602 inserting with OPENXML, 564–565 write ahead paradigm, 112 modification with cursors, 557–558 records storage and, 100–102 inserting, and GUIDS, 162 deadlock conditions, creating, 636–637 storage, 196–198, 196 default values, defining and testing, 233–234 recovery model, and database options, 116 indexes, creating, 322, 322 recursive relationships, 16, 16 2942Ind.qxd 7/27/01 10:07 AM Page 783 redundant columns, adding – security 783 redundant columns, adding, 43–45, 44 updating, 545–548 referential integrity, 24–28, 223 all, 545–546 relational models and normalization, 28–40 sets of rows, 547–548 advanced normalization, 39–40 rowset views, 561–564 Boyce/Codd Normal form (BCNF), 39 rules Fourth Normal Form (4NF), 39 data integrity and, 239–240 Fifth Normal Form (5NF), 40 referential integrity and, 24–26 basics, 28–29, 41 normal forms, 31–39 First Normal Form, 32–35, 32, 33, 34 Second Normal Form, 35–36, 36 Third Normal Form, 36–39, 38 S normalization defined, 30 scalar subqueries, 472 relational tables, 29–31 scalars relational tables, 29–31 scalar UDFs, 399, 400–401 relationships SELECT statement and, 471–472 characteristics of, 10–13, 11, 12 schema locks, 632–633 defined, 6 SCHEMABINDING option, 364–365 identifying, 20–21 SCOPE_IDENTITY, 158–160 many-to-many, 14–15, 15 scopes defined, 158 one-to-many, 13, 14, 14 scripts one-to-one, 13–14, 13 Create Database statement, 68 recursive, 16, 16 Generate SQL Scripts utility, 69 result sets, limiting, 721–723, 722, 723 Query Analyzer and opening, 79 right and left outer joins, 459–460 SCROLL cursors, 486 rights, viewing, 670–671, 671 SCROLL_LOCKS cursors, 487 roles Second Normal Form (2NF), 35–36, 36 application, 664–666 secondary data files (.NDF), 67 user-defined, 662–664 security, 654–695 roots of indexes defined, 291 assigning object rights, 667–685 ROWGUIDCOL object rights, 667–671, 671 dropping from columns, 174 ownership chains, 684–685 GUIDs and, 161–162 stored procedures, 677–680 rows, See also unique constraints; unique identi- tables, 671–675 fiers triggers, 680–681 deleting, 551–554 user-defined functions (UDFs), 681–684 inserting, 535–544 views, 675–677 multiple, 542–544 exam essentials, 686–687 single rows, 535–538 key terms, 686 single rows with identity fields, 540–541 linked servers and, 493 single rows with selected columns, 538–539 overview, 654–666 OPENXML, 564–567 adding new database users, 660–662, 660, deleting with, 566–567 661, 662 inserting with, 564–565 adding roles and users, 663–664 updating with, 565–566 application roles, 664–666 relational tables and, 30 basics on database access, 659–660 2942Ind.qxd 7/27/01 10:07 AM Page 784 784 SELECT list, subqueries in – statistics server access, 655–659, 657, 658, 659 manual shrinking, 82–85, 82 user defined roles, 662–663 shrinking in Enterprise Manager, 85–87, 85 review questions and answers, 687–695 shrinking files, 87–90, 89, See also shrinking data- stored procedures and, 379 bases summary, 685 single statement optimization, 700–702 views and, 361 single table SELECT, 444–445, 445 SELECT list, subqueries in, 472–473 size SELECT statement, 443–475 of databases aggregate operators, 464–468 Create Database statement options and, aliases, specifying, 450–451 74–79, 77, 78 basics, 443–444 increasing, 86–87 cross joins and, 462 managing size and growth options, 74–75 DISTINCT command, 470–471 of log files, and space management, 109–110 formal syntax for, 474–475 space management, 94–112 in views, 362–363, 367–368 data files, 94–107, 95, 96, 104 inner joins and, 456–458 allocation tracking, 103–107, 104 joining tables exercise, 463–464 data allocation, 99–103 ORDER BY clause, 446–447 extents, 95–98, 95, 96 outer joins and, 458–461 pages, 98–99, 99 returned results, limiting, 451–453 log files, 107–113, 109, 110, 111 scalars and, 471–472 log records, 110–111 single table SELECT, 444–445, 445 size of, 109–110 subqueries and, 472–475 sp_createstats system stored procedure, 317 UNION command, 470 special attributes datatypes, 23 using aggregates exercise, 468–469 SP_LOCK result set, 641–642 using queries exercise, 453–455 SP_XML_PREPAREDOCUMENT, 559–561 using with XML, 502–506 SQL Enterprise Manager. See Enterprise Manager WHERE clause, 447–449 SQL Profiler. See Profiler servers, See also SQL Server 2000 SQL Query Analyzer. See Query Analyzer proce- accessing, and security, 655–659, 657, 658, dures 659 SQL Server 2000 extracting template files from, 508–511 accessing, 655–659 linked servers authentication, 655 accessing data and, 490–494 login, 655–659, 657, 658, 659 modifying data with, 569 accessing data and, 442–443 lock hints and, 643–644 importance of powerful hardware, 128 SET FORCEPLAN, 704 integrating with XML, 507–514 SET NOEXEC, 704–705 uses of, 514 SET ROWCOUNT, 451–453 using template files, 508–511 SET SHOWPLAN_ALL, 705–707 XPATH (XML Path Language), 514 SET statements, 703–709 XSL, 511–513, 513 SET STATISTICS IO, 708 OSQL, 79 SET STATISTICS TIME, 709 STATIC cursors, 486 SETSHOWPLAN_TEXT, 707–708 statistics shared locks, 631 creating on multiple columns, 331–332 shrinking databases, 80–90 index choice and, 306–320 automatic shrinking, 80–81 distribution statistics, 307–311 2942Ind.qxd 7/27/01 10:07 AM Page 785 storage – tables, creating and maintaining 785 index choice, 312–314, 312, 315 security, 685 statistics maintenance, 315–320 tables, creating and maintaining, 204 options defined, 318–319 synonyms, system datatypes, 183–184 storage system datatypes in columns, 176–184 computed columns storage, 327 date and time, 181 data storage, 100–102 listed, 177–180 structured storage files, 613 numeric, 180–181 table storage, 195–203 special, 182–183 BLOBs and, 182 strings, 181–182 record storage, 196–198, 196 synonyms, 183–184 text columns, 198–203, 202 text, ntext and image storage, 198–203, 202 stored procedures advantages, 379–380, 380, 699 T creating and altering, 380–384 T-SQL. See Transact-SQL procedures defined, 379, 380, 380, 678 table-valued UDFs defined, 400 error handling, 392–396 tables, See also columns; data integrity; rows executing, 386–392 defined, 151 compiling, 387–389 edge tables, 563 query tree and query plans, 386–387 exercise in joining, 463–464 running procedures, 389–392 granting rights to, 672–673 object rights and, 677–680 granting rights to columns in, 674–675 optimizing, 744–746 isolating, 26 stored procedure debugger, 746–755 object rights and, 671–675 exercise in debugging, 749–755, 752, partitioning 753, 754 horizontal, 47 using, 746–749, 748 vertical, 46–47, 46 using parameters, 384–385 physically separating from indexes, 126 using to insert multiple rows, 543–544 reindexing, 341 striped disks, and data placement, 129–130 relational, 29–31 structured storage files, 613 tables, creating and maintaining, 150–219 subqueries, 472–475 altering, 170–176 correlated subqueries, 473 adding or dropping columns, 174–176 SELECT statement and, 472–475 altering columns, 171–174 summaries computed columns, 188–189 data constraints, defining data access, analyzing and optimizing, check constraints, 235–239 755–756 default constraints, 228–231, 230 accessing, 515–516 creating, 150–170 importing and exporting, 617–618 collation and, 163–165, 165 data integrity, 267 filegroups and, 165–170, 166, 167, 168, modifying, 573 170 databases GUID columns and, 162–163 database logical modeling, 48 identity, 156–158 database objects, 420 identity comparisons, 158–161 database physical modeling, 132 simple, 151–155, 151, 152 indexes, 342 simple with Enterprise Manager, 155–156 locking, 645–646 UniqueIdentifier, 161–162 2942Ind.qxd 7/27/01 10:07 AM Page 786 786 tasks, and DTS – uniform resource locators (URLs) exam essentials, 205 databases extended properties, 190–195, 191, 194 adding new users, 660–662, 660, 661, 662 foreign key constraints, defining at creation, adding roles and users, 663–664 253–257 altering, 91–93 key terms, 204 creating, 68 placement, defining with Enterprise Manager, options, 113–114 169–170, 170 files, defining data and log files, 68 primary keys, 241–246 inexperienced programmers and, 449 defining at creation, 241–245, 244 tables, creating simple, 151–152 defining at table modification, 245–246 transaction isolation levels, 627–628 review questions and answers, 205–219, 207, transaction logs 211, 214 importance of, 71 storage, 195–203 space management and, 107–108 record storage, 196–198, 196 storing, 131 text, ntext and image storage, transactions 198–203, 202 defining, 69 summary, 204 locking and, 634–638 system datatypes, 176–184 triggers, 406–420 date and time, 181 advantages of, 406–407 listed, 177–180 AFTER, 407–408, 419 numeric, 180–181 AFTER INSERT, 419 special, 182–183 cascading updates and deletes and, 260 strings, 181–182 defined, 406–407, 680 synonyms, 183–184 DELETE, 410–412, 411 unique constraints, 247–252 disabling, 419–420 defining at table creation, 247–251, 249 firing order, changing, 419 defining at table modification, 251–252 INSERT, 409–410, 409 user-defined datatypes and, 184–187 INSTEAD of, 413–418 tasks, and DTS, 610–612 INSTEAD OF DELETE, 415 template files, 508–511 INSTEAD OF INSERT, 414–415 templates defined, 501 INSTEAD OF UPDATE, 416 text columns, and storage, 198–203, 202 multiple, 419 Third Normal Form (3NF), 36–39, 38 object rights and, 680–681 time and date values, 181 optimizing, 744–746 TOP, 451–452 performance considerations, 418 traces, 734–741 UPDATE, 412–413, 412 basics, 734–738, 735, 736, 737 vs. constraints, 225 creating using Profiler, 738–739, 739 Web, 419 replaying with Profiler, 739–740 trace flags with lock detection, 741 Transact-SQL procedures, See also Query Ana- lyzer procedures; various statements U data manipulation, 534 UDFs. See user-defined functions (UDFs) DELETE statement, 551–554 Unicode vs. collation, 164 INSERT statement, 535–544 uniform extents, 95, 96 UPDATE statement, 544–551 uniform resource locators (URLs) 2942Ind.qxd 7/27/01 10:07 AM Page 787 UNION command – WHERE clause 787 accessing SQL Server through, 507–514 defined, 501 V UNION command, 470 values unique constraints, 246–252 database options values, 114–115 defining at table creation, 247–251, 249 default values, 227–234 defining at table modification, 251–252 basics, 227–228 unique identifiers, 156–163, 337 default constraints, 228–231, 230 basics, 156–158 default objects, 232–234 collation, 163–165, 165 identity values, 158–160, 541 comparisons of identity, 158–161 NULL values globally unique identifiers (GUIDs) changing in columns, 173 creating tables with GUID columns, using defining columns and, 154–155 Query Analyzer, 162–163 time and date values, 181 statement creating tables with GUID vertical partitions defined, 445, 445 columns, 161–162 VIEW_METADATA option, 365 identity, defined, 156 views, 359–378 UniqueIdentifier, 161–162 creating and altering, 362–367 unique indexes ENCRYPTION option, 364 creating, 324–325 METADATA option, 365 described, 296 SCHEMABINDING option, 364–365 update locks, 632 SELECT statement, 362–363 update rules, 25–26 VIEW_METADATA option, 365 UPDATE statement, 544–551 WITH CHECK OPTION option, 366–367 all rows, 545–546 defined, 360, 360, 675 multiple columns, 548–550 distributed partitioned views, 496–500, 497 sets of rows, 547–548 granting rights to, 676–677 views, 550–551 indexed views, 369–372 UPDATE triggers, 412–413, 412 modifying new rowset views with OPENXML, updateable cursors, 555–556 561–564 updates object rights and, 675–677 fragmentation and, 337–338, 338 partitioned views, 372–378, 372 statistics examples, 310–320 SELECT statement and, 362–363, 367–368 URLs (uniform resource locators) triggers and, 416–418 accessing SQL Server through, 507–514 updating, 550–551 defined, 501 uses, 361 user access to databases, 663–664 using, 367–369 user-defined datatypes in columns, 184–187 retrieving data, 367–368 user-defined filegroups defined, 120 updating data, 368–369 user-defined functions (UDFs), 399–405 virtual log files, 109, 109 advantages of, 400 creating and altering UDFs, 400–404 defined, 399–400, 682 granting rights to, 682–684 W object rights and, 681–684 types, 399–400 Web triggers, 419 using UDFs, 404–405 WHERE clause user-defined roles, 662–664 basics, 447–449 subqueries in, 473–474

Tải về miễn phí