Table Of ContentGuru’s Guide to Transact-SQL
The Guru's Guide to Transact-SQL
An imprint of Addison Wesley Longman, Inc.
Reading, Massachusetts • Harlow, England • Menlo Park, California
Berkeley, California • Don Mills, Ontario • Sydney
Bonn • Amsterdam • Tokyo • Mexico City
Copyright Information
Copyright © 2000 by Addison-Wesley
All rights reserved. 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, or otherwise,
without the prior consent of the publisher. Printed in the United States of America. Published
simultaneously in Canada.
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book and Addison-Wesley was aware of a
trademark claim, the designations have been printed in initial caps or all caps.
Warning and Disclaimer
The author and publisher have taken care in the preparation of this book but make no expressed or
implied warranty of any kind and assume no responsibility for errors or omissions. No liability is
assumed for incidental or consequential damages in connection with or arising out of the use of the
information or programs contained herein.
The publisher offers discounts on this book when ordered in quantity for special sales. For more
information, please contact:
Corporate, Government, and Special Sales Group
Addison Wesley Longman, Inc.
One Jacob Way
Reading, Massachusetts 01867
(781) 944-3700
Visit AW on the Web: http://www.awl.com
Library of Congress Cataloging-in-Publication Data
Henderson, Kenneth W.The guru's guide to Transact-SQL / Kenneth W. Henderson.p. cm.Includes
bibliographical references and index.
1. SQL (Computer program language) I. Title.
QA76.73.S67 H47 2000
005.7596—dc21
99-057209Copyright © 2000 by Addison-Wesley
All rights reserved. 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, or otherwise,
without the prior consent of the publisher. Printed in the United States of America. Published
simultaneously in Canada.
Text printed on recycled and acid-free paper.
1 2 3 4 5 6 7 8 9 10—MA—03 02 01 00
1st Printing, June 2000
For H
Foreword
Foreword
What Ken Henderson wanted to do is to write the best possible book on real, practical programming in
Transact-SQL available, bar none. He succeeded. Ken had most of these tricks in his head when he started
this book. When you work for a living, you tend to pick things up. If you are smart, you save them, study them,
and figure out why they worked and something else did not work. If you are a nice person, you write a book so
someone else can benefit from your knowledge. It is very hard for a person new to a language to walk into a
project knowing only the syntax and a few rules and write a complex program. Ever try to get along in a
foreign country with only a dictionary and a pocket grammar book?
Okay, we now have a goal for this book. The next step is how to write so that someone can use it. Writing in
the age of the Internet is really different from the days when Victor Hugo would stand by a writing desk and
write great novels on one continuous strip of paper with a quill pen. Today, within the week that a book hits
hardcopy, the author can expect some compulsive geek with an email connection to read it and find
everything that the author left out or got wrong and every punctuation mark that the proofreader or typesetter
missed. In short, you can be humiliated at the speed of light.
But this can work both ways. When you are writing your book, you can exploit this vast horde of people who
have nothing better to do with their time than be your unpaid research staff!
Since I have a reputation for expertise in SQL standards and programming, I was one of the people he
emailed and asked to look over the manuscript. Neat stuff and some tricks I had not seen before! Suddenly,
we are swapping ideas and I am stealing—er, researching—my next book, too. Well, communication is a two
way street, you know.
I think you will find this book to be an easy read with a lot of good ideas and code samples. While this is
specifically a Transact-SQL book, you will find that many of the approaches and techniques will work with any
SQL product. Enjoy!
—Joe Celko
i
Preface
Preface
This is a coder's book. It's intended to help developers build applications that make use of Transact-SQL. It's
not about database administration or design. It's not about end-user or GUI application development. It's not
even about server or database performance tuning. It's about developing the best Transact-SQL code
possible, regardless of the application.
When I began writing this book, I had these design goals in mind:
• Be very generous with code samples—don't just tell readers how to do something, show them.
• Include complete code samples within the chapter texts so that the book can be read through without
requiring a computer or CD-ROM.
• Use modern coding techniques, with specific emphases on ANSI compliance and current version
features and enhancements.
• Construct chapters so that they're self-contained—so that they rely as little as possible on objects
created in other chapters.
• Provide real-world code samples that have intrinsic value apart from thebook.
• Avoid rehashing what's already covered extensively in the SQL Server Books Online.
• Highlight aspects of Transact-SQL that differentiate it from other SQL dialects; don't just write another
ANSI SQL book.
• Avoid excessive screenshots and other types of filler mechanisms often seen in computer books.
• Proceed from the simple to the complex within each chapter and throughout the book.
• Provide an easygoing, relaxed commentary with a de-emphasis on formality. Be the reader's
indulgent, amiable tutor. Attempt to communicate in writing the way that people speak.
You'll have to judge for yourself whether these goals have been met, but my hope is that, regardless of the
degree of success, the effort will at least be evident.
About the Sample Databases
This book uses SQL Server's Northwind and pubs sample databases extensively. You'll nearly always be able
to determine which database a particular example uses from the surrounding commentary or from the code
itself. The pubs database is used more often than Northwind, so, when it's not otherwise specified or when in
doubt, use pubs.
Usually, modifications to these databases are made within transactions so that they can be reversed; however,
for safety's sake, you should probably drop and recreate them after each chapter in which they're modified.
The scripts to rebuild them (instnwnd.sql and instpubs.sql) can be found in the \Install subdirectory under the
root SQL Server folder.
Results Abridged
If I have a pet peeve about computer books, it's the shameless use of space-filling devices to lengthen them—
the dirty little secret of the computer publishing industry. Many technical books these days overflow with
gratuitous helpings of screenshots, charts, diagrams, outlines, sidebars, icons, line art, etc. There are people
who assign more value to a book that's heavy, and many authors and publishers have been all too happy to
accommodate them. They seem to take the old saying that "a picture is worth a thousand words" literally—in
some cases turning out books that are little more than picture books.
I think there's a point at which comprehensiveness gives way to corpulence, a time when exhaustiveness
becomes exhausting. In this book, I've tried to strike a balance between being thorough and being space-
efficient. To that end, I've often truncated or clipped query result sets, especially those too wide to fit on a
page and those of excessive length (I always point this out). On occasion I also list them using reduced font
sizes. I don't include screenshots unless doing so benefits the discussion at hand materially (only one chapter
contains any screenshots). This is in keeping with my design goal of being complete without being
overwrought. Nearly 600SQL scripts are used in this book, and they are all included in the chapters that
reference them. Hopefully none of the abridgements will detract from the book's overall usefulness or value.
On Formality
iii
Guru’s Guide to Transact-SQL
Another of my pet peeves is formality for the sake of formality. An artist once observed that "it's harder to draw
a good curved line than a straight one." What he meant was that it's in some ways more difficult to do
something well for which there is no exact or stringent standard than to do something that's governed by
explicit rules and stuffy precedents. All you have to do to draw a straight line is pick up a straightedge. The
rules that govern formal writing, particularly that of the academic variety, make writing certain kinds of books
easier because they convert much of the subjective nature of writing into something more objective. They're
like training wheels on the would-be author's bicycle. Writing goes from being a creative process to a
mechanical one. Cross all the T's, dot all the I's, and you're halfway there. Obviously, this relieves the author
of many of the decisions that shape creative writing. It also turns otherwise good pieces of work into dreary,
textbook-like dissertations that are about as interesting as the telephone book White Pages.
So, I reject the notion that formal writing is better writing, that it is a higher standard and is the ideal for which
all technical writers should strive. Instead, I come from the Mark Twain school of thought—I "eschew
surplusage"—and I believe that, so long as common methods of speech do not become overly banal (a
subjective distinction, I freely admit), the ultimate goal of the technical writer should be to write the way that
readers speak. It is the way people—even technical people—are most accustomed to communicating and the
way they are the most able to learn and share ideas. I did not invent this way of thinking; it's simply the way
most of my favorite authors—Mark Twain, Dean Koontz, Joe Celko, Ernest Hemingway, Robert Heinlein,
Andrew Miller, Oscar Wilde, P.J. O'Rourke, Patricia O'Connor—write. Though it is far more difficult to structure
and write a narrative that flows naturally and reads easily, it's worth the effort if the ideas the writer seeks to
convey are understood as they were intended.
So, throughout this book, you'll see a number of the rules and pseudo rules of formal writing stretched, skirted,
bent, and sometimes outright broken. This is intentional. Sometimes I split infinitives, begin sentences with
conjunctions, and end them with prepositions.[1] Sometimes record is used interchangeably with row;
sometimes field takes the place of column; and I never, ever treat data as a plural word. I saw some software
recently that displayed a message to the effect "the data are being loaded," and I literally laughed out loud.
The distinction between the plural data and its obscure singular form datum is not maintained in spoken
language and hasn't really ever been (except, perhaps, in ancient Rome). It has also been deprecated by
numerous writing guides [2] and many authors[3] You will have to look very hard for an author who treats
dataas a plural word (I can think of only one off the top of my head, the irascible Ted Codd). The tendency for
technical communication to become self-important or ostentatious has always bafed me: why stoop to
pretension? Why trade the uid conveyance of ideas between people for nonsense that confuses some and
reads like petty one-upmanship to others?
[1] According to Patricia T. O'Connor's excellent book, Words Fail Me (Harcourt Brace & Company, 1999), a number of these
rules are not really rules at all. The commonly cited prohibitions against split infinitives, beginning sentences with
conjunctions, using contractions, and ending sentences with prepositions are all pseudo rules—they are not, nor have ever
been, true English grammatical rules. They originate from dubious attmepts to force Latin grammar on the English language
and have been broken and regularly ignored by writers since the 1300s.
[2] See, for example, The Microsoft Manual of Style for Technical Publications (Microsoft Press, 1995), p.48.
[3] See, for example, Joe Celko's Data and Databases: Concepts in Practice (Morgan-Kaufmann Publishers, 1999), p.3,
where Joe refers to data in the singular as he does throughout the book.
Acknowledgments
I'd like to thank my wife, who not only makes it possible for me to write books but also makes it worthwhile.
The book you see before you is as much hers as it is mine. I'd like to thank Neil Coy, who made a real
programmer of me many years ago. Under Neil's tutelage, I learned software craftsmanship from a master.
Joe Celko, the dean of the SQL language, has been a good friend and a valuable source of information
throughout this project. Kudos to John Sarapata and Thomas Holaday for helping me come up with a title for
the book (I'll keep Sybase for Dummies in mind for future use, John). Thanks to the book's technical reviewers,
particularly Wayne Snyder, Gianluca Hotz, Paul Olivieri, and Ron Talmage. Heartfelt thanks to John
Gmuender, Joe Gallagher, Mike Massing, and Danny Thorpe for their equanimity and for keeping me sane
through the recent storm. Congratulations and genuine appreciation to the superb team at Addison-Wesley—
Michael Slaughter, Marisa Meltzer, J. Carter Shanklin, and others too numerous to list. Special thanks to
Nancy Cara-Sager, a friend, technical reviewer, and copyeditor who's been with me through several books
and a couple of publishers now. Her tireless attention to detail has saved me from embarrassing myself more
times than I can count.
iv
Contents
Contents
Foreword............................................................................................................................................i
Preface............................................................................................................................................iii
About the Sample Databases.................................................................................................iii
Results Abridged.......................................................................................................................iii
On Formality...............................................................................................................................iii
Acknowledgments......................................................................................................................iv
Contents..........................................................................................................................................v
Chapter 1. Introductory Transact-SQL.........................................................................................1
Choosing a SQL Editor...............................................................................................................1
Creating a Database...................................................................................................................2
Creating Tables...........................................................................................................................3
Inserting Data...............................................................................................................................4
Updating Data..............................................................................................................................5
Deleting Data...............................................................................................................................5
Querying Data..............................................................................................................................6
Filtering Data................................................................................................................................9
Grouping Data...........................................................................................................................14
Ordering Data............................................................................................................................16
Column Aliases..........................................................................................................................16
Table Aliases..............................................................................................................................17
Managing Transactions............................................................................................................17
Summary....................................................................................................................................18
Chapter 2. Transact-SQL Data Type Nuances........................................................................19
Dates...........................................................................................................................................19
Strings.........................................................................................................................................28
Numerics.....................................................................................................................................46
BLOBs.........................................................................................................................................50
Bits...............................................................................................................................................55
UNIQUEIDENTIFIER................................................................................................................57
Cursor Variables........................................................................................................................58
Timestamps................................................................................................................................62
Summary....................................................................................................................................64
Chapter 3. Missing Values...........................................................................................................65
NULL and Functions.................................................................................................................66
NULL and ANSI SQL................................................................................................................67
NULL and Stored Procedures.................................................................................................68
NULL if you Must.......................................................................................................................69
Chapter 4. DDL Insights...............................................................................................................71
CREATE TABLE........................................................................................................................71
Dropping Objects.......................................................................................................................74
CREATE INDEX........................................................................................................................75
TEMPORARY OBJECTS.........................................................................................................76
Object Naming and Dependencies.........................................................................................77
Summary....................................................................................................................................78
Chapter 5. DML Insights..............................................................................................................81
v
Guru’s Guide to Transact-SQL
INSERT.......................................................................................................................................81
UPDATE.....................................................................................................................................91
DELETE....................................................................................................................................100
Detecting DML Errors.............................................................................................................103
Summary..................................................................................................................................103
Chapter 6. The Mighty SELECT Statement............................................................................105
Simple SELECTs.....................................................................................................................105
Computational and Derived Fields.......................................................................................105
SELECT TOP...........................................................................................................................106
Derived Tables.........................................................................................................................108
Joins..........................................................................................................................................111
Predicates.................................................................................................................................113
Subqueries...............................................................................................................................123
Aggregate Functions...............................................................................................................129
GROUP BY and HAVING......................................................................................................131
UNION.......................................................................................................................................137
ORDER BY...............................................................................................................................139
Summary..................................................................................................................................141
Chapter 7. Views.........................................................................................................................143
Restrictions...............................................................................................................................143
ANSI SQL Schema VIEWs....................................................................................................144
Getting a VIEW's Source Code.............................................................................................145
Updatable VIEWs....................................................................................................................146
WITH CHECK OPTION..........................................................................................................146
Derived Tables.........................................................................................................................146
Dynamic VIEWs.......................................................................................................................147
Partitioning Data Using Views...............................................................................................148
Summary..................................................................................................................................150
Chapter 8. Statistical Functions................................................................................................151
The Case for CASE................................................................................................................151
Efficiency Concerns................................................................................................................152
Variance and Standard Deviation.........................................................................................153
Medians....................................................................................................................................153
Clipping.....................................................................................................................................160
Returning the Top n Rows.....................................................................................................161
Rankings...................................................................................................................................164
Modes........................................................................................................................................166
Histograms...............................................................................................................................167
Cumulative and Sliding Aggregates.....................................................................................168
Extremes...................................................................................................................................170
Summary..................................................................................................................................172
Chapter 9. Runs and Sequences.............................................................................................173
Sequences ...............................................................................................................................173
Runs..........................................................................................................................................178
Intervals....................................................................................................................................180
Summary..................................................................................................................................182
Chapter 10. Arrays......................................................................................................................185
Arrays as Big Strings..............................................................................................................185
Arrays as Tables......................................................................................................................190
Summary..................................................................................................................................198
vi
Contents
Chapter 11. Sets.........................................................................................................................199
Unions.......................................................................................................................................199
Differences...............................................................................................................................201
Intersections.............................................................................................................................202
Subsets.....................................................................................................................................204
Summary..................................................................................................................................207
Chapter 12. Hierarchies.............................................................................................................209
Simple Hierarchies..................................................................................................................209
Multilevel Hierarchies..............................................................................................................210
Indented lists............................................................................................................................215
Summary..................................................................................................................................216
Chapter 13. Cursors...................................................................................................................217
On Cursors and ISAMs..........................................................................................................217
Types of Cursors.....................................................................................................................218
Appropriate Cursor Use.........................................................................................................222
T-SQL Cursor Syntax.............................................................................................................226
Configuring Cursors................................................................................................................234
Updating Cursors....................................................................................................................238
Cursor Variables......................................................................................................................239
Cursor Stored Procedures.....................................................................................................240
Optimizing Cursor Performance............................................................................................240
Summary..................................................................................................................................242
Chapter 14. Transactions...........................................................................................................243
Transactions Defined..............................................................................................................243
How SQL Server Transactions Work...................................................................................244
Types of Transactions............................................................................................................244
Avoiding Transactions Altogether.........................................................................................246
Automatic Transaction Management...................................................................................246
Transaction Isolation Levels..................................................................................................248
Transaction Commands and Syntax....................................................................................251
Debugging Transactions........................................................................................................256
Optimizing Transactional Code.............................................................................................257
Summary..................................................................................................................................258
Chapter 15. Stored Procedures and Triggers.........................................................................259
Stored Procedure Advantages..............................................................................................260
Internals....................................................................................................................................260
Creating Stored Procedures..................................................................................................261
Executing Stored Procedures...............................................................................................269
Environmental Concerns........................................................................................................270
Parameters...............................................................................................................................272
Important Automatic Variables..............................................................................................275
Flow Control Language..........................................................................................................276
Errors.........................................................................................................................................277
Nesting......................................................................................................................................279
Recursion..................................................................................................................................280
Autostart Procedures..............................................................................................................281
Encryption.................................................................................................................................281
Triggers.....................................................................................................................................281
Debugging Procedures...........................................................................................................284
Summary..................................................................................................................................285
vii
Guru’s Guide to Transact-SQL
Chapter 16. Transact-SQL Performance Tuning...................................................................287
General Performance Guidelines.........................................................................................287
Database Design Performance Tips....................................................................................287
Index Performance Tips.........................................................................................................288
SELECT Performance Tips...................................................................................................290
INSERT Performance Tips....................................................................................................291
Bulk Copy Performance Tips.................................................................................................291
DELETE and UPDATE Performance Tips..........................................................................292
Cursor Performance Tips.......................................................................................................292
Stored Procedure Performance Tips ...................................................................................293
SARGs......................................................................................................................................296
Denormalization.......................................................................................................................311
The Query Optimizer..............................................................................................................325
The Index Tuning Wizard.......................................................................................................333
Profiler.......................................................................................................................................334
Perfmon....................................................................................................................................335
Summary..................................................................................................................................337
Chapter 17. Administrative Transact-SQL..............................................................................339
GUI Administration..................................................................................................................339
System Stored Procedures....................................................................................................339
Administrative Transact-SQL Commands...........................................................................339
Administrative System Functions.........................................................................................339
Administrative Automatic Variables......................................................................................340
Where's the Beef?...................................................................................................................341
Summary..................................................................................................................................392
Chapter 18. Full-Text Search....................................................................................................395
Full-Text Predicates................................................................................................................399
Rowset Functions....................................................................................................................402
Summary..................................................................................................................................405
Chapter 19. Ole Automation......................................................................................................407
sp-exporttable..........................................................................................................................407
sp-importtable..........................................................................................................................411
sp-getsQLregistry....................................................................................................................415
Summary..................................................................................................................................417
Chapter 20. Undocumented T-SQL.........................................................................................419
Defining Undocumented.........................................................................................................419
Undocumented DBCC Commands......................................................................................419
Undocumented Functions and Variables............................................................................430
Undocumented Trace Flags..................................................................................................433
Undocumented Procedures...................................................................................................434
Summary..................................................................................................................................438
Chapter 21. Potpourri.................................................................................................................439
Obscure Functions..................................................................................................................439
Data Scrubbing........................................................................................................................448
Iteration Tables........................................................................................................................451
Summary..................................................................................................................................452
Appendix A. Suggested Resources.........................................................................................453
Books........................................................................................................................................453
Internet Resources..................................................................................................................453
viii
