The Gurus Guide To Transact SQL - 2000, SQL
[ Pobierz całość w formacie PDF ]
Guru’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:
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
[ Pobierz całość w formacie PDF ]