View Larger Image	Microsoft Corp. Microsoft Press, Hardcover, 2nd Bk&CD edition, Published May 2003, 880 pages, ISBN 0735619603
	List Price: $59.99 Our Price: $37.50 You Save: $22.49 (37% Off)
	Availability: In-Stock

Read an Excerpt: Chapter 3: Designing a SQL Server Database       Excerpt provided courtesy of Microsoft Press.

Write a Review and tell the world about this title!

People who purchase this book frequently purchase:

• MCSA/MCSE Self-Paced Training Kit: Installing, Configuring, and Administering Microsoft Windows XP Professional, 2nd Edition (Exam 70-270); Walter Glenn, et al, $37.50, 37% Off!
• MCSE Self-Paced Training Kit: Microsoft Windows Server 2003 Core Requirements, 2nd Edition; Dan Holme, et al, $122.50, 39% Off!
• MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, Exam 70-228, 2nd Edition; Microsoft Corp., $37.50, 37% Off!
• MCAD/MCSD Self-Paced Training Kit: Microsoft .NET Core Requirements, Exams 70-305, 70-315, 70-306, 70-316, 70-310, 70-320, and 70-300; Microsoft Corp., $103.50, 39% Off!

• Certification : MCDBA
• Certification : MCAD
• Certification : MCSE
• * Series - Exam Study : Self-Paced Training Kit

• Microsoft Press

Key Book Benefits:

• Official Microsoft study guide helps you build the skills and expertise for designing database solutions with SQL Server 2000.
• Provides in-depth preparation for MCP Exam 70-229, "Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition," a core credit for MCDBA certification and an elective for the MCAD, MCSA, and MCSE tracks.
• This TRAINING KIT delivers self-paced training that maps to MCP exam goals and objectives, a testing tool that generates timed practice exams with automated scoring, and an electronic version of the study guide.

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

About the Authors

Founded in 1975, Microsoft (NASDAQ 'MSFT') is the worldwide leader in software for personal and business computing. The company offers a wide range of products and services designed to empower people through great software-any time, any place, and on any device.

Dec 1, 2004     A review from NYC
not enough for 70-229 exam
the book itself is not bad but sample questions are not even close to real life exam questions. They give you wrong idea of what exam will be like. Exams questions are bigger and more real-life. Book's questions are short and on more theory. Also, some questions are not even reviewed in this book. I used this book and just failed the exam.

Feb 6, 2004     A review from Cleveland, OH - USA
Great study book for the 70-229 exam
This is a very well written, comprehensive book that explains many of the things you need to know for the exam. The book is nicely formatted and has very good code snippets to demonstate the use of TSQL statements, stored procedures, etc. It could use more information in the areas concerning database replication, user functions and their purpose, and sql table locking hints and their use.

I usually use the Exam Cram 2 books for studying, but the one for the 70-229 exam is not highly rated. It is a good thing that Microsoft Press came through with this book.

Forgot your password? FAQs Shipping Options Returns Your Orders Your Account