Thư viện tri thức trực tuyến
Kho tài liệu với 50,000+ tài liệu học thuật
© 2023 Siêu thị PDF - Kho tài liệu học thuật hàng đầu Việt Nam
beginning microsoft sql server 2008 programming
Nội dung xem thử
Mô tả chi tiết
Beginning
Microsoft®
SQL Server®
2008
Programming
www.wrox.com
$39.99 USA
$43.99 CAN
Wrox Beginning guides are crafted to make learning programming languages and technologies easier than you think, providing
a structured, tutorial format that will guide you through all the techniques involved.
Recommended
Computer Book
Categories
Database Management
General
ISBN: 978-0-470-25701-2
Starting simply and gradually progressing to increasingly advanced topics,
this introduction to the SQL Server database focuses on what is distinctive
about the SQL environment—SQL Server 2008, in particular—versus other
programming environments. Author Robert Vieira draws on his experience as
one of the leading authorities on Microsoft SQL Server and uses his unique
tutorial approach to explain the significant changes to the fundamental core
components of SQL Server 2008.
You’ll begin with an overview of database design concepts and learn how to
implement these fundamental concepts with Microsoft SQL Server 2008. Then,
you’ll take a look at the role of an RDBMS (relational database management
system) and where it fits in the grand scheme of system development.
Thorough coverage of the key additions and changes to the 2008 version of
SQL Server include discussions on DATE and TIME datatypes, hierarchyID
datatypes, MERGE and multiple inserts, recursive queries, and more. With this
book, you will conquer the many changes and challenges of Microsoft SQL
Server 2008.
What you will learn from this book
● How RDBMSs store, manage, and retrieve data
● Ways to create and alter tables
● Various “forms” of database normalization
● Techniques for writing scripts and working with stored procedures
● The positives and negatives of indexes
● Myriad consequences that locks and deadlocks have on system performance
● An understanding of triggers and how they are used
Who this book is for
This book is for developers who are looking for a complete introduction
to database design concepts and learning SQL. A basic understanding of
development fundamentals is helpful.
Enhance Your Knowledge
Advance Your Career
Microsof
t
® SQL Server
® 2008
Programming
Vieira
Beginning
subtitle
spine=1.392"
Updates, source code, and Wrox technical support at www.wrox.com
Beginning
Microsoft®
SQL Server®
2008
Programming
Robert Vieira
Wrox Programmer to Programmer Wrox Programmer to Programmer TM TM
Beginning
Microsoft®
SQL Server®
2008
Programming
Enhance Your Knowledge
Advance Your Career
Professional Microsoft SQL Server 2008
Integration Services
978-0-470-24795-2
This book shows developers how to master the 2008 release of SSIS,
covering topics including data warehousing with SSIS, new methods of
managing the SSIS platform, and improved techniques for ETL operations.
Professional SQL Server 2008 Reporting Services
978-0-470-24201-8
This book teaches solutions architects, designers, and developers how
to use Microsoft’s reporting platform to create reporting and business
intelligence solutions.
Professional Microsoft SQL Server 2008
Analysis Services
978-0-470-24798-3
Professional Microsoft SQL Server 2008 Analysis Services shows readers
how to build data warehouses and multidimensional databases, query
databases, and use Analysis Services and other components of SQL
Server to provide end-to-end solutions.
Professional Microsoft SQL Server 2008 Programming
978-0-470-25702-9
This updated new edition of Wrox’s best-selling SQL Server book has been
expanded to include coverage of SQL Server 2008’s new datatypes, new
indexing structures, manageability features, and advanced time-zone
handling.
Professional Microsoft SQL Server 2008 Administration
978-0-470-24796-9
A how-to guide for experienced database administrators, this book is
loaded with unique tips, tricks, and workarounds for handling the most difficult SQL Server administration issues. The authors
discuss data capture, performance studio, Query Governor, and new techniques for monitoring and policy management.
Beginning T-SQL with Microsoft SQL Server 2005 and 2008
978-0-470-25703-6
Beginning T-SQL with Microsoft SQL Server 2005 and 2008 provides a comprehensive introduction to the T-SQL programming
language, with concrete examples showing how T-SQL works with both SQL Server 2005 and SQL Server 2008.
Beginning Database Design Solutions
978-0-470-38549-4
Beginning Database Design Solutions introduces IT professionals—both DBAs and database developers—to database design.
It explains what databases are, their goals, and why proper design is necessary to achieve those goals. It tells how to decide
what should be in a database to meet the application’s requirements. It tells how to structure the database so it gives good
performance while minimizing the chance for error.
Get more out of
WROX.com
Programmer to Programmer™
Interact
Take an active role online by participating in
our P2P forums
Wrox Online Library
Hundreds of our books are available online
through Books24x7.com
Wrox Blox
Download short informational pieces and
code to keep you up to date and out of
trouble!
Chapters on Demand
Purchase individual book chapters in pdf
format
Join the Community
Sign up for our free monthly newsletter at
newsletter.wrox.com
Browse
Ready for more Wrox? We have books and
e-books available on .NET, SQL Server, Java,
XML, Visual Basic, C#/ C++, and much more!
Contact Us.
We always like to get feedback from our readers. Have a book idea?
Need community support? Let us know by e-mailing [email protected]
Beginning Microsoft SQL Server® 2008
Programming
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi
Chapter 1: RDBMS Basics: What Makes Up a SQL Server Database? . . . . . . . 1
Chapter 2: Tools of the Trade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Chapter 3: The Foundation Statements of T-SQL . . . . . . . . . . . . . . . . . . . . . . 43
Chapter 4: JOINs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Chapter 5: Creating and Altering Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Chapter 6: Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Chapter 7: Adding More to Our Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
Chapter 8: Being Normal: Normalization and Other Basic Design Issues. . . . 215
Chapter 9: SQL Server Storage and Index Structures . . . . . . . . . . . . . . . . . 261
Chapter 10: Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Chapter 11: Writing Scripts and Batches . . . . . . . . . . . . . . . . . . . . . . . . . . 325
Chapter 12: Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
Chapter 13: User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Chapter 14: Transactions and Locks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 427
Chapter 15: Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
Chapter 16: A Brief XML Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Chapter 17: Reporting for Duty, Sir! A Look At Reporting Services . . . . . . . 517
Chapter 18: Getting Integrated with Integration Services . . . . . . . . . . . . . . 543
Chapter 19: Playing Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 563
Appendix A: System Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587
Appendix B: Very Simple Connectivity Examples. . . . . . . . . . . . . . . . . . . . . 649
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 657
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page i
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page ii
Beginning
Microsoft SQL Server® 2008 Programming
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page iii
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page iv
Beginning
Microsoft SQL Server® 2008 Programming
Robert Vieira
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page v
Beginning Microsoft SQL Server® 2008 Programming
Published by
Wiley Publishing, Inc.
10475 Crosspoint Boulevard
Indianapolis, IN 46256
www.wiley.com
Copyright © 2009 by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
ISBN-13: 978-0-470-25701-2
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
Library of Congress Cataloging-in-Publication Data:
Vieira, Robert.
Beginning SQL server 2008 programming / Robert Vieira.
p. cm.
Includes index.
ISBN 978-0-470-25701-2 (paper/website) 1. SQL server. 2. Database management. 3. Relational databases.
I. Title.
QA76.9.D3V5254 2008
005.75'85--dc22
2008033212
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 (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 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 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, Wrox 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 registered 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.
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page vi
For over 10 years now, and through what is now 5 books (6 when I finish the Pro title
this time around), I’ve dedicated my books to my daughters (well, it was a single
daughter when I wrote my first one). I don’t see any reason to change that now.
Things in life have changed a great deal for me over the years, and the oldest daughter
didn’t have to sacrifice as much time with me this time around (college life means she
doesn’t want to see that much of me anyway — I hate kids growing up!). The younger
one regularly asks me when I’m going to be done, so I guess she’s noticed time lost, and,
frankly, so have I.
And so it is, once again, that I dedicate this book to my two daughters, and promise that
at least some portion of the proceeds of this book will help pay for a nice trip to Disney
World so we can make up a little time!
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page vii
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page viii
About the Author
Experiencing his first infection with computing fever in 1978, Rob Vieira knew right away that this was
something “really cool.” In 1980 he began immersing himself in 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 the DBA Team Lead for the Stockamp practice of The Huron Consulting Group in Portland,
Oregon, and makes occasional speaking appearances on database development, business intelligence,
and other topics.
He resides with his youngest daughter Adrianna (aka Addy, aka Trillian, aka “T”) in Vancouver, WA.
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page ix
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page x
Credits
Executive Editor
Bob Elliott
Development Editor
Sydney Jones
Technical Editor
John Mueller
Production Editor
Daniel Scribner
Copy Editor
Kim Cofer
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, Cover
Lynsey Stanford
Compositor
Craig Johnson, Happenstance Type-O-Rama
Proofreaders
Justin Neely, Word One
Kathy Pope, Word One
Josh Chase, Word One
Indexer
Ron Strauss
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page xi
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page xii
Acknowledgments
Over the years, there have been so many that deserve a note of thanks. Some long ago earned a permanent place in my acknowledgment list, and others are new to the scene but have done their own special
things along the way.
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 continue to hold this first 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 of dealing with 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. (I said the same thing in my
last book, but I don’t seem to have made much progress.)
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.
Greg Jackson, who saved me some sleepless nights by taking over some of the copy-edit phase duties
for this title, so I could increase my focus on finishing the Professional title without messing up the
schedule. The trials of late changes to the product and troubles in the editing process wound up making
things substantially more frustrating than I imagine Greg thought he was signing up for, and I appreciate his willingness to keep sorting things out until I stepped back in.
A few other honorable mentions are deserved for past or present contributions (forgive me if I leave
someone out): Richard Waymire, Gert Drapers, Bill Ramos, Dan Jones, and Bob Elliott.
57012ffirs.qxd:WroxBeg 11/24/08 4:52 PM Page xiii