Microsoft® SQL Server" 2000 Database Design and Implementation, Exam 70-229, Second Edition

Developed by senior editors and content managers at Microsoft Corporation.

About This Book xxv PART 1 SELF-PACED TRAINING FOR MICROSOFT SQL SERVER 2000 DATABASE DESIGN AND IMPLEMENTATION CHAPTER 1 Introduction to Microsoft SQL Server 2000 3 About this Chapter 3 Before You Begin 3 Lesson 1: Overview of SQL Server 2000 4 What Is SQL Server 2000? 4 Databases 5 Relational Databases 5 SQL 5 XML 6 SQL Server 2000 Features 6 Ease of Installation, Deployment, and Use 6 Scalability 7 Data Warehousing 7 System Integration 8 Editions of SQL Server 2000 9 Lesson Summary 10 Lesson 2: Components of SQL Server 2000 11 Overview of the SQL Server 2000 Components 11 SQL Server 2000 Relational Database Engine 11 SQL Server 2000 Replication 13 SQL Server 2000 DTS 13 SQL Server 2000 Analysis Services 14 SQL Server 2000 English Query 14 SQL Server Meta Data Services 15 SQL Server Books Online 16 SQL Server 2000 Tools 16 Command Prompt Tools 17 User Interface Tools 18 SQL Server Enterprise Manager 18 SQL Server Agent 19 SQL Profiler 19 SQL Server Client Network Utility 19 SQL Server Network Utility 19 SQL Server Service Manager 20 SQL Query Analyzer 20 SQL Server 2000 Built-In Wizards 21 Lesson Summary 21 Lesson 3: Overview of SQL Server 2000 Architecture 22 Database Architecture 22 Logical Database Components 24 Database Objects 24 Collations 25 Logins, Users, Roles, and Groups 26 Physical Database Architecture 27 Pages and Extents 27 Database Files and Filegroups 28 Space Allocation and Reuse 28 Table and Index Architecture 29 Transaction Log Architecture 30 Relational Database Engine Architecture 30 Tabular Data Stream 31 Server Net-Libraries 31 Relational Database Engine 31 Query Processor Architecture 32 Memory Architecture 32 Input/Output (I/O) Architecture 33 Full-Text Query Architecture 33 Transactions Architecture 34 Administration Architecture 35 Data Definition Language, Data Manipulation Language, and Stored Procedures 36 SQL Distributed Management Framework 36 Graphical Tools 37 Automated Administration Architecture 37 Backup/Restore Architecture 38 Data Import/Export Architecture 38 DTS 38 Replication 38 Bulk Copying 38 Distributed Queries 39 Data Integrity Validation 39 Replication Architecture 40 Data Warehousing and Online Analytical Processing (OLAP) 40 OLTP Systems 40 OLAP Systems 41 Application Development Architecture 41 API or URL 42 APIs Supported by SQL Server 42 Database Language 42 Transact-SQL 42 XPath 43 Lesson Summary 43 Review 44 CHAPTER 2 Using Transact-SQL on a SQL Server Database 45 About This Chapter 45 Before You Begin 45 Lesson 1: SQL Server Programming Tools 46 SQL Query Analyzer 46 Query Window 47 Editor Pane 48 Color Coding in Query Analyzer 48 Executing Transact-SQL Statements 49 Results Pane 49 Grids Tab 49 Results Tab 50 Execution Plan Tab 51 Trace Tab 52 Statistics Tab 53 Messages Tab 54 Estimated Execution Plan Tab 54 Object Browser Window 55 Transact-SQL Debugger Window 55 Open Table Window 57 Object Search Window 57 isqlw Command-Prompt Utility 58 isql Command Prompt Utility 58 osql Command-Prompt Utility 59 Exercise 1: Navigating SQL Query Analyzer and Running a Query 59 Lesson Summary 64 Lesson 2: Introduction to Transact-SQL 65 Overview of Transact-SQL 65 Transact-SQL Statements 65 Data Definition Language 65 CREATE TABLE 66 ALTER TABLE 66 DROP TABLE 66 Data Control Language 67 GRANT 67 REVOKE 67 DENY 67 Data Manipulation Language 67 SELECT 68 INSERT 68 UPDATE 68 DELETE 68 Exercise 2: Creating and Executing DDL, DCL, and DML Statements 69 Lesson Summary 74 Lesson 3: Transact-SQL Syntax Elements 75 Identifiers 75 Classes of Identifiers 75 Regular Identifiers 76 Delimited Identifiers 76 Variables 76 Functions 77 Built-In Functions 77 Rowset Functions 77 Aggregate Functions 78 Scalar Functions 78 User-Defined Functions 79 Types of User-Defined Functions 79 Function Determinism 80 Data Types 80 Expressions 80 Using Operators in Expressions 80 Control-of-Flow Language Elements 81 Comments 82 Exercise 3: Using Transact-SQL Syntax Elements to Create a Script 83 Lesson Summary 86 Lesson 4: Executing Transact-SQL Statements 87 Single Transact-SQL Statements 87 Processing a SELECT Statement 88 Processing Other Statements 88 Batches 88 The GO Command 89 Batch Processing 90 Stored Procedures and Triggers 91 Stored Procedure and Trigger Execution 92 Transact-SQL Scripts 92 Lesson Summary 93 Review 94 CHAPTER 3 Designing a SQL Server Database 95 About This Chapter 95 Before You Begin 95 Lesson 1: Introduction to Database Design 96 Components of a SQL Server Database 96 Normalizing a Database Design 97 Achieving a Well-Designed Database 98 A Table Should Have an Identifier 98 A Table Should Store Data for Only a Single Type of Entity 99 A Table Should Avoid Nullable Columns 100 A Table Should Not Have Repeating Values or Columns 101 Entity Relationships 102 One-to-One Relationships 102 One-to-Many Relationships 103 Many-to-Many Relationships 103 Exercise 1: Exploring the Basic Concepts of Database Design 103 Lesson Summary 107 Lesson 2: Planning a SQL Server Database 108 Files and Filegroups 108 Rules for Designing Files and Filegroups 109 Default Filegroups 109 Recommendations 110 Transaction Logs 110 Environment 111 Estimating the Size of a Database 111 Physical Database Design 112 SQL Server Installation 112 Security 113 Planning Security 113 Security Levels 113 Authentication Modes 113 Lesson Summary 114 Lesson 3: Identifying System Requirements 115 The Process of Identifying System Requirements 115 Identifying System Goals 116 Identifying the Amount and Types of Data 117 Identifying How the Data Will Be Used 118 Identifying Business Rules of the System 119 Exercise 2: Identifying the System Requirements for Your Database Design 120 Book Shop Scenario 120 Lesson Summary 123 Lesson 4: Developing a Logical Data Model 124 Identifying Entities and Their Attributes 124 Identifying Relationships Between Entities 126 Identifying Constraints on Data 127 Exercise 3: Developing a Logical Data Model 128 Lesson Summary 135 Review 136 CHAPTER 4 Implementing SQL Server Databases and Tables 137 About This Chapter 137 Before You Begin 137 Lesson 1: Creating and Managing a SQL Server Database 138 Creating a SQL Server Database 138 Methods for Creating a SQL Server Database 139 CREATE DATABASE Statement 139 Enterprise Manager 140 Create Database Wizard 140 Managing a SQL Server Database 141 Viewing Information about a Database 141 Modifying a Database 141 Setting Database Options 142 Deleting a SQL Server Database 143 Exercise 1: Creating and Managing a Database 143 Lesson Summary 147 Lesson 2: Identifying Data Types 148 System-Supplied Data Types 148 User-Defined Data Types 152 Exercise 2: Identifying Column Data Types 152 Lesson Summary 154 Lesson 3: Creating and Managing Tables 155 Creating Tables in a SQL Server Database 155 Determining Column Nullability 155 Defining Default Values 157 Autonumbering and Identifier Columns 158 Creating Identifier Columns 159 IDENTITY Property 159 Globally Unique Identifiers 160 Methods for Creating a Table 160 CREATE TABLE Statement 161 Enterprise Manager 161 Database Designer 161 Managing Tables in a SQL Server Database 162 Viewing Information about Tables 162 Modifying Tables in a SQL Server Database 162 Deleting Tables from a SQL Server Database 163 Exercise 3: Creating and Managing Tables in a SQL Server Database 164 Lesson Summary 166 Review 168 CHAPTER 5 Implementing Data Integrity 169 About This Chapter 169 Before You Begin 169 Lesson 1: Introduction to Data Integrity 170 Enforcing Data Integrity 170 Data Types 170 NOT NULL Definitions 171 DEFAULT Definitions 171 IDENTITY Properties 171 Constraints 171 Rules 172 Triggers 172 Indexes 172 Types of Data Integrity 172 Entity Integrity 173 Domain Integrity 173 Referential Integrity 173 User-Defined Integrity 174 Exercise 1: Identifying the Properties Used to Ensure Data Integrity 174 Lesson Summary 175 Lesson 2: Implementing Integrity Constraints 176 Introduction to Integrity Constraints 176 PRIMARY KEY Constraints 177 Creating PRIMARY KEY Constraints 177 UNIQUE Constraints 179 Creating UNIQUE Constraints 179 FOREIGN KEY Constraints 180 Creating FOREIGN KEY Constraints 181 Disabling FOREIGN KEY Constraints 182 CHECK Constraints 183 Creating CHECK Constraints 183 Disabling CHECK Constraints 184 Exercise 2: Adding Constraints to Existing Tables 185 Lesson Summary 190 Review 191 CHAPTER 6 Accessing and Modifying Data 193 About This Chapter 193 Before You Begin 193 Lesson 1: Accessing Data in a SQL Server Database 194 The Fundamentals of a SELECT Statement 194 The SELECT Clause 195 Using Keywords in the Select List 195 The DISTINCT Keyword 195 The TOP n Keyword 195 The AS Keyword 196 Types of Information in the Select List 196 The INTO Clause 197 The FROM Clause 197 The WHERE, GROUP BY, and HAVING Clauses 197 The GROUP BY Clause 198 Processing the WHERE, GROUP BY, and HAVING Clauses 199 The ORDER BY Clause 199 Exercise 1: Using SELECT Statements to Access Data 199 Lesson Summary 202 Lesson 2: Using Advanced Query Techniques to Access Data 203 Using Joins to Retrieve Data 203 Inner Joins 204 Outer Joins 205 Using Left Outer Joins 205 Using Right Outer Joins 205 Using Full Outer Joins 206 Defining Subqueries inside SELECT Statements 206 Types of Subqueries 207 Subqueries that Are Used with IN or NOT IN 207 Subqueries that Are Used with Comparison Operators 208 Subqueries that Are Used with EXISTS and NOT EXISTS 208 Summarizing Data 209 Using the CUBE Operator to Summarize Data 209 Using the ROLLUP Operator to Summarize Data 210 Exercise 2: Using Advanced Query Techniques to Retrieve Data 210 Lesson Summary 215 Lesson 3: Modifying Data in a SQL Server Database 216 Inserting Data into a SQL Server Database 216 Using the INSERT Statement to Add Data 216 Using an INSERT VALUES Statement to Add Data 217 Using a SELECT Subquery to Add Data 218 Using a SELECT INTO Statement to Add Data 218 Adding ntext, text, or image Data to Inserted Rows 218 Using Bulk Copy Operations to Add Data 219 Modifying Data in a SQL Server Database 219 Using an UPDATE Statement to Modify Data 219 Using a SET Clause to Modify Data 220 Using a WHERE Clause to Modify Data 220 Using a FROM Clause to Modify Data 220 Using APIs and Cursors to Modify Data 221 Modifying ntext, text, or image Data 221 Deleting Data from a SQL Server Database 221 Using a DELETE Statement to Delete Data 222 Using APIs and Cursors to Delete Data 222 Using the TRUNCATE TABLE Statement to Delete Data 222 Exercise 3: Modifying Data in a SQL Server Database 223 Lesson Summary 226 Review 227 CHAPTER 7 Managing and Manipulating Data 229 About This Chapter 229 Before You Begin 229 Lesson 1: Importing and Exporting Data 230 Using the bcp Utility and the BULK INSERT Statement 230 Using Data Formats 232 Using DTS 234 DTS Tools 234 DTS Packages 234 DTS Tasks 235 DTS Transformations 235 DTS Connections 236 DTS Package Workflow 237 Exercise 1: Importing and Exporting Data 237 Lesson Summary 240 Lesson 2: Using Distributed Queries to Access External Data 241 Introduction to Distributed Queries 241 Using Linked Server Names in Distributed Queries 242 Linked Servers 242 Four-Part Names 243 The OPENQUERY Function 243 Using Ad Hoc Computer Names in Distributed Queries 244 The OPENROWSET Function 244 The OPENDATASOURCE Function 245 Exercise 2: Using Distributed Queries to Access External Data 245 Lesson Summary 247 Lesson 3: Using Cursors to Retrieve Data 248 Introduction to Cursors 248 Transact-SQL Server Cursors 249 Referencing Transact-SQL Cursors 250 API Server Cursors 251 API Server Cursor Restrictions 252 Client Cursors 252 Fetching and Scrolling 252 Controlling Cursor Behavior 253 Cursor Locking 254 Exercise 3: Creating a Cursor to Retrieve Data 254 Lesson Summary 256 Lesson 4: Retrieving XML Data 257 Introduction to XML 257 Using the FOR XML Clause to Retrieve Data 258 RAW, AUTO, EXPLICIT 259 RAW Mode 259 AUTO Mode 259 EXPLICIT Mode 260 XMLDATA 260 ELEMENTS 260 BINARY Base64 260 Using the OPENXML Function to Access XML Data 260 XML Document Handle (idoc) 262 Xpath Expression (rowpattern) 262 Mapping (flags) 262 SchemaDeclaration 263 TableName 263 Exercise 4: Retrieving XML Data 263 Lesson Summary 265 Review 266 CHAPTER 8 Implementing Stored Procedures 267 About This Chapter 267 Before You Begin 267 Lesson 1: Introduction to Stored Procedures 268 Purpose and Advantages of Stored Procedures 268 Performance 268 Programming Framework 269 Security 270 Categories of Stored Procedures 270 System Stored Procedures 270 Local Stored Procedures 271 Temporary Stored Procedures 271 Extended Stored Procedures 272 Remote Stored Procedures 273 Exercise 1: Exploring Stored Procedures 273 Lesson Summary 275 Lesson 2: Creating, Executing, Modifying, and Deleting Stored Procedures 276 How a Procedure Is Stored 276 Methods for Creating Stored Procedures 277 The CREATE PROCEDURE Statement 278 Providing a Stored Procedure with Context 278 Creating Temporary Stored Procedures 279 Grouping, Caching, and Encrypting Stored Procedures 280 Enterprise Manager 281 Create Stored Procedure Wizard 282 Creating and Adding Extended Stored Procedures 282 Deferred Name Resolution 283 Executing a Stored Procedure 283 Calling a Stored Procedure for Execution 283 Specifying Parameters and Their Values 284 Executing Stored Procedures when SQL Server Starts 285 Modifying Stored Procedures 286 Deleting Stored Procedures 287 Exercise 2: Working with Stored Procedures 287 Lesson Summary 292 Lesson 3: Programming Stored Procedures 293 Parameters and Variables 293 The RETURN Statement and Error Handling 295 Default Values and Setting a Parameter to NULL 297 Testing for Server Errors 298 Nesting Procedures 300 Cursors 300 Data Retrieval Methods 301 Exercise 3: Programming Stored Procedures to Insert and Retrieve Data 302 Lesson Summary 307 Review 309 CHAPTER 9 Implementing Triggers 311 About This Chapter 311 Before You Begin 312 Lesson 1: Introduction to Triggers 313 Extending Data Integrity with Triggers 313 Procedural Data Integrity 313 Trigger Features and Limitations 314 Trigger Events 315 Trigger Execution 315 Exercise 1: Applying Cascading Referential Integrity Constraints 316 Lesson Summary 319 Lesson 2: Creating and Managing Triggers 320 Creating Triggers Using Transact-SQL 320 The CREATE TRIGGER Clause 321 The ON Clause 321 The FOR, AFTER, and INSTEAD OF Clauses 321 The AS Clause 322 Creating a Trigger Using Enterprise Manager 322 Trigger Management 323 Altering and Renaming Triggers 323 Viewing, Dropping, and Disabling Triggers 325 Exercise 2: Creating and Managing Triggers 326 Lesson Summary 329 Lesson 3: Programming Triggers 330 The Inserted and Deleted Pseudo Tables 330 Trigger Syntax, System Commands, and Functions 331 UPDATE (column_name) and (COLUMNS_UPDATED()) Clauses 331 Functions and System Commands 332 Transact-SQL Language Precautions 333 Common Trigger Programming Tasks 333 Exercise 3: Creating a Trigger to Update a Column Value 335 Lesson Summary 338 Review 339 CHAPTER 10 Implementing Views 341 About This Chapter 341 Before You Begin 341 Lesson 1: Introduction to Views 342 Overview of Views 342 Scenarios for Using Views 343 To Focus on Specific Data 344 To Simplify Data Manipulation 344 To Customize Data 344 To Export and Import Data 344 To Combine Partitioned Data 344 Lesson Summary 345 Lesson 2: Creating, Modifying, and Deleting Views 346 Creating Views 346 Creating Standard Views 348 Creating Indexed Views 348 Creating the Index 350 Creating Partitioned Views 351 Modifying Views 353 Deleting Views 354 Exercise 1: Creating and Modifying a View 354 Lesson Summary 356 Lesson 3: Accessing Data through Views 357 Viewing Data through Views 357 Modifying Data through Views 357 Modifying Data through Basic Views 358 Adding Data through a View 359 Changing Data through a View 359 Deleting Data through a View 360 Modifying Data through Partitioned Views 360 INSERT Statements 360 UPDATE Statements 361 DELETE Statements 361 Modifying Data when the INSTEAD OF Trigger Is Used 361 Exercise 2: Using the AuthorsBooks View to Access Data 362 Lesson Summary 364 Review 365 CHAPTER 11 Implementing Indexes 367 About This Chapter 367 Before You Begin 367 Lesson 1: Index Architecture 368 Purpose and Structure 368 Index Types 369 Clustered Indexes 369 Nonclustered Indexes 370 Index Characteristics 371 Unique 371 Composite 371 Fill Factor and Pad Index 372 Sort Order 373 Index Information 373 Full-Text Indexing 375 Exercise 1: Viewing Index Properties and Using an Index 375 Lesson Summary 377 Lesson 2: Index Creation and Administration 378 Index Creation 378 Using a Graphical Interface 378 Using Transact-SQL Statements 380 Index Administration 382 Deleting an Index 383 Rebuilding an Index 383 Renaming an Index 384 Choosing to Index 384 Index Performance 386 Index Statistics 386 Exercise 2: Creating a Clustered Index 387 Lesson Summary 390 Review 392 CHAPTER 12 Managing SQL Server Transactions and Locks 393 About This Chapter 393 Before You Begin 393 Lesson 1: Transaction and Locking Architecture 394 Transaction Log Architecture 394 Write-Ahead Transaction Log 395 Transaction Log Logical Architecture 395 Checkpoints and the Active Portion of the Log 396 Truncating the Transaction Log 397 Transaction Log Physical Architecture 397 Shrinking the Transaction Log 398 Concurrency Architecture 399 Locking Architecture 399 Distributed Transaction Architecture 401 Exercise 1: Accessing and Modifying the Transaction Log 401 Lesson Summary 402 Lesson 2: Managing SQL Server Transactions 403 Overview of SQL Server Transactions 403 Specifying Transaction Boundaries 404 Transact-SQL Statements Allowed in Transactions 405 Coding Efficient Transactions 405 Avoiding Concurrency Problems 405 Errors During Transaction Processing 406 Types of Transactions 406 Explicit Transactions 406 Autocommit Transactions 407 Compile and Run-Time Errors 408 Implicit Transactions 409 Transact-SQL Implicit Transactions 409 API Implicit Transactions 410 Distributed Transactions 411 Transact-SQL Distributed Transactions 411 Required Transact-SQL Statements 412 MS DTC Distributed Transactions 412 Exercise 2: Implementing Explicit Transactions 413 Lesson Summary 416 Lesson 3: Managing SQL Server Locking 417 Types of Concurrency Problems 417 Lost Updates 417 Uncommitted Dependency (Dirty Read) 418 Inconsistent Analysis (Non-repeatable Read) 418 Phantom Reads 418 Optimistic and Pessimistic Concurrency 419 Optimistic Concurrency 419 Pessimistic Concurrency 419 Isolation Levels 419 SQL-92 Isolation Levels 419 Customizing Locking 420 Managing Deadlocks 421 Minimizing Deadlocks 421 Customizing Timeouts 422 Setting Transaction Isolation Levels 423 Implementing Table-Level Locking Hints 424 Customizing Locking for an Index 426 Exercise 3: Configuring Transaction Properties 427 Lesson Summary 428 Review 429 CHAPTER 13 Designing and Administering SQL Server 2000 Security 431 About This Chapter 431 Before You Begin 431 Lesson 1: Overview of SQL Server 2000 Security 432 Physical Security 432 Network Protocol Security 432 Domain Security 434 Local Computer Security 434 SQL Server Security 435 Authentication 435 Authorization 437 Groups and Roles 437 Permission States 440 Object and Statement Permissions 440 Auditing 441 Object Encryption 441 Application 442 Lesson Summary 442 Lesson 2: Designing a Database Security Plan 443 Requirements 443 Nesting and Ownership Chains 444 Nesting 445 Ownership Chains 445 Security Design Recommendations 446 Users, Groups, and Roles 446 Permissions 446 Exercise 1: Designing Security for BookShopDB 447 Lesson Summary 448 Lesson 3: Database Security Implementation and Administration 449 Administering Authentication 449 Configuring Authentication in Enterprise Manager 449 Configuring Authentication Using Transact-SQL 450 Windows Accounts 450 SQL Server Login ID 451 Administering Authorization 451 Configuring Authorization in Enterprise Manager 451 Configuring Authorization Using Transact-SQL 452 Administering Permissions 453 Configuring Permissions in Enterprise Manager 453 Configuring Permissions Using Transact-SQL 454 The GRANT Statement 454 The Revoke Statement 456 The DENY Statement 457 Administering Roles 457 Adding and Deleting Roles 457 Administering Role Membership 458 Activating an Application Role 459 Exercise 2: Implementing Security for BookShopDB 460 Lesson Summary 463 Review 465 CHAPTER 14 SQL Server Monitoring and Tuning 467 About this Chapter 467 Before You Begin 467 Lesson 1: Monitoring Databases with SQL Profiler 468 SQL Server Monitoring 468 SQL Profiler 469 Monitoring with SQL Profiler 469 Choosing Events, Data Columns, and Filters 470 Preparing to Run a Trace in SQL Profiler 471 Using a Trace Template 473 Controlling the Trace 473 Replaying a Trace 473 Exercise 1: Capturing Events Using SQL Profiler 474 Lesson Summary 477 Lesson 2: Index Tuning and Database Partitioning 478 SQL Server Tuning 478 Index Tuning Wizard Overview 478 Running the Index Tuning Wizard 480 Partitioning Overview 481 File and Disk Partitioning 481 Federated and Clustered Servers 481 Distributed Partitioned Views 482 Exercise 2: Tuning Queries Using the Index Tuning Wizard 482 Lesson Summary 485 Review 486 PART 2 PREPARATION FOR MCP EXAM 70-229 OBJECTIVE DOMAIN 1: Developing a Logical Data Model 489 Tested Skills and Suggested Practices 489 Further Reading 491 Objective 1.1: Define entities 495 Questions 497 Answers 500 Objective 1.2: Design entity keys 503 Questions 505 Answers 509 Objective 1.3: Design attribute domain integrity 513 Questions 515 Answers 518 OBJECTIVE DOMAIN 2: Implementing the Physical Database 521 Tested Skills and Suggested Practices 522 Further Reading 523 Objective 2.1: Create and alter databases 525 Questions 526 Answers 530 Objective 2.2: Create and alter database objects 535 Questions 536 Answers 542 Objective 2.3: Alter database objects to support replication and partitioned views 549 Questions 551 Answers 554 Objective 2.4: Troubleshoot failed object creation 559 Questions 560 Answers 562 OBJECTIVE DOMAIN 3: Retrieving and Modifying Data 565 Tested Skills and Suggested Practices 566 Further Reading 568 Objective 3.1: Import and export data 571 Questions 572 Answers 574 Objective 3.2: Manipulate heterogeneous data 577 Questions 579 Answers 581 Objective 3.3: Retrieve, filter, group, summarize, and modify data by using Transact-SQL 583 Questions 585 Answers 590 Objective 3.4: Manage result sets by using cursors and Transact-SQL 595 Questions 597 Answers 598 Objective 3.5: Extract data in XML format 601 Questions 603 Answers 605 OBJECTIVE DOMAIN 4: Programming Business Logic 607 Tested Skills and Suggested Practices 608 Further Reading 609 Objective 4.1: Manage data manipulation by using stored procedures, transactions, triggers, user-defined functions, and views 613 Questions 615 Answers 620 Objective 4.2: Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views 625 Questions 626 Answers 631 Objective 4.3: Troubleshoot and optimize programming objects 635 Questions 636 Answers 638 OBJECTIVE DOMAIN 5: Tuning and Optimizing Data Access 641 Tested Skills and Suggested Practices 642 Further Reading 643 Objective 5.1: Analyze the query execution plan 645 Questions 646 Answers 649 Objective 5.2: Capture, analyze, and replay SQL Profiler traces 651 Questions 652 Answers 654 Objective 5.3: Create and implement indexing strategies 657 Questions 658 Answers 659 Objective 5.4: Improve index use by using the Index Tuning Wizard 661 Questions 662 Answers 664 Objective 5.5: Monitor and troubleshoot database activity by using SQL Profiler 667 Questions 668 Answers 670 OBJECTIVE DOMAIN 6: Designing a Database Security Plan 673 Tested Skills and Suggested Practices 674 Further Reading 674 Objective 6.1: Control data access by using stored procedures, triggers, user-defined functions, and views 677 Questions 678 Answers 680 Objective 6.2: Define object-level security including column-level permissions by using GRANT, REVOKE, and DENY 683 Questions 684 Answers 686 Objective 6.3: Create and manage application roles 689 Questions 690 Answers 692 APPENDIX: Questions and Answers 695 GLOSSARY 749 INDEX 797