Professional SQL Server 2005 Programming

913 Pages·2007·17.797 MB·English

by  Robert Vieira

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, [phone], fax [phone]. 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 [phone], 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 [phone], outside the United States at [phone] or fax [phone].
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