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.