Table Of Content

Dewson_5882Front.fm Page i Thursday, January 12, 2006 2:16 PM Beginning SQL Server 2005 for Developers From Novice to Professional ■ ■ ■ Robin Dewson Dewson_5882Front.fm Page ii Thursday, January 12, 2006 2:16 PM Beginning SQL Server 2005 for Developers Copyright © 2006 by Robin Dewson All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-588-6 ISBN-10 (pbk): 1-59059-588-2 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editors: Matthew Moodie, Tony Davis Technical Reviewer: Jasper Smith Additional Material: Cristian Lefter Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Project Manager: Beth Christmas Copy Edit Manager: Nicole LeClerc Copy Editor: Ami Knox Assistant Production Director: Kari Brooks-Copony Production Editor: Kelly Winquist Compositor: Susan Glinert Proofreaders: Lori Bring, Nancy Sixsmith Indexer: Broccoli Information Management Artist: Kinetic Publishing Services, LLC Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com in the Source Code section. Dewson_5882Front.fm Page iii Thursday, January 12, 2006 2:16 PM This book is dedicated to my long-suffering family, especially my wife, Julie, who has had to put up with my stress and my lack of input to helping with the house, tiredness, bad moods, and antisocial lifestyle, and to my three kids: Cameron, a star rugby player who for once is first in the list; Ellen (another star rugby player)—I feel sorry for the man she marries one day; and Scott, the future Air Force pilot, the big foot of the family. Thank you for putting up with everything. Up the Blues. www.fat-belly.com Dewson_5882Front.fm Page iv Thursday, January 12, 2006 2:16 PM Dewson_5882Front.fm Page v Thursday, January 12, 2006 2:16 PM Contents at a Glance About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii ■ CHAPTER 1 SQL Server 2005 Overview and Installation . . . . . . . . . . . . . . . . . . . . . 1 ■ CHAPTER 2 SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 ■ CHAPTER 3 Database Design and Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 ■ CHAPTER 4 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 ■ CHAPTER 5 Defining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 ■ CHAPTER 6 Creating Indexes and Database Diagramming . . . . . . . . . . . . . . . . 153 ■ CHAPTER 7 Database Backups, Recovery, and Maintenance . . . . . . . . . . . . . . 185 ■ CHAPTER 8 Working with the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 ■ CHAPTER 9 Building a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309 ■ CHAPTER 10 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 ■ CHAPTER 11 T-SQL Essentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359 ■ CHAPTER 12 Advanced T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 ■ CHAPTER 13 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427 ■ CHAPTER 14 SQL Server 2005 Reporting Services . . . . . . . . . . . . . . . . . . . . . . . . . 451 ■ APPENDIX Glossary of Terms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473 ■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485 v Dewson_5882Front.fm Page vi Thursday, January 12, 2006 2:16 PM Dewson_5882Front.fm Page vii Thursday, January 12, 2006 2:16 PM CCoonntteennttss About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii ■ CHAPTER 1 SQL Server 2005 Overview and Installation . . . . . . . . . . . . . . . 1 Why SQL Server 2005? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Evolution of SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Hardware Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 CPU. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Memory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Hard Disk Space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Operating System Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 The Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 A Standard Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Preparing to Install . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Services Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Looking at the Authentication Mode. . . . . . . . . . . . . . . . . . . . . . . . . . 18 The sa Login . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 ■ CHAPTER 2 SQL Server Management Studio . . . . . . . . . . . . . . . . . . . . . . . . . . 25 A Quick Overview of SSMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Examining SSMS’s Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Environment Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Source Control Node. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Text Editor Node. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Query Execution Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Query Results Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 vii Dewson_5882Front.fm Page viii Thursday, January 12, 2006 2:16 PM viii ■ CONTENTS Query Editor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Surface Area Configuration Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 ■ CHAPTER 3 Database Design and Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Defining a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Databases Within SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 master . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 tempdb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 msdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 AdventureWorks/AdventureWorksDW . . . . . . . . . . . . . . . . . . . . . . . . 58 Choosing the Database System Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 OLTP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 OLAP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Example System Choice . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Gathering the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Determining the Information to Store in the Database . . . . . . . . . . . . . . . 62 Financial Products . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Customers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Customer Addresses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Shares . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 External and Ignored Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Building Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Using Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Creating Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 More on Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Each Entity Should Have a Unique Identifier . . . . . . . . . . . . . . . . . . . 73 Only Store Information That Directly Relates to That Entity. . . . . . . 73 Avoid Repeating Values or Columns. . . . . . . . . . . . . . . . . . . . . . . . . . 73 Normalization Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Creating the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Creating a Database in SQL Server Management Studio. . . . . . . . . 76 Dropping the Database in SQL Server Management Studio . . . . . . 89 Creating a Database in a Query Pane. . . . . . . . . . . . . . . . . . . . . . . . . 92 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Dewson_5882Front.fm Page ix Thursday, January 12, 2006 2:16 PM ■ CONTENTS ix ■ CHAPTER 4 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 Logins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 Server Logins and Database Users. . . . . . . . . . . . . . . . . . . . . . . . . . 105 Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Fixed Server Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Database Roles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 Application Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 Before You Can Proceed with Your Solution . . . . . . . . . . . . . . . . . . . . . . 113 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 ■ CHAPTER 5 Defining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 What Is a Table? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Defining a Table: SQL Server Management Studio . . . . . . . . . . . . . . . . . 121 Different Table Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Different Program Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Columns Are More Than Simple Data Repositories . . . . . . . . . . . . . . . . 126 Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Generating IDENTITY Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 The Use of NULL Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Why Define a Column to Allow NULL? . . . . . . . . . . . . . . . . . . . . . . . 127 Image and Large Text Storage in SQL Server . . . . . . . . . . . . . . . . . . . . . 127 Creating a Table in SQL Server Management Studio . . . . . . . . . . . . . . . 128 Defining a Table Through the Query Editor . . . . . . . . . . . . . . . . . . . . . . . 134 Defining a Table: Using a Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Creating and Altering a Template . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 The ALTER TABLE Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Defining the Remaining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Setting a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Creating a Relationship . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 Check Existing Data on Creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Enforce Foreign Key Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Delete Rule/Update Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Using the ALTER TABLE SQL Statement . . . . . . . . . . . . . . . . . . . . . . . . . 150 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151