Table Of Content

INSTRUCTOR GUIDE n o i t i d E E r T o A C t I L c P U u D T r O t N s O Microsoft SQL Server 2000 - Database D n Design I Microsoft SQL Server 2000 n Database Design o i t i d E E r T o A C t I L c P U u D T r O t N s O D n I M SQL S 2000 - D D ICROSOFT ERVER ATABASE ESIGN n CourseNumber:NH77462 (IGEE) CourseEdition:1 Forsoftwareversion:2000 ACKNOWLEDGEMENTS o ProjectTeam i CurriculumDeveloperandTechnicalWriter:RozanneM.Whalen•CopyEditor:ChristyD.Flanders• ReviewingEditors:ElizabethM.Swank,TarynChaseandAngiteJ.French•QualityAssuranceAnalyst:Frank Wosnick•PrintDesigner:DanielP.Smith i ProjectSupport d ManagingEditor:ClareS.Dygert•AcquisitionsEditor:TinaMariaNelson Administration SeniorDirectorofContentandContentDevEelopment:WilliamO.Ingle•DirectorofCertification:Mike Grakowsky•DirectorofDesignandWebDevelopment:JoyInsinna•ManagerofOfficeProductivityand AppliedLearning:CherylRusso•ManagerofDatabases,ERP,andBusinessSkills:MarkOnisk•Director ofBusinessDevelopment:KentMichels•InstructionalDesignManager:SusanL.Reber•Managerof PublishingServices:MichaelHoyt E r NOTICES T o A DISCLAIMER:WhileElementKPressLLCtakescaretoensuretheaccuracyandqualityofthesematerials,wecannotguaranteetheiraccuracy,andallmaterialsare C providedwithoutanywarrantywhatsoever,including,butnotlimitedto,theimpliedwarrantiesofmerchantabilityorfitnessforaparticularpurpose.Thenameusedinthedata filesforthiscourseisthatofafictitiouscompany.Anyresemblancetocurrentorfuturecompaniesispurelycoincidental.Wedonotbelievewehaveusedanyone’snamein creatingthiscourse,butiftwehave,pleasenotifyusandwewillchangethenameinthenextrevisionIofthecourse.ElementKisanindependentproviderofintegrated trainingsolutionsforindividuals,businesses,educationalinstitutions,andgovernmentagencies.UsLeofscreenshotsoranotherentity’sproductnameorserviceinthisbook isforeditorialpurposesonly.Nosuchuseshouldbeconstruedtoimplysponsorshiporendorsementofthebookby,noranyaffiliationofsuchentitywithElementK. c P TRADEMARKNOTICES:ElementKandtheElementKlogoaretrademarksofElementKLLC.SQLServerisaregisteredtrademarkofMicrosoftCorporationintheU.S.and othercountries.Allotherproductnamesandservicesusedthroughoutthisbookmaybecommonlaworregisteredtrademarksoftheirrespectiveproprietors. U Copyright©2001ElementKContentLLC.Allrightsreserved.Screenshotsusedforillustrativepurposesarethepropertyofthesoftwareproprietor.Thispublication,orany partthuereof,maynotbereproducedortransmittedinanyformorbyanymeans,electronicormechanical,includingphotocopying,recording,storageinaninformation D retrievalsystem,orotherwise,withoutexpresswrittenpermissionofElementK,500CanalViewBoulevard,Rochester,NY14623,(716)240-7500,(800)434-3466.ElementK PressLLC’sWorldWideWebsiteislocatedatwww.elementkpress.com. ThisbookconveysnorightsinthesoftwareorotherproductsaboutwhTichitwaswritten;alluseorlicensingofsuchsoftwareorotherproductsistheresponsibilityofthe useraccordingtotermsandconditionsoftheowner.Donotmakeillegalcopiesofbooksorsoftware.Ifyoubelievethatthisbook,relatedmaterials,oranyotherElementK r materialsarebeingreproducedortransmittedwithoutpermission,pleasecall1-800-478-7788. O t N s O D n I ii M SQL S 2000 - D D CONTENT ICROSOFT ERVER ATABASE ESIGN OVERVIEW n o AboutThis Course . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Lesson 1:An Overview of SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 i t Lesson 2:ExploringTransact-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 i Lesson 3:Designing and Implementing Databases . . . . . . . . . . . . . . . . . .d. . . . . 91 Lesson 4:Creating and ManagingTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 E Lesson 5:Designing and Implementing Data Integrity . . . . . . . . . . . . . . . . . . . . . 143 Lesson 6:Implementing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 E r T Lesson 7:JoiningTables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 o A C Lesson 8:DesigningAdvanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 t I L Lesson 9:DesigningViews . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 c P U Lesson 10:Creating Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247 u D Lesson 11:Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 T r O Lesson 12:CreatingTriggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299 t N Lesson 13:UnderstandsingTransactions and Locks . . . . . . . . . . . . . . . . . . . . . . . 317 O Lesson 14:Implementing Distributed Queries . . . . . . . D. . . . . . . . . . . . . . . . . . . . 337 n Lesson 15:Optimizing Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 I Lesson 16:Analyzing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377 AppendixA:The Movies Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . 397 Contents iii CONTENT Appendix B:Course Script Files. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399 OVERVIEW Additional Instructor Notes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 n Glossary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 405 o Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 409 i t i d E E r T o A C t I L c P U u D T r O t N s O D n I iv M SQL S 2000 - D D CONTENTS ICROSOFT ERVER ATABASE ESIGN n o About This Course About This Course......................................... xvi Course Setup Information................................... xvi t How to Use This Book...................................... xxi i L 1: A O SQL S d ESSON N VERVIEW OF ERVER TOPIC 1A Exploring the Components of SQL Server ......E......... 2 Features of Microsoft SQL Server ............................. 3 Task 1A-1 Exploring the Features of Microsoft SQL Server .......... 6 Components of SQL Server ................... ............... 6 Task 1A-2 Exploring the SQL Server Services...r................. 7 E Administrative Tools ....................................... 7 T Task 1A-3 Identifying Administrative Utilitioes................... 9 A Registering Servers........................................ 9 C Task 1A-4 Using SQL Server Enterprise Manager ................. 10 SQL Query Analyzer...............t......................... 1I2 L Task 1A-5 Using SQL Query Analyzer......................... 13 c P TOPIC 1B SQL Server Database Structure ........................ 16 U Task 1B-1 Discussing the Components of SQL Server .............. 20 u D System Tables ............................................ 22 Task 1B-2 Identifying the Default Databases and System Tabl es ...... 24 T Creating Database Diagrrams ................................. 26 O Task 1B-3 Creating and Working with a Database Diagram .......... 26 Apply Your Knowledtge 1-1 ....................N.............. 31 TOPIC 1C .......s........................... ................ 31 O SQL Server Architecture..................................... 31 D Task 1C-1 Understanding the SQL Server Architecture ............. 34 n Designing a Database Application ............................ 34 Task 1C-2 Exploring the Application Architecture ................ 36 TOPIC 1DI Identifying SQL Server Management Tasks .............. 36 Task 1D-1 Determining SQL Server Management Tasks ............. 37 An Overview of SQL Server Security ........................... 37 Task 1D-2 Configuring Your Server’s Authentication Mode .......... 39 Creating Login Accounts.................................... 40 Contents v CONTENTS Task 1D-3 Creating a SQL Login Account ...................... 40 Apply Your Knowledge 1-2 .................................. 41 Configuring Database Users and Permissions.................... 42 Task 1D-4 Working With Server and Database Roles....n........... 44 Lesson 1 Review .......................................... 46 L 2: E T -SQL o ESSON XPLORING RANSACT TOPIC 2A i Working with Transact-SQL ........................... 50 Task 2A-1 Using SQL Query Analyzer t........................ 51 Osql .................................................... 53 i Task 2A-2 Using Osql ................................... 54 d TOPIC 2B Transact-SQL Statements ............................ 55 Data Definition Language Statements ......................... 56 Task 2B-1 Creating a SimEple Table .......................... 57 Data Manipulation Language Statements ...................... 59 Task 2B-2 Inserting Data ................................ 60 Using SELECT ..... ....................................... 60 Task 2B-3 Selecting Data....................E............. 61 r Using a WHERE Clause With the SELECT Statement ............... 62 T Functions....o............................................ 63 A Apply Your Knowledge 2-1 .................................. 66 C Using UPDATE ............................................ 68 Using DEtLETE ......................I...................... 68 L Apply Your Knowledge 2-2 .................................. 68 c Data Control Language Statements...P......................... 69 Task 2B-4 Assigning PermissionsUto the Public Database Role ....... 71 uUsing DENY .............................................. 72 D Using REVOKE ............................................ 72 Apply Your Knowledge 2-3 .................................. 73 T rWorking with Books Online ................................. 73 O Apply Your Knowledge 2-4 .................................. 74 t N TOPIC 2C Programming in Transact-SQL ......................... 75 s Task 2C-1 UsOing Variables ................................ 77 Executing SQL Statements .................................. 77 D n Apply Your Knowledge 2-5 .................................. 80 Control-of-Flow Statements ................................. 81 Apply Your Knowledge 2-6 .................................. 83 I Creating SQL Scripts ....................................... 84 Task 2C-2 Saving a Script File ............................. 84 Apply Your Knowledge 2-7 .................................. 85 Working with XML ......................................... 86 Task 2C-3 Choosing an XML Format.......................... 89 Lesson 2 Review .......................................... 90 vi L 3: D I D CONTENTS ESSON ESIGNING AND MPLEMENTING ATABASES TOPIC 3A Identifying Database Design Issues ................... 92 n Task 3A-1 Designing Databases ............................ 93 Estimating the Space Requirements for Databases ............... 93 Task 3A-2 Identifying the Space Requirements for Databases ........ 96 o Optimizing Databases...................................... 96 Task 3A-3 Optimizing Database Performance.................... 99 TOPIC 3B i Creating Databases ................................. 99 Creating a Database Using Transact-SQL .......................t100 Apply Your Knowledge 3-1 ..................................102 i Creating Multiple Data Files and Filegroups.....................103 d Apply Your Knowledge 3-2 ..................................105 Configuring Database Options................................106 Task 3B-1 Setting Database Options .........................108 E Displaying Information About Databases and Transaction Logs .....109 Task 3B-2 Using Stored Procedures to View Database Information .....109 TOPIC 3C Managing Databases ................. ...............111 Apply Your Knowledge 3-3 .................r.................112 E Monitoring the Size of a Transaction Log ......................113 T Apply Your Knowledge 3-4 ..............o....................114 A Shrinking Databases or Files ................................114 C Task 3C-1 ConfiguringSQLServertoAutomaticallyShrinkaDatabaseFile .................t.........................11I5 L Deleting a Database .......................................116 c P Task 3C-2 Deleting a Database.............................116 Apply Your Knowledge 3-5 ...............................U...117 Lesson 3 Review ..........u................................118 D L 4: C M T T ESSON REATING AND ANAGING ABLES r O TOPIC 4A t N Design and Create Tables.............................120 Task 4A-1 sNormalizing Table Designs......... ................122 O Defining Columns for a Table ................................123 Apply Your Knowledge 4-1 ...............D...................130 n Implementing User-defined Data Types ........................131 Apply Your Knowledge 4-2 ..................................132 Creating a Table ..........................................133 I Apply Your Knowledge 4-3 ..................................135 TOPIC 4B Maintaining Tables ..................................137 Apply Your Knowledge 4-4 ..................................138 Dropping a Table .........................................139 Apply Your Knowledge 4-5 ..................................139 Contents vii CONTENTS Scripting Tables ..........................................139 Task 4B-1 Using SQL Server Enterprise Manager to Generate a Script ...140 Lesson 4 Review ..........................................142 n L 5: D I D I ESSON ESIGNING AND MPLEMENTING ATA NTEGRITY o TOPIC 5A Understanding Data Integrity ........................144 Apply Your Knowledge 5-1 ..................................145 i TOPIC 5B Implementing Constraints ...........................147 t Task 5B-1 Adding a Primary Key Constraint ....................150 Apply Your Knowledge 5-2 ........i..........................151 Defining Foreign Key Constraints .d............................152 Task 5B-2 Adding a Foreign Key Constraint to the Movie Table .......154 Apply Your Knowledge 5-3 ..................................155 Default Constraints ......E.................................156 Task 5B-3 Adding a Default Constraint to the Movie Table ..........157 Apply Your Knowledge 5-4 ..................................157 Check Constraints .. .......................................158 Task 5B-4 Adding a Check Constraint to the Movie TaEble ...........159 r Check Your Skills 5-1.......................................160 T Managing Conostraints ......................................160 A Task 5B-5 Disabling Constraint Checking ......................161 C Using Data Transformation Services ...........................163 Apply Yotur Knowledge 5-5 ............I......................164 L Lesson 5 Review ..........................................165 c P L 6: I I U ESSON MPLEMENTING NDEXES u D TOPIC 6A Designing Indexing T.................................168 r Task 6A-1 UnderstandOing Index Architecture ...................172 Guidelines for Defining Indexes ..............................172 t N Apply Your Knowledge 6-1 ..................................174 s TOPIC 6B ImplementOing Indexes ..............................175 Task 6B-1 Creating a Clustered Index on the Movie Table ..........178 D n Creating Composite Indexes .................................179 Apply Your Knowledge 6-2 ..................................179 Creating a Unique Index ...................................180 I Apply Your Knowledge 6-3 ..................................181 Using the Sysindexes Table..................................181 Task 6B-2 Viewing the Information in Sysindexes ................182 TOPIC 6C Maintaining Indexes ................................183 Task 6C-1 Rebuilding an Index ............................186 viii CONTENTS Using DROP_EXISTING to Change an Index .....................188 Task 6C-2 UsingDROP_EXISTINGtoRe-createanIndexBasedonaPri- mary Key ....................................189 Using SQL Profiler ........................................190 n Task 6C-3 Generating a Workload File in SQL Profiler..............191 Using the Index Tuning Wizard ..............................192 Apply Your Knowledge 6-4 ..................................193 o Managing Index Statistics ..................................194 Task 6C-4 Observing Index Statistics ........................196 Lesson 6 Review ..........................................198i t L 7: J T ESSON OINING ABLES i d TOPIC 7A Querying Multiple Tables ............................200 Apply Your Knowledge 7-1 ..................................202 Defining an Outer Join ...........................E.........203 Apply Your Knowledge 7-2 ..................................204 Designing a Cross Join .....................................205 Task 7A-1 Working with Cross Joins .......... ...............205 E TOPIC 7B r Implementing Advanced Table Joins ...................206 T Task 7B-1 Joining Multiple Tables ..........................206 o A Apply Your Knowledge 7-3 ..................................207 Implementing Self Joins ...................................208C Task 7B-2 Working with Self Joins .t.........................20I8 L Combining the Results of Multiple SELECT Statements ............208 c Task 7B-3 Combining SELECT Statements ....................P.209 Creating a Table Based on a Results Set ....................U...210 Task 7B-4 Creating a NewuTable Based on a SELECT INTO Statement ...211 D Apply Your Knowledge 7-4 ..................................212 Lesson 7 Review .................................. ........213 T r O L 8: D A Q ESSON ESIGNING DVANCED UERIES t N s TOPIC 8A O Designing Subqueries ...............................216 Task 8A-1 Designing Single Value SubqueriesD...................217 Using a Sunbquery to Return a List of Values ....................218 Apply Your Knowledge 8-1 ..................................219 Designing Correlated Subqueries .............................220 I Apply Your Knowledge 8-2 ..................................221 TOPIC 8B Changing Data Through Queries .......................222 Task 8B-1 Inserting Data Based on a Query ...................222 Deleting Rows Based on a Query .............................223 Task 8B-2 Deleting Rows Based on a Query ....................224 Contents ix