MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft® SQL Server™ 2000 Database Design and Implementation, Exam 70-229, Second Edition

Author Microsoft Corporation Pages 880 Disk 1 Companion CD(s); 1 Evaluation CD(s) Level Int/Adv Published 05/07/2003 ISBN 9780735619609 Price $59.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Related Series Related Books About the Author Support: Book & CD Rate this book

Table of Contents

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

Last Updated: May 15, 2003

Top of Page