Tài liệu Beginning SQL Server 2005 Programming doc

Beginning

SQL Server™ 2005 Programming

Robert Vieira

01_584332 ffirs.qxp 1/17/06 6:49 PM Page iii

01_584332 ffirs.qxp 1/17/06 6:49 PM Page ii

Beginning

SQL Server™ 2005 Programming

01_584332 ffirs.qxp 1/17/06 6:49 PM Page i

01_584332 ffirs.qxp 1/17/06 6:49 PM Page ii

Beginning

SQL Server™ 2005 Programming

Robert Vieira

01_584332 ffirs.qxp 1/17/06 6:49 PM Page iii

Beginning SQL Server™ 2005 Programming

Published by

Wiley Publishing, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN-13: 978-0-7645-8433-6

ISBN-10: 0-7645-8433-2

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

1MA/QT/QS/QW/IN

Library of Congress Cataloging-in-Publication Data: Available from publisher

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 Sec￾tions 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Pub￾lisher, 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 (978) 646-8600. Requests to the Publisher for permis￾sion 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

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 HERE￾FROM. 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 DISAP￾PEARED 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. SQL Server is a trademark of Microsoft

Corporation in the United States and/or other countries. All other trademarks are the property of their respec￾tive 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_584332 ffirs.qxp 1/17/06 6:49 PM Page iv

Credits

Executive Editor

Robert Elliott

Development Editor

Adaobi Obi Tulton

Technical Editor

John Mueller

Production Editor

Pamela Hanley

Copy Editor

Nancy Rapoport

Editorial Manager

Mary Beth Wakefield

Production Manager

Tim Tate

Vice President and Executive Group Publisher

Richard Swadley

Vice President and Executive Publisher

Joseph B. Wikert

Project Coordinator

Kristie Rees

Quality Control Technician

Laura Albert

Jessica Kramer

Graphics and Production Specialists

Carrie A. Foster

Lauren Goddard

Denny Hager

Stephanie D. Jumper

Barbara Moore

Alicia South

Proofreading and Indexing

TECHBOOKS Production Services

01_584332 ffirs.qxp 1/17/06 6:49 PM Page v

This book is dedicated with all my heart to my children Ashley and 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 Wiley

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_584332 ffirs.qxp 1/17/06 6:49 PM Page vi

About the Author

Experiencing his first infection with computing fever in 1978, Robert Vieira knew right away that this

was something “really cool.” In 1980 he began immersing himself into the computing world more fully —

splitting 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 SQL Server. Rob completed a degree in Business Administration in

1990, and, since has typically worked in roles that allow him to combine his knowledge of business and

computing. Beyond his Bachelor’s degree, he has been certified as a Certified Management Accountant

as well as Microsoft Certified as a Solutions Developer (MCSD), Trainer (MCT), and Database

Administrator (MCDBA).

Rob is currently a Software Architect for WebTrends Corporation in Portland, Oregon.

He resides with his daughters Ashley and Adrianna in Vancouver, WA.

01_584332 ffirs.qxp 1/17/06 6:49 PM Page vii

01_584332 ffirs.qxp 1/17/06 6:49 PM Page viii

Acknowledgments

Five years have gone by, and my how life has changed since the last time I wrote a title on SQL Server. So

many people have affected my life in so many ways, and, as always, there are a ton of people to thank.

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. Even as my youngest has asked me several times when I’m “going to be done with

that book” (she is not pleased with how it takes up some of my play time), she has been tremendously

patient with me all during the development of this book. My eldest just continues to amaze me in her

maturity and her sensitivity to what doing a book like this requires (and, of course, what it means to her

college education!). The thank yous 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 sup￾port of my Professional series titles has been amazing. We struck a chord — I’m glad. Here’s to hoping

we help make your SQL Server 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 edit 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 you —

wherever you are Kate, I hope you are doing splendidly.

Adaobi Obi Tulton — Who has had to put up with yet another trialing year in my life and what that has

sometimes meant to 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.

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...).

Catherine Alexander — who played Kate’s more than able-bodied sidekick for my first title, and was

central 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 know when to let it be — John did a terrific job on both counts.

01_584332 ffirs.qxp 1/17/06 6:49 PM Page ix

x

Acknowledgments

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

WAY back) include Paul Turley, Greg Beamer, Itzik Ben-Gan, Kalen Delaney, Fernando Guerrero, Gert

Drapers and Richard Waymire.

01_584332 ffirs.qxp 1/17/06 6:49 PM Page x

Contents

Acknowledgments ix

Introduction xxi

Chapter 1: RDBMS Basics: What Makes Up a SQL Server Database? 1

An Overview of Database Objects 2

The Database Object 2

The Transaction Log 6

The Most Basic Database Object: Table 6

Filegroups 8

Diagrams 8

Views 9

Stored Procedures 10

User-Defined Functions 10

Users and Roles 11

Rules 11

Defaults 11

User-Defined Data Types 11

Full-Text Catalogs 12

SQL Server Data Types 12

NULL Data 17

SQL Server Identifiers for Objects 17

What Gets Named? 17

Rules for Naming 18

Summary 18

Chapter 2: Tools of the Trade 19

Books Online 20

The SQL Server Configuration Manager 21

Service Management 22

Network Configuration 22

The Protocols 23

On to the Client 26

02_584332 ftoc.qxp 1/17/06 6:52 PM Page xi

xii

Contents

The SQL Server Management Studio 28

Getting Started 28

Query Window 33

SQL Server Integration Services (SSIS) 38

Bulk Copy Program (bcp) 39

SQL Server Profiler 40

sqlcmd 40

Summary 40

Chapter 3: The Foundation Statements of T-SQL 41

Getting Started with a Basic SELECT Statement 42

The SELECT Statement and FROM Clause 42

The WHERE Clause 45

ORDER BY 49

Aggregating Data Using the GROUP BY Clause 52

Placing Conditions on Groups with the HAVING Clause 61

Outputting XML Using the FOR XML Clause 63

Making Use of Hints Using the OPTION Clause 63

The DISTINCT and ALL Predicates 64

Adding Data with the INSERT Statement 66

The INSERT INTO . . . SELECT Statement 70

Changing What You’ve Got with the UPDATE Statement 72

The DELETE Statement 75

Summary 77

Exercises 77

Chapter 4: JOINs 79

JOINs 79

INNER JOINs 81

How an INNER JOIN Is Like a WHERE Clause 85

OUTER JOINs 89

The Simple OUTER JOIN 90

Dealing with More Complex OUTER JOINs 95

Seeing Both Sides with FULL JOINs 99

CROSS JOINs 100

Exploring Alternative Syntax for Joins 102

An Alternative INNER JOIN 102

An Alternative OUTER JOIN 103

An Alternative CROSS JOIN 104

02_584332 ftoc.qxp 1/17/06 6:52 PM Page xii