Beginning T-SQL 2008

466 Pages·2009·9.06 MB·English

by  Kathi Kellenberger

cyan yeLLoW
MaGenTa BLack
Books for professionaLs By professionaLs® The experT’s Voice® in sQL serVer
Companion
eBook
Available
Beginning T-SQL 2008
B
Dear Reader,
e
Transact-SQL (T-SQL) is the language used to retrieve and manipulate data in a
SQL Server database, and it’s the programming language that I use most often
g
in my job as a SQL Server DBA. T-SQL is really far more than just a simple query
language involving statements such as SELECT and UPDATE. T-SQL is a full-
i
Beginning
fledged programming language that allows you to use SQL Server in some very
n
Coauthor of powerful ways. It allows you to write stored code that executes in the database,
allows you to centralize business logic in the database, and quite frankly is the
Professional SQL n
Server 2005 Integration key to utilizing SQL Server. Using SQL Server without knowing T-SQL is like try-
Services (Wrox) ing to ride a bicycle without attaching the pedals. i T-SQL 2008
Beginning T-SQL 2008 is organized so that each section and chapter builds n
upon what comes before it. Instead of showing you every possible option when
I introduce a new statement or topic, each section covers a single concept, and
g
those concepts lead you one step at a time toward mastery of the language.
Almost every section in the book contains a code example, and I encourage

you to type in and execute each example to help you learn. I also encourage you T
to experiment with these examples or to come up with your own. Most of the
chapters have exercises giving you the chance to work out the solution. Take -
the time to complete the exercises because that is how you will learn. S
Without pedals, you might get your bike to move, but you’ll go much faster
and more efficiently if you attach the pedals. Go ahead. Put on your pedals.
Q
Learn T-SQL.
Kathi Kellenberger, SQL Server MVP, MCT L

THE APRESS ROADMAP 2 Quickly move beyond beginner status with this
Pro Expert step-by-step approach to learning T-SQL
Beginning T-SQL 2008 SQL Server 2008 0
T-SQL 2008 Programmer’s Guide Development
Companion eBook
0
8
SQL Server 2008
Beginning Transact-SQL SQL Server 2008
See last page for details SQL Queries Recipes Query Performance Tuning
on $10 eBook version
K
e
l
l
e
n Kathi Kellenberger
SOURCE CODE ONLINE
b
www.apress.com ISBN 978-1-4302-2461-7 e
r
53999 g
e
r
US $39.99
Shelve in
Databases / SQL Server
User level:
9 781430 224617
Beginner Beginning Joomla! Pro PHP: Patterns,
Second Edition Frameworks, Testing,
Advanced Joomla! and More
this print for content only—size & color not accurate .875 trim = 7.5" x 9.25" spine = 0.875" 464 page count

Beginning T-SQL 2008

■ ■ ■
Kathi Kellenberger

i
Beginning T-SQL 2008
Copyright © 2009 by Kathi Kellenberger
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-4302-2461-7
ISBN-13 (electronic): 978-1-4302-2462-4
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.
Java™ and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc., in
the US and other countries. Apress, Inc., is not affiliated with Sun Microsystems, Inc., and this book was
written without endorsement from Sun Microsystems, Inc.
Lead Editors: Jonathan Gennick, Douglas Pundick
Technical Reviewer: Ken Simmons
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Tony Campbell, Gary
Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Jeffrey
Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt
Wade, Tom Welsh
Project Manager: Kylie Johnston
Copy Editor: Kim Wimpsett
Production Support: Patrick Cunningham
Indexer: BIM Indexing
Artist: April Milne
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 [phone], e-mail [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 233 Spring Street, New York, NY 10013.
E-mail [email protected], or visit http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our
Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
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.

ii
This book is dedicated to Thomas Indiana, the most joyful person I have
ever known. In his first three years, he has taught me to appreciate every
moment of my life.

iii
iv
Contents at a Glance
■ Contents at a Glance..............................................................................................v
■ Contents................................................................................................................vi
■ About the Author..............................................................................................xviii
■ About the Technical Reviewer..............................................................................xix
■ Acknowledgments...............................................................................................xx
■ Introduction........................................................................................................xxi
■ Chapter 1: Getting Started....................................................................................1
■ Chapter 2: Writing Simple SELECT Queries.........................................................37
■ Chapter 3: Using Functions and Expressions.....................................................79
■ Chapter 4: Querying Multiple Tables................................................................117
■ Chapter 5: Grouping and Summarizing Data....................................................151
■ Chapter 6: Manipulating Data...........................................................................183
■ Chapter 7: Understanding T-SQL Programming Logic......................................221
■ Chapter 8: Moving Logic to the Database.........................................................269
■ Chapter 9: Working with New Data Types........................................................317
■ Chapter 10: Writing Advanced Queries.............................................................343
■ Chapter 11: Where to Go Next?.........................................................................375
■ Appendix: Solutions to the Exercises...............................................................379
■ Index.................................................................................................................427
v
vi
Contents
■ Contents at a Glance..............................................................................................v
■ Contents................................................................................................................vi
■ About the Author..............................................................................................xviii
■ About the Technical Reviewer..............................................................................xix
■ Acknowledgments...............................................................................................xx
■ Introduction........................................................................................................xxi
■ Chapter 1: Getting Started....................................................................................1
Installing SQL Server Express Edition.............................................................................1
Installing the Sample Databases..................................................................................12
Installing Books Online.............................................................................................16
Using Books Online...................................................................................................17
Using SQL Server Management Studio.........................................................................19
Launching SQL Server Management Studio.............................................................20
Running Queries.......................................................................................................21
Exploring Database Concepts.......................................................................................26
What Is SQL Server?.................................................................................................26
Database As Container.............................................................................................28
Data Is Stored in Tables............................................................................................29
Data Types................................................................................................................30
Normalization...........................................................................................................31
Understanding Indexes.............................................................................................33
Database Schemas...................................................................................................34
Summary......................................................................................................................35

vii
■ CONTENTS

■ Chapter 2: Writing Simple SELECT Queries.........................................................37
Using the SELECT Statement........................................................................................37
Selecting a Literal Value...........................................................................................37
Retrieving from a Table............................................................................................38
Generating a Select-List...........................................................................................40
Mixing Literals and Column Names..........................................................................42
Filtering Data................................................................................................................43
Adding a WHERE Clause...........................................................................................43
Using WHERE Clauses with Alternate Operators......................................................45
Using BETWEEN........................................................................................................48
Using NOT BETWEEN................................................................................................49
Filtering On Date and Time.......................................................................................51
Pattern Matching with LIKE......................................................................................53
Restricting the Characters in Pattern Matches.........................................................54
Combining Wildcards................................................................................................56
Using WHERE Clauses with Two Predicates.............................................................58
Using WHERE Clauses with Three or More Predicates.............................................58
Using NOT with Parentheses....................................................................................60
Using the IN Operator...............................................................................................62
Working with Nothing...................................................................................................63
Performing a Full-Text Search......................................................................................65
Using CONTAINS.......................................................................................................66
Using Multiple Terms with CONTAINS......................................................................67
Searching Multiple Columns.....................................................................................68
Using FREETEXT.......................................................................................................69
Sorting Data..................................................................................................................70
Thinking About Performance........................................................................................72
Taking Advantage of Indexes...................................................................................72
Viewing Execution Plans...........................................................................................74
Summary......................................................................................................................77
■ Chapter 3: Using Functions and Expressions.....................................................79
Expressions Using Operators........................................................................................79
Concatenating Strings..............................................................................................79
Concatenating Strings and NULL..............................................................................80
viii