MCITP Self-Paced Training Kit (Exam 70-442): Designing and Optimizing Data Access by Using Microsoft SQL Server(TM) 2005 (Self-Paced Training Kits)

525 Pages·2007·6.87 MB·English

by  Sara Morgan

PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2007 by GrandMasters
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form
or by any means without the written permission of the publisher.
Library of Congress Control Number:2007922585
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 2 1 0 9 8 7
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIPcatalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further infor-
mation about international editions, contact your local Microsoft Corporation office or contact Microsoft
Press International directly at fax [phone]. Visit our Web site at www.microsoft.com/mspress.
Send comments [email protected].
Microsoft, Microsoft Press, Active Directory, ActiveSync, ActiveX, Excel, Internet Explorer, MSDN,
MSN, Outlook, PowerPoint, SQL Server, Visual Basic, Visual C#, Visual FoxPro, Visual J#, Visual
Studio, Win32, Windows, Windows Live, Windows Mobile, Windows NT, Windows Server, and
Windows Vistaare either registered trademarks or trademarks of Microsoft Corporation in the United
States and/or other countries. Other product and company names mentioned herein may be the
trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,
and events depicted herein are fictitious. No association with any real company, organization, product,
domain name, e-mail address, logo, person, place, or event is intended or should be inferred.
(cid:55)(cid:75)(cid:76)(cid:86)(cid:3)(cid:69)(cid:82)(cid:82)(cid:78)(cid:3)(cid:72)(cid:91)(cid:83)(cid:85)(cid:72)(cid:86)(cid:86)(cid:72)(cid:86)(cid:3)(cid:87)(cid:75)(cid:72)(cid:3)(cid:68)(cid:88)(cid:87)(cid:75)(cid:82)(cid:85)(cid:182)(cid:86)(cid:3)(cid:89)(cid:76)(cid:72)(cid:90)(cid:86)(cid:3)(cid:68)(cid:81)(cid:71)(cid:3)(cid:82)pinions. The information contained in this book is provided
without any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its
resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly
or indirectly by this book.
Acquisitions Editor:Ken Jones
Developmental Editor:Karen Szall
Project Editor:Maria Gargiulo
Editorial Production:nSight, Inc.
Body Part No. X13-62481
Dedication
To my Mom and Dad, who have always been a tremendous inspiration for me.
I am nothing without you both. I love you.
—Sara Morgan
To my fiancée, Frida, my mother, Viveca, and the memory of my father, Bertil.
—Tobias Thernström
To my wife, Mary, who patiently endured the late nights and long weekends that were spent
on this effort, and to Nikki the Rottweiler, who tried her best to help but just
couldn’t get the syntax down.
—Ted Malone
About the Authors
Sara Morgan
SaraMorgan, an independent author and consultant based in
Baton Rouge, Louisiana, is an MCSD and MCDBA and is certified
in SQL Server 2000 and Visual Basic .NET. She specializes in devel-
oping leading-edge Web-based applications using Microsoft tech-
nologies. Since graduating from Louisiana State University with a
degree in quantitative business analysis, she has been developing
software for a variety of industries, including a not-for-profit hospi-
tal, a financial company offering mortgages, a major retailer, a soft-
ware company that writes legislative software, and an application
service provider.
Sara has written articles for MSDN Magazine, Enterprise Development, .NET Development, Visual
Studio Magazine, and DevX.com. She has also co-written MCTS Self-Paced Training Kit (Exam
70-529): Microsoft .NET Framework 2.0 Distributed Application Development (Microsoft Press,
2006) and MCPD Self-Paced Training Kit (Exam 70-547): Designing and Developing Web-Based
Applications Using the Microsoft .NET Framework. Sara’s articles about enhanced computing
and her latest research efforts can be found at http://www.custsolutions.net.
Tobias Thernström
Tobias Thernström has enjoyed the company of SQL Server for
over 10 years. He is a senior database architect and trainer at Rbam
AB (www.rbam.se), a professional software services company
located in Sweden. Tobias has been involved in the development of
several SQL Server certifications provided by Microsoft. He is an
MCT and co-founder of the Swedish SQL Server User Group
(www.sqlug.se).
vii
viii
Ted Malone
Ted Malone has been working with SQL Server since the days of
OS/2 and has developed database systems ranging from small to
extremely large. Ted is currently product strategy architect for Con-
figuresoft, Inc. (http://www.configuresoft.com), a Colorado Springs,
Colorado–based software development firm and Microsoft Gold
ISV Partner that specializes in delivering enterprise management
tools for Fortune 1000 corporations. Ted is the vice president of
the Rocky Mountain SQL Server Users Group and a frequent con-
tributor to the Professional Association for SQL Server. Ted was
also a contributing author to SQL Server Security (McGraw-Hill
Osborne Media, 2003) as well as a contributor to several SQL
Server 2005 study guides.
In his free time, Ted enjoys the Colorado wilderness with his family and dogs. Ted is also an
avid pilot with more than 4,000 hours of flight time and enjoys riding his motorcycle when-
ever possible.
Contents at a Glance
1 Designing a Data Access Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
2 Designing Database Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47
3 Designing a Cursor and Caching Strategy . . . . . . . . . . . . . . . . . . . . . . . . .97
4 Advanced Query Topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
5 Data Integrity and Error Handling in SQL Server 2005 . . . . . . . . . . . . .193
6 Designing Transactions and Transaction Isolation . . . . . . . . . . . . . . . . .233
7 Optimizing SQL Server 2005 Performance . . . . . . . . . . . . . . . . . . . . . . .283
8 Improving Database Application Performance. . . . . . . . . . . . . . . . . . . .341
Answers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .395
Appendix A References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .399
Appendix B SQL Server 2005 Architecture and Internals . . . . . . . . . .405
ix
Table of Contents
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Hardware Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Software Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xx
Installing SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Installing Visual Studio 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxii
Installing the AdventureWorks Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxii
Installing Microsoft Web Application Stress Tool . . . . . . . . . . . . . . . . . . . . . . . xxiii
Case Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Case Scenarios and the 70-442 Exam. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Case Scenario Structure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv
Using the CD and DVD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiv
How to Install the Practice Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxv
How to Use the Practice Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxv
How to Uninstall the Practice Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi
Microsoft Certified Professional Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvi
Technical Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
Evaluation Edition Software Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvii
1 Designing a Data Access Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
Lesson 1: Designing Data Access Technologies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
Selecting a Network Protocol. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .2
Selecting a Data Provider . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
How to Connect by Using ADO.NET. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6
How to Connect by Using MDAC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning
resources for you. To participate in a brief online survey, please visit:
www.microsoft.com/learning/booksurvey/ xi
xii Table of Contents
How to Connect by Using SQL Native Client. . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Managing Password Policies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Lab: Connecting to a SQL Server Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Lesson 2: Designing a Data Access Connection. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Designing Connections for Heterogeneous Client Computers. . . . . . . . . . . . . 19
Designing Connections for HTTP Web Services. . . . . . . . . . . . . . . . . . . . . . . . . . 22
Designing Connections for Another SQL Server Instance . . . . . . . . . . . . . . . . . 25
Lab: Creating and Consuming HTTP Endpoints. . . . . . . . . . . . . . . . . . . . . . . . . . 26
Lesson 3: Designing a Data Access Object Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
What is the ADO Object Model? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Using a Disconnected Model with ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Using a Connected Model with ADO.NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Lab: Selecting a Data Access Object Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Case Scenario: Selecting a Data Access Strategy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
2 Designing Database Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Lesson 1: Writing Database Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Write SELECT Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Writing Full-Text Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Lab: Writing Database Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Lesson 2: Improving Query Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Showing the Execution Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Using Query Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Using Plan Guides. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Using Searchable Arguments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Lab: Tuning Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Lesson 3: Retrieving Data from XML Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Working with XML Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Table of Contents xiii
Lab: Working with XML Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90
Case Scenario: Creating a Plan Guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .94
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .95
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96
3 Designing a Cursor and Caching Strategy . . . . . . . . . . . . . . . . . . . . . . . . .97
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98
Lesson 1: Designing Caching Strategies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
What is Output Caching? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99
Designing Custom Caching Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
Using Query Notifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Designing a Refresh Strategy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Lab: Implementing Output Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Lesson 2: Designing a Cursor Strategy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Considering Cursor Alternatives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Maximizing Cursor Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Evaluating Use of Cursors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Lab: Creating a Cursor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
Lesson 3: Designing Efficient Cursors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Using Scrollable Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
Processing on a Row-by-Row Basis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Using Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Selecting a Cursor Type. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Evaluating Cursor Efficiency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Lab: Designing Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Case Scenario: Evaluating Cursor Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
References. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Chapter Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142
4 Advanced Query Topics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .143
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
xiv Table of Contents
Lesson 1: Administering a SQL Server Service. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Designing Server Management Objects Applications . . . . . . . . . . . . . . . . . . . 145
Designing Replication Management Objects Applications . . . . . . . . . . . . . . . 149
Designing Analysis Management Objects Applications. . . . . . . . . . . . . . . . . . 154
Lab: Administrative Programming. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Lesson 2: Querying with Multiple Active Result Sets . . . . . . . . . . . . . . . . . . . . . . . . . 170
Using MARS. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
Using Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Lab: Creating MARS Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Lesson 3: Performing Asynchronous Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Using Asynchronous Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Lab: Performing Asynchronous Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Case Scenario: Automating a Data-Mining Solution . . . . . . . . . . . . . . . . . . . . . . . . . 189
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
5 Data Integrity and Error Handling in SQL Server 2005. . . . . . . . . . . . . 193
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Lesson 1: Validating Data and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Implementing Declarative Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Implementing Procedural Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
Using T-SQL to Verify Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Lab: Comparing Procedural and Declarative
Data Validation Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Lesson 2: Detecting and Reacting to Errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Techniques for Detecting and Reacting to Errors . . . . . . . . . . . . . . . . . . . . . . . 218
Creating User-Defined Messages. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Lab: Using Try/Catch Blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Case Scenario: Validating Data Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232
Reference. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232