SQL ServerTM 2005 Bible

1325 Pages·2007·21.578 MB·English

by  Paul Nielsen

01_542567 ffirs.qxp 9/27/06 9:26 PM Page iii
SQL Server™ 2005
Bible
Paul Nielsen
01_542567 ffirs.qxp 9/27/06 9:26 PM Page iv
SQL Server™2005 Bible
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN-13: 978-0-7645-4256-5
ISBN-10: 0-7645-4256-7
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
1B/RW/RQ/QW/IN
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means,
electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of
the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through
payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978)
750-8400, fax [phone]. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley
Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, [phone], fax [phone], or online at
http://www.wiley.com/go/permissions.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY:THE PUBLISHER AND THE AUTHOR MAKE NO REPRESENTATIONS
OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS WORK AND
SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR A
PARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS.
THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS
SOLD WITH THE UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING,
OR OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A
COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE
LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN
THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF FURTHER INFORMATION DOES NOT MEAN THAT
THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE
OR RECOMMENDATIONS IT MAY MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED
IN THIS WORK MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT
IS READ.
For general information on our other products and services or to obtain technical support, please contact our Customer
Care Department within the U.S. at [phone], outside the U.S. at [phone] or fax [phone].
Library of Congress Cataloging-in-Publication Data:
Nielsen, Paul.
SQL server 2005 bible / Paul Nielsen.
p. cm.
ISBN-13: 978-0-7645-4256-5 (paper/website)
ISBN-10: 0-7645-4256-7 (paper/website)
1. SQL server. 2. Client/server computing. 3. Database management. I. Title.
QA76.9.C55N55 2006
005.2’768—dc22
2006016151
Trademarks:Wiley and related trade dress are registered trademarks of Wiley Publishing, Inc., in the United States and
other countries, and may not be used without written permission. All other trademarks are the property of their respective
owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in
electronic books.
01_542567 ffirs.qxp 9/27/06 9:26 PM Page vi
Credits
Executive Editor Vice President and Executive
Robert Elliott Publisher
Joseph B. Wikert
Development Editors
Mark Cierzniak Project Coordinator
Ami Frank Sullivan Ryan Steffen
Technical Editors Graphics and Production Specialists
John Mueller Sean Decker
Kevin Lloyd Lauren Goddard
Chris Shaw Brooke Graczyk
Jennifer Mayberry
Production Editor Jill Proll
William A. Barton
Quality Control Technician
Copy Editor John Greenough
Luann Rouff
Media Development Specialists
Editorial Manager Angela Denny
Mary Beth Wakefield Kit Malone
Travis Silvers
Production Manager
Tim Tate Media Development Coordinator
Laura Atkinson
Vice President and Executive Group
Publisher Proofreading and Indexing
Richard Swadley Techbooks
01_542567 ffirs.qxp 9/27/06 9:26 PM Page vii
About the Author
Paul Nielsenis a hands-on database developer, Microsoft SQL Server MVP, author, and
trainer specializing in data architecture and Microsoft SQL Server technologies. Besides hold-
ing several certifications, he was the Design-SME (subject matter expert) for the Microsoft
Official Course 2784: Tuning and Optimizing Queries Using Microsoft SQL Server 2005.
Paul has been developing datacentric solutions since 1982, and was the Enterprise Data
Architect for Compassion International, a SQL Server instructor with Learning Tree, the
technical editor for a database magazine, and a U. S. Navy Submariner (Data Systems Tech).
He serves on the PASS (Professional Association for SQL Server) Board of Directors, is active
in the Colorado area SQL Server user groups, and organizes the Colorado PASS Camp. For
recreation, Paul enjoys scuba diving, playing guitar, and hiking/biking in the Front Range
mountains of Colorado. Paul regularly offers public and private workshops in SQL Server
development and data architecture, including the popular Advanced Design and Optimization
course. For more information visit his website at www.SQLServerBible.com.
About the Co-Authors and Contributors
Hilary Cotter, Microsoft SQL Server MVP and author of Chapter 13, “Using Full-Text Search,”
and Chapter 39, “Replicating Data,” is director of Text Mining and Database Strategy for
RelevantNoise, which indexes blogs for business intelligence. He received a degree in
mechanical engineering from the University of Toronto, and subsequently studied economics
at the University of Calgary and computer science at the University of California (Berkeley).
He and his beautiful wife, Miriam, have five children ranging in age from 13 months to six
years. Hilary Cotter has a companion volume to this text on merge replication, which is in
the works for 2005.
Hilary has worked for Microsoft, Merrill Lynch, UBS-Paine Webber, MetLife, VoiceStream,
Tiffany & Co., Pacific Bell, Cahners, Novartis, Petro-Canada, Associated Press, and Johnson
and Johnson. You can find Hilary on the Microsoft newsgroups or contact him at hilary.
[email protected].
Monte Holyfield, author of Chapter 46, “Authoring Reports with Reporting Services,” and
Chapter 47, “Administering Reports with Reporting Services,” is the founder and manager of
MJH Software Solutions LLC (www.mjhsoftware.com). He has over 10 years of experience
developing custom software and has served in many capacities including solution architect,
principal consultant, mentor, and project lead. He is a Microsoft Certified Professional and
has successfully leveraged the Microsoft.NET technologies to deliver outstanding solutions to
a diverse group of clients. Monte offers a unique combination of technical, analytical, and
leadership skills and shares a contagious “can do” attitude. When Monte is not building soft-
ware, he can most likely be found with his family doing something active such as riding dirt
bikes, skiing, windsurfing, or just enjoying the incredible Colorado outdoors.
Kevin Lloyd, Technical Editor, is an experienced SQL Server Developer and Data Architect,
Microsoft MCP, consultant and primary member of Data Solutions, LLC. He specializes in
data architecture and application integration, data modeling, data quality, and performance
tuning. Besides working with SQL Server, he develops C# XML Web Services for SQL Mobile
01_542567 ffirs.qxp 9/27/06 9:26 PM Page viii
viii
About the Co-Authors and Contributors
replication. He also enjoys mentoring others in SQL and writing Windows Mobile applica-
tions. Kevin has been developing data-centric solutions since 1996 in finance, e-commerce,
and government, and has worked or consulted with organizations such as Microsoft
Corporation; Frontier Airlines, Inc.; Compassion International, Inc.; and Keane, Inc. He can be
reached at [email protected]. When not writing code, he enjoys woodworking, spending
time with his family, and traveling.
George Mudrak,author of chapter 4, “Installing SQL Server 2005,” Chapter 5, “Client Software
Connectivity,” and Chapter 54, “Designing High-Performance Data Access Providers,” works
as an Enterprise Data Architect for Compassion International in Colorado Springs, CO. He has
been doing full life-cycle programming in a variety of languages for over 12 years, with his
most recent development activities using Microsoft’s .NET technologies. He is currently work-
ing within the Enterprise Information Architecture and Management (EIM/EIA) realm and has
worked at start-ups as well as major corporations. When he’s not working, George enjoys
spending time with his family, working out, and pursuing hobbies.
John Mueller, primary technical reviewer and co-author of Chapter 34, “Configuring SQL
Server,” Chapter 35, “Transferring Databases,” Chapter 37, “Maintaining the Database,”
Chapter 38, “Automating Database Maintenance with SQL Server Agent,” and Chapter 41,
“Administering SQL Server Express,” is a freelance author and technical editor. He has writing
in his blood, having produced 69 books and over 300 articles to date, with the topics that
range from networking to artificial intelligence and from database management to heads-
down programming. Some of his current books include a Windows power optimization book,
a book on .NET security, and books on Amazon Web Services, Google Web Services, and eBay
Web Services. He’s currently editor of the .NET electronic newsletter for Pinnacle Publishing.
See www.freeenewsletters.comfor more information. When John isn’t working at the com-
puter, he is likely in his workshop. An avid candle maker and woodworker, he can be found
working at a lathe or putting the finishing touches on a bookcase. His website is
www.mwt.net/~jmueller.
Darren Shaffer, Microsoft Compact Framework MVP, author of Chapter 26, “Developing for
SQL Server Mobile,” is the Chief Architect for .NET Solutions at Connected Innovation LLC
(www.connectedinnovation.com). Darren is responsible for the design and development of
over 25 Compact Framework solutions for Fortune 1000 clients in the past four years. Darren
is a frequent speaker at Global Microsoft Events such as MEDC and TechEd. as well as a con-
tributor to MSDN, emphasizing the importance of the .NET Compact Framework,
SQL CE/SQL Mobile, and teaching mobile development best practices. Darren moderates
the MSDN SQL Mobile forum, and authors a well-traveled blog for .NET junkies at www.
dotnetjunkies.com/WebLog/darrenshaffer/default.aspx. A West Point graduate and
retired Army Telecommunications Officer, Darren now lives in Colorado with his wife and
daughter.
Chris Shaw, technical editor and author of chapter 52, “Providing High Availability” is President
of SQL on Call in Colorado Springs, Colorado, for the past 5 years Chris has been working at
Premiere Global Services. He has also worked with Yellow Pages Inc, Ford Fairlane Motor
Credit, AdminiQuest, Wells Fargo and AT&T Wireless. Chris has published a number of Articles
for a number of magazines including SQL Server Magazine and SQL Standard Magazine.
He is a member of Rocky Mountain Microsoft Community Insiders group, is the president of the
Colorado Springs SQL Server Users Group, and is one of the organizers of the Colorado PASS
Camp. He has also been a guest speaker to a number of other user groups, is a regular speaker
at PASS Summits and SQL Connection, and presents web casts with SSWUG; recent presenta-
tions include Replication SQL Server 2005, Performance Metrics, and Crisis Management.
01_542567 ffirs.qxp 9/27/06 9:26 PM Page ix
About the Co-Authors and Contributors ix
Andrew Watt, Microsoft SQL Server MVP, wrote Chapter 33, “InfoPath and SQL Server 2005.”
Andrew Watt is an independent consultant and experienced computer book author. He has
been a Microsoft MVP since 2004.
Michael Whitehas focused on database development and administration since 1992.
Concentrating on Microsoft’s Business Intelligence (BI) tools and applications since 2000, he
has architected and implemented large warehousing and Analysis Services applications, as
well as nontraditional applications of BI tools. After many years in corporate IT and consult-
ing, Mike currently works as a software engineer for Wall Street On Demand. He is a strong
advocate for the underused BI toolset; currently leads the Boulder (Colorado) SQL Server
User Group and is one of the organizers of the Colorado PASS Camp. Mike wrote Chapter 42,
“ETL with Integration Services,” Chapter 43, “Business Intelligence with Analysis Services,”
Chapter 44, “Data Mining with Analysis Services,” Chapter 45, “Programming MDX Queries,”
and Chapter 48, “Analyzing Data with Excel and Data Analyzer.”
Bill Wunder, Microsoft SQL Server MVP, wrote Chapter 27, “Programming CLR Assemblies
within SQL Server,” Chapter 29, “Persisting Custom Data Types,” and Chapter 30,
“Programming with ADO.NET 2.0.” Bill’s introduction to set based technology began when he
was an AS/400 developer in 1993. The query processor at the time was built by an Australian
company, LANSA. The 4GL language was called Relational Database Markup Language (RDML).
It was an innovative product written in RPG that included commands like SELECT, INSERT,
UPDATE and DELETE. Shortly after that introduction he became the DBA for the company’s
sales force automation application. The database had just been upgraded to SQL Server 4.2.1
running on the Novell OS. He has been working with SQL Server since that time. He began
sharing his scripts on the Internet in 1995. He has published over 100 SQL Server related arti-
cles at various web sites. He has been a speaker at swynk.com’s Back Office Administrators
Conferences and Professional Association for SQL Server (PASS) conferences in the US and
Europe. He founded and has been a frequent speaker at the Boulder SQL Server Users Group.
He received Microsoft’s MVP award for his contributions to the SQL Server community in 2004,
2005 and 2006. Today Bill is a working DBA on the Dutch Antilles island of Curaçao. Readers
are invited to contact him at [email protected] with any questions or comments.
02_542567 ftoc.qxp 9/27/06 9:29 PM Page x
Contents at a Glance
Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii
Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli
Part I:Laying the Foundation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 1: The Information Architecture Principle . . . . . . . . . . . . . . . . . . . . . . . . 3
Chapter 2: Relational Database Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Chapter 3: Exploring SQL Server 2005 Architecture . . . . . . . . . . . . . . . . . . . . . . . 47
Chapter 4: Installing SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Chapter 5: Client Software Connectivity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Chapter 6: Using Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Part II: Manipulating Data with Select . . . . . . . . . . . . . . . . . . . . . . 115
Chapter 7: Understanding Basic Query Flow. . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Chapter 8: Using Expressions and Scalar Functions. . . . . . . . . . . . . . . . . . . . . . . 141
Chapter 9: Merging Data with Joins and Unions. . . . . . . . . . . . . . . . . . . . . . . . . 171
Chapter 10:Including Data with Subqueries and CTEs . . . . . . . . . . . . . . . . . . . . . 199
Chapter 11:Aggregating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
Chapter 12:Navigating Hierarchical Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243
Chapter 13:Using Full-Text Search . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Chapter 14: Creating Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279
Chapter 15:Working with Distributed Queries. . . . . . . . . . . . . . . . . . . . . . . . . . 295
Chapter 16:Modifying Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Part III:Developing with SQL Server . . . . . . . . . . . . . . . . . . . . . . . 353
Chapter 17:Implementing the Physical Database Schema. . . . . . . . . . . . . . . . . . . 355
Chapter 18:Programming with Transact-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 397
Chapter 19:Performing Bulk Operations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Chapter 20:Kill the Cursor!. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
Chapter 21:Developing Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
Chapter 22:Building User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Chapter 23:Implementing Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Chapter 24:Exploring Advanced T-SQL Solutions . . . . . . . . . . . . . . . . . . . . . . . 499
Chapter 25:Creating Extensibility with a Data Abstraction Layer . . . . . . . . . . . . . . 539
Chapter 26:Developing for SQL Server Everywhere . . . . . . . . . . . . . . . . . . . . . . 549
Chapter 27:Programming CLR Assemblies within SQL Server. . . . . . . . . . . . . . . . . 589
02_542567 ftoc.qxp 9/27/06 9:29 PM Page xi
Chapter 28:Queueing Data with Service Broker. . . . . . . . . . . . . . . . . . . . . . . . . 617
Chapter 29:Persisting Custom Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . 623
Chapter 30:Programming with ADO.NET 2.0. . . . . . . . . . . . . . . . . . . . . . . . . . . 643
Chapter 31:Using XML, XPath, and XQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . 677
Chapter 32:Building an SOA Data Store with Web Services . . . . . . . . . . . . . . . . . . 689
Chapter 33:InfoPath and SQL Server 2005 . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695
PARTIV:Enterprise Data Management. . . . . . . . . . . . . . . . . . . . . . 703
Chapter 34:Configuring SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 705
Chapter 35:Transferring Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 741
Chapter 36:Recovery Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 751
Chapter 37:Maintaining the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775
Chapter 38:Automating Database Maintenance with SQL Server Agent . . . . . . . . . . . 795
Chapter 39:Replicating Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 813
Chapter 40:Securing Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 857
Chapter 41:Administering SQL Server Express . . . . . . . . . . . . . . . . . . . . . . . . . 889
Part V:Business Intelligence. . . . . . . . . . . . . . . . . . . . . . . . . . . . 897
Chapter 42:ETL with Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . 899
Chapter 43:Business Intelligence with Analysis Services . . . . . . . . . . . . . . . . . . . 935
Chapter 44:Data Mining with Analysis Services. . . . . . . . . . . . . . . . . . . . . . . . . 973
Chapter 45:Programming MDX Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 989
Chapter 46:Authoring Reports with Reporting Services . . . . . . . . . . . . . . . . . . . 1005
Chapter 47:Administering Reports with Reporting Services. . . . . . . . . . . . . . . . . 1035
Chapter 48:Analyzing Data with Excel and Data Analyzer . . . . . . . . . . . . . . . . . . 1051
Part VI:Optimization Strategies . . . . . . . . . . . . . . . . . . . . . . . . . 1063
Chapter 49:Measuring Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1065
Chapter 50:Query Analysis and Index Tuning . . . . . . . . . . . . . . . . . . . . . . . . . 1081
Chapter 51:Managing Transactions, Locking, and Blocking . . . . . . . . . . . . . . . . . 1105
Chapter 52:Providing High Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1141
Chapter 53:Scaling Very Large Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . 1153
Chapter 54:Designing High-Performance Data Access Providers . . . . . . . . . . . . . . 1173
Part VII:Appendixes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1197
Appendix A:SQL Server 2005 Specifications . . . . . . . . . . . . . . . . . . . . . . . . . . 1199
Appendix B:Sample Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1205
Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1213
02_542567 ftoc.qxp 9/27/06 9:29 PM Page xiii
Contents
Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli
Part I: Laying the Foundation 1
Chapter 1:The Information Architecture Principle . . . . . . . . . . . . . . . . 3
Simplicity vs. Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Complexity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Simplicity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
The Usability Objective. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Suitability of Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Data Store Configurations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Master Data Stores Design Styles . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Data Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Entity Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Domain Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
User-Defined Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Transactional Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Transactional Faults. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Isolation Levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
The Value of Null . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Set-Based Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Redundancy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Extensibility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Abstraction Layer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Generalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Security. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Restricted Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Information Ownership . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Audit Trails . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Optimization Theory and SQL Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Schema Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
02_542567 ftoc.qxp 9/27/06 9:29 PM Page xiv
xiv
Contents
Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Advanced Scalability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Chapter 2:Relational Database Modeling. . . . . . . . . . . . . . . . . . . . . 25
Modeling Reality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Visible Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Every Tuple (Row) Is an Island. . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Tables, Rows, Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Identifying Multiple Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Multiple Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Relationships Between Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Organizing or Grouping Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Consistent Lookup Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Complex Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Relational Patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Secondary Entities and Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . 31
Relationship Cardinality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Relationship Optionality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Data-Model Diagramming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
One-to-Many Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
One-to-One Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Supertype/Subtype Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Many-to-Many Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Category Entities. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Reflexive Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Entity/Attribute Design Principles. . . . . . . . . . . . . . . . . . . . . . . . . . 39
Normal Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Simplicity and Normalization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
First Normal Form (1NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
The Second Normal Form (2NF) . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
The Third Normal Form (3NF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
The Boyce-Codd Normal Form (BCNF) . . . . . . . . . . . . . . . . . . . . . . . 44
The Fourth Normal Form (4NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
The Fifth Normal Form (5NF). . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Relational Algebra. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Chapter 3:Exploring SQL Server 2005 Architecture . . . . . . . . . . . . . . . 47
Data Access Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
The Client/Server Database Model. . . . . . . . . . . . . . . . . . . . . . . . . . 49
N-Tier Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Service-Oriented Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
SQL Server Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Relational Engine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Transact-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Visual Studio and the CLR. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Replication Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55