01_584340 ffirs.qxp 10/18/06 2:10 PM Page i Professional SQL Server™ 2005 Programming Robert Vieira 01_584340 ffirs.qxp 10/18/06 2:10 PM Page ii Professional SQL Server™ 2005 Programming Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-7645-8434-3 ISBN-10: 0-7645-8434-0 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 1B/QW/RR/QW/IN Library of Congress Cataloging-in-Publication Data: Vieira, Robert. Professional SQLserver 2005 programming / Robert Vieira. p. cm. ISBN-13: 978-0-7645-8434-3 (paper/website) ISBN-10: 0-7645-8434-0 (paper/website) 1. Client/server computing. 2. SQLserver. I. Title. QA76.9.C55V55 2006 005.2’768—dc22 2006023047 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, MA01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www.wiley.com/go/permissions. LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY:THE PUBLISHER AND THE AUTHOR MAKE NO REP- RESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACYOR COMPLETENESS OF THE CON- TENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITHOUT LIMITATION WARRANTIES OF FITNESS FOR APARTICULAR PURPOSE. NO WARRANTYMAYBE CREATED OR EXTENDED BYSALES OR PROMOTIONALMATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAYNOT BE SUITABLE FOR EVERYSITUATION. THIS WORK IS SOLD WITH THE UNDERSTAND- ING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR OTHER PROFES- SIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF A COMPETENT PROFESSIONALPERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE AUTHOR SHALLBE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZATION OR WEBSITE IS REFERRED TO IN THIS WORK AS ACITATION AND/OR APOTENTIALSOURCE OF FURTHER INFORMA- TION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE INFORMATION THE ORGANIZATION OR WEBSITE MAYPROVIDE OR RECOMMENDATIONS IT MAYMAKE. FURTHER, READ- ERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK MAYHAVE CHANGED OR DIS- APPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT IS READ. For general information on our other products and services please contact our Customer Care Department within the United States at (800) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002. Trademarks:Wiley, the Wiley logo, Wrox, the Wrox logo, Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. SQLServer is a trademark of Microsoft Corporation in the United States and/or other countries. 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_584340 ffirs.qxp 10/18/06 2:10 PM Page iii Credits Executive Editor Vice President and Executive Group Publisher Bob Elliott Richard Swadley Senior Development Editor Vice President and Executive Publisher Kevin Kent Joseph B. Wikert Technical Editor Graphics and Production Specialists John Mueller Denny Hager, Jennifer Mayberry, Barbara Moore, Lynsey Osborn, Heather Ryan, Alicia B. South, Production Editor Ronald Terry Pamela Hanley Quality Control Technician Copy Editor John Greenough Foxxe Editorial Services Project Coordinators Editorial Manager Michael Kruzil, Kristie Rees Mary Beth Wakefield Proofreading and Indexing Production Manager Techbooks Tim Tate About the Author Experiencing his first infection with computing fever in 1978, Robert Vieiraknew right away that this was something “really cool.” In 1980 he began immersing himself into the computing world more fully—splitting his time between building and repairing computer kits and programming in BASIC as well as Z80 and 6502 Assembly. In 1983, he began studies for a degree in Computer Information Systems, but found the professional mainframe environment too rigid for his tastes and dropped out in 1985 to pursue other interests. Later that year, he caught the “PC bug” and began the long road of programming in database languages from dBase to SQLServer. Rob completed a degree in business administration in 1990 and since has typically worked in roles that allow him to combine his unique knowledge of business and computing. Beyond his bachelor’s degree, he has been certified as a Certified Management Accountant; Microsoft Certified as a Solutions Developer (MCSD), Trainer (MCT), and Database Administrator (MCDBA); and even had a brief stint certified as an emergency medical technician (EMT). Rob is currently self-employed as a high-level consultant specializing in architectural analysis, long-term planning, and product viability analysis and is recognized internationally for his expertise in database architecture and management. He has published four books on SQLServer development. He resides with his daughters Ashley and Adrianna in Vancouver, Washington. 01_584340 ffirs.qxp 10/18/06 2:10 PM Page iv This book is dedicated with all my heart to my children Ashleyand Addy, who put up with me “disappearing” into my home office during the several months that I worked on this book. They provide the energy that powers my writing, and I love them to no end. I only wish Wrox would let me print a picture of the two women in my life on the cover of this book rather than my ugly mug. 01_584340 ffirs.qxp 10/18/06 2:10 PM Page v Acknowledgments Nearly six years have gone by since I finished my first book on SQLServer, and my how life has changed. It’s been only a few months since I completed the new Beginning SQLServer 2005 Programming title that is something of a companion to this book, yet there remain quite a few people to thank in that time (and before for that matter). I’ll start with my kids, who somehow continue to be just wonderful even in the face of dad stressing out over this and that. It’s nice to be able to tell my youngest that I’m finally “done with that book” that she has asked me the “when?” question about for over a year now (I’m not sure she has completely gotten the idea that there have been two this time around). She has been tremendously patient with me all dur- ing the development of this as well as the Beginningbook. I think I’ll miss that occasional time that she would come in and just sit and watch me write just to be with me. Having just watched my eldest grad- uate high school, I wonder where the years have gone. The “thank you’s” definitely need to begin with those two. You—the readers. You’ve written me mail and told me how I helped you out in some way. That was and continues to be the number one reason I find to strength to write another book. The continued support of my Professionalseries titles has been amazing. We struck a chord—I’m glad. Here’s to hoping we help make your SQLServer experience a little less frustrating and a lot more successful. I also want to pay special thanks to several people past and present. Some of these are at the old Wrox Press and have long since fallen out of contact, but they remain so much of who I am as I writer that I need to continue to remember them. Others are new players for me, but have added their own stamp to the mix—sometimes just by showing a little patience: Kate Hall—Who, although she was probably ready to kill me by the end of each of my first two books, somehow guided me through the editing process to build a better book each time. I have long since fallen out of touch with Kate, but she will always be the most special to me as someone who really helped shape my writing career. I will likely always hold this first “professional” dedication spot for her. Wherever you are Kate, I hope you are doing splendidly. Adaobi Obi Tulton—Who has had enough of her own stresses this year, so my apologies go out to her for all the stress I’ve placed in her regarding delivery schedules. If I ever make it rich, I may hire Adaobi as my spiritual guide. While she can be high stress about deadlines, she has a way of displaying a kind of “peace” in just about everything else I’ve seen her do—I need to learn that. Bob Elliott—Mostly just a “thanks for hanging in there”—he’ll understand what I mean. Kevin Kent—Who had to pick up in the middle and shepherd things along. Dominic Shakeshaft—Who got me writing in the first place (then again, given some nights filled with writing instead of sleep lately, maybe it’s not thanks I owe him). 01_584340 ffirs.qxp 10/18/06 2:10 PM Page vi Acknowledgments Catherine Alexander—Who played Kate’s more than able-bodied sidekick for my first title and was cen- tral to round two. Catherine was much like Kate in the sense she had a significant influence on the shape and success of my first two titles. John Mueller—Who had the dubious job of finding my mistakes. I’ve done tech editing myself, and it’s not the easiest job to notice the little details that were missed or are, in some fashion, wrong. It’s even harder to read someone else’s writing style and pick the right times to say, “You might want to approach this differently” and the right times to let it be. John did a terrific job on both counts. There are not quite as many other players in this title as there have been in my previous titles, but this book has been in development for so long and touched enough people that I’m sure I’ll miss one or two— if you’re among those missed, please accept my humblest apologies and my assurance that your help was appreciated. That said, people who deserve some additional thanks (some of these go to influences from WAYback) include Paul Turley, Greg Beamer, Itzik Ben-Gan, Kalen Delaney, Fernando Guerrero, Gert Drapers,and especially Richard Waymire. vi 02_584340 ftoc.qxp 10/18/06 2:10 PM Page vii Contents Acknowledgments v Introduction xxiii Chapter 1: Being Objective: Re-Examining Objects in SQL Server 1 So,What Exactly Do We Have Here? 1 An Overview of Database Objects 2 The Database Object 2 The Transaction Log 5 The Most Basic Database Object: Table 5 Schemas 6 Filegroups 7 Diagrams 7 Views 7 Stored Procedures 9 User-Defined Functions 9 Users and Roles 10 Rules 10 Defaults 10 User-Defined Data Types 10 Full-Text Catalogs 11 SQL Server Data Types 11 NULL Data 14 SQL Server Identifiers for Objects 15 What Gets Named? 15 Rules for Naming 15 Summary 16 Chapter 2: Tool Time 17 Books Online 18 The SQL Server Configuration Manager 19 Service Management 19 Network Configuration 20 The Protocols 21 On to the Client 23 02_584340 ftoc.qxp 10/18/06 2:10 PM Page viii Contents The SQL Server Management Studio 24 Getting Started 25 Query Window 27 SQL Server Business Intelligence Development Studio 32 SQL Server Integration Services (SSIS) 32 Reporting Services 33 Bulk Copy Program (bcp) 33 SQL Server Profiler 33 sqlcmd 34 Summary 34 Chapter 3: Basic T-SQL 35 The Basic SELECT Statement 36 The SELECT Statement and FROM Clause 36 The JOIN Clause 38 The WHERE Clause 45 ORDER BY 49 Aggregating Data Using the GROUP BY Clause 52 Placing Conditions on Groups with the HAVING Clause 56 Outputting XML Using the FOR XML Clause 57 Making Use of Hints Using the OPTION Clause 57 DISTINCT 57 Adding Data with the INSERT Statement 59 The INSERT INTO . . . SELECT Statement 61 Changing What You’ve Got with the UPDATE Statement 62 The DELETE Statement 64 Exploring Alternative Syntax for Joins 66 An Alternative INNER JOIN 67 An Alternative OUTER JOIN 67 An Alternative CROSS JOIN 68 The UNION 69 Summary 72 Chapter 4: Creating and Altering Tables 73 Object Names in SQL Server 73 Schema Name (a.k.a. Ownership) 74 The Database Name 76 Naming by Server 76 The CREATE Statement 77 CREATE DATABASE 77 CREATE TABLE 82 viii 02_584340 ftoc.qxp 10/18/06 2:10 PM Page ix Contents The ALTER Statement 89 ALTER DATABASE 89 ALTER TABLE 92 The DROP Statement 95 Using the GUI Tool 96 Creating or Editing the Database 96 Creating and Editing Tables 97 Summary 99 Chapter 5: Reviewing Keys and Constraints 101 Types of Constraints 102 Domain Constraints 103 Entity Constraints 103 Referential Integrity Constraints 104 Constraint Naming 104 Key Constraints 105 PRIMARY KEY Constraints 106 FOREIGN KEY Constraints 108 UNIQUE Constraints 117 CHECK Constraints 118 DEFAULT Constraints 119 Defining a DEFAULT Constraint in Your CREATE TABLE Statement 120 Adding a DEFAULT Constraint to an Existing Table 121 Disabling Constraints 121 Ignoring Bad Data When You Create the Constraint 122 Temporarily Disabling an Existing Constraint 124 Rules and Defaults: Cousins of Constraints 126 Rules 126 Defaults 128 Determining Which Tables and Data Types Use a Given Rule or Default 129 Triggers for Data Integrity 129 Choosing What to Use 129 Summary 131 Chapter 6: Asking a Better Question: Advanced Queries 133 What Is a Subquery? 134 Building a Nested Subquery 135 Nested Queries Using Single Value SELECT Statements 136 Nested Queries Using Subqueries That Return Multiple Values 137 The ANY,SOME,and ALL Operators 138 ix