Table of Contents
Introduction
Hardware Requirements
Software Requirements
Installing SQL Server 2005
Installing Visual Studio 2005
Installing the AdventureWorks Database
Installing Microsoft Web Application Stress Tool
Case Scenarios
Case Scenarios and the 70-442 Exam
Case Scenario Structure
Using the CD and DVD
How to Install the Practice Tests
How to Use the Practice Tests
How to Uninstall the Practice Tests
Microsoft Certified Professional Program
Technical Support
Evaluation Edition Software Support
1 Designing a Data Access Strategy
Before You Begin
Lesson 1: Designing Data Access Technologies
Selecting a Network Protocol
Selecting a Data Provider
How to Connect by Using ADO.NET
How to Connect by Using MDAC
How to Connect by Using SQL Native Client
Managing Password Policies
Lab: Connecting to a SQL Server Service
Lesson 2: Designing a Data Access Connection
Designing Connections for Heterogeneous Client Computers
Designing Connections for HTTP Web Services
Designing Connections for Another SQL Server Instance
Lab: Creating and Consuming HTTP Endpoints
Lesson 3: Designing a Data Access Object Model
What is the ADO Object Model?
Using a Disconnected Model with ADO.NET
Using a Connected Model with ADO.NET
Lab: Selecting a Data Access Object Model
Case Scenario: Selecting a Data Access Strategy
Suggested Practices
References
Chapter Summary
2 Designing Database Queries
Before You Begin
Lesson 1: Writing Database Queries
Write SELECT Queries
Writing Full-Text Queries
Lab: Writing Database Queries
Lesson 2: Improving Query Performance
Showing the Execution Plan
Using Query Hints
Using Plan Guides
Using Searchable Arguments
Lab: Tuning Queries
Lesson 3: Retrieving Data from XML Sources
Working with XML Data
Lab: Working with XML Data
Case Scenario: Creating a Plan Guide
Suggested Practices
References
Chapter Summary
3 Designing a Cursor and Caching Strategy
Before You Begin
Lesson 1: Designing Caching Strategies
What is Output Caching?
Designing Custom Caching Functionality
Using Query Notifications
Designing a Refresh Strategy
Lab: Implementing Output Caching
Lesson 2: Designing a Cursor Strategy
Considering Cursor Alternatives
Maximizing Cursor Performance
Evaluating Use of Cursors
Lab: Creating a Cursor
Lesson 3: Designing Efficient Cursors
Using Scrollable Cursors
Processing on a Row-by-Row Basis
Using Dynamic SQL
Selecting a Cursor Type
Evaluating Cursor Efficiency
Lab: Designing Cursors
Case Scenario: Evaluating Cursor Performance
Suggested Practices
References
Chapter Summary
4 Advanced Query Topics
Before You Begin
Lesson 1: Administering a SQL Server Service
Designing Server Management Objects Applications
Designing Replication Management Objects Applications
Designing Analysis Management Objects Applications
Lab: Administrative Programming
Lesson 2: Querying with Multiple Active Result Sets
Using MARS
Using Transactions
Lab: Creating MARS Connections
Lesson 3: Performing Asynchronous Processing
Using Asynchronous Processing
Lab: Performing Asynchronous Processing
Case Scenario: Automating a Data-Mining Solution
Suggested Practices
References
Chapter Summary
5 Data Integrity and Error Handling in SQL Server 2005
Before You Begin
Lesson 1: Validating Data and Permissions
Implementing Declarative Data Integrity
Implementing Procedural Data Integrity
Using T-SQL to Verify Permissions
Lab: Comparing Procedural and Declarative Data Validation Performance
Lesson 2: Detecting and Reacting to Errors
Techniques for Detecting and Reacting to Errors
Creating User-Defined Messages
Lab: Using Try/Catch Blocks
Case Scenario: Validating Data Integrity
Suggested Practices
Reference
Chapter Summary
6 Designing Transactions and Transaction Isolation
Before You Begin
Lesson 1: Understanding the Available Transaction Isolation Levels
Types of Concurrency Problems
Using Locks to Solve Concurrency Problems
Choosing the Correct Isolation Level
Avoiding Extensive Blocking
Lab: Comparing Transaction Isolation Levels
Lesson 2: Designing Transactions and Optimizing Locking
Optimizing Locking
Minimizing Deadlocks
Working with Distributed Transactions
Lab: Designing Code That Uses Transactions
Case Scenario: Optimizing Locking
Suggested Practices
References
Chapter Summary
7 Optimizing SQL Server 2005 Performance
Before You Begin
Lesson 1: Optimizing and Tuning Queries
Evaluating Query Performance
Optimizing Query Performance
Lab: Comparing Query Performance
Lesson 2: Optimizing Index Strategies
Improving Performance with Covered Indexes
Using Included Columns and Index Depth
Using Clustered Indexes
Read Performance vs. Write Performance
Using Computed Columns
Using Indexed Views
Analyzing Index Usage
Lab: Optimizing Queries Through Indexing
Lesson 3: Optimizing Data Storage
Optimizing Row Width
De-Normalizing
Data Types
Lab: De-Normalizing an Aggregation
Case Scenario: Optimizing Query Performance
Suggested Practices
References
Chapter Summary
8 Improving Database Application Performance
Before You Begin
Lesson 1: Scale Database Applications
Specifying a Data-Partitioning Model
Targeting Multiple Servers
Implementing Scale-Out Techniques
Transparently Distributing Data and Workload
Moving Code to a Different Tier
Rewriting Algorithms
Lab: Scaling Database Applications
Lesson 2: Resolving Performance Problems
Analyzing Application Performance
Investigating Performance Issues
Lab: Analyzing Performance
Case Scenario: Diagnosing a Performance Problem
Suggested Practices
References
Chapter Summary
Answers
A References
Design Appropriate Data Access Technologies
Design an Appropriate Data Access Object Model
Write Queries
Design Caching Strategies
Design a Cursor Strategy
Design Applications that Administer a SQL Server Service
Scale Database Applications
Resolve Performance Problems
Manage Concurrency by Selecting the Appropriate Transaction Isolation Levels
Design Code that Uses Transactions
Optimize and Tune Queries for Performance
Design Code that Validates Input Data and Permissions
B SQL Server 2005 Architecture and Internals
Hardware Trends and Their Effect on SQL Server 2005
Hardware Building Blocks
Hardware Trends
Implications of Hardware on Software Design
SQLOS
SQLOS Architecture
Using SQLOS to Determine Application Performance Characteristics
Common Language Runtime (CLR) Host
Design Goals for the CLR Host
CLR Services
CLR Database Object Types
CLR vs. Transact-SQL
Deciding When to Use CLR Code
SQL Server 2005 Storage Engine
Storage Engine Architecture and Terminology
SQL Server Database Engine Physical Operations
SQL Server 2005 Query Engine
Query Engine Processing
SQL Server 2005 Index Internals
SQL Server 2005 Index Organization
Full-Text Indexes
XML Indexes
Database Encryption
Certificates
Configuring SQL Server for SSL
Glossary
Index
© Microsoft. All Rights Reserved.