MCSE Microsoft SQL Server 2000

If you took Microsoft Certified Professional (MCP) Exam 70-229 today, would you pass? With the Readiness Review MCP exam simulation on CD-ROM, you get a low-risk, low-cost way to find out! This next-generation test engine delivers a set of randomly generated, 50-question practice exams covering real MCP objectives. You can test and retest with different question sets each time-and with automated scoring, you get immediate Pass/Fail feedback. More important, you get answers to these four critical questions:

• What do MCP exam questions look like?
• In what topic and skill areas am I proficient/deficient?
• How should I focus my studies?
• Am I ready for the real exam?

• Developing a logical data model
• Implementing the physical database
• Retrieving and modifying data
• Programming business logic
• Tuning and optimizing data access
• Designing a database security plan

Contents

• Welcome to Microsoft SQL Server 2000 Database Design and Implementation vii
• Before You Begin viii
• Using the MCSE Readiness Review x
• Exam Objectives Summary xvi
• Getting More Help xviii

• Tested Skills and Suggested Practices 1
• Further Reading 3
• Objective 1.1: Define entities. 7
• Objective 1.2: Design entity keys. 15
• Objective 1.3: Design attribute domain integrity. 25

• Tested Skills and Suggested Practices 36
• Further Reading 37
• Objective 2.1: Create and alter databases. 39
• Objective 2.2: Create and alter database objects. 49
• Objective 2.3: Alter database objects to support replication and partitioned views. 63
• Objective 2.4: Troubleshoot failed object creation. 73

• Tested Skills and Suggested Practices 80
• Further Reading 82
• Objective 3.1: Import and export data. 85
• Objective 3.2: Manipulate heterogeneous data. 91
• Objective 3.3: Retrieve, filter, group, summarize,and modify data by using Transact-SQL. 97
• Objective 3.4: Manage result sets by using cursors and Transact-SQL. 109
• Objective 3.5: Extract data in XML format. 115

• Tested Skills and Suggested Practices 122
• Further Reading 123
• Objective 4.1: Manage data manipulation by using stored procedures, transactions, triggers, user-defined functions, and views. 127
• Objective 4.2: Enforce procedural business logic by using stored procedures, transactions, triggers, user-defined functions, and views. 139
• Objective 4.3: Troubleshoot and optimize programming objects. 149

• Tested Skills and Suggested Practices 156
• Further Reading 157
• Objective 5.1: Analyze the query execution plan. 159
• Objective 5.2: Capture, analyze, and replay SQL Profiler traces. 165
• Objective 5.3: Create and implement indexing strategies. 171
• Objective 5.4: Improve index use by using the Index Tuning Wizard. 175
• Objective 5.5: Monitor and troubleshoot database activity by using SQL Profiler. 181

• Tested Skills and Suggested Practices 188
• Further Reading 188
• Objective 6.1: Control data access by using stored procedures, triggers, user-defined functions, and views. 191
• Objective 6.2: Define object-level security including column-level permissions by using GRANT, REVOKE, and DENY. 197
• Objective 6.3: Create and manage application roles. 203