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
SQL Server 2000 stored procedure & XML programming
Nội dung xem thử
Mô tả chi tiết
SQL ServerTM 2000
Stored Procedure
& XML Programming
Second Edition
Dejan Šunderic
McGraw-Hill/Osborne
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 /
Blind Folio i
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:53 PM
Color profile: Generic CMYK printer profile
Composite Default screen
McGraw-Hill/Osborne
2100 Powell Street, 10th Floor
Emeryville, California 94608
U.S.A.
To arrange bulk purchase discounts for sales promotions, premiums, or fund-raisers, please contact
McGraw-Hill/Osborne at the above address. For information on translations or book distributors
outside the U.S.A., please see the International Contact Information page immediately following the
index of this book.
SQL Server™ 2000 Stored Procedure & XML Programming, Second Edition
Copyright © 2003 by The McGraw-Hill Companies. All rights reserved. Printed in the United States
of America. Except as permitted under the Copyright Act of 1976, no part of this publication may be
reproduced or distributed in any form or by any means, or stored in a database or retrieval system,
without the prior written permission of publisher, with the exception that the program listings may be
entered, stored, and executed in a computer system, but they may not be reproduced for publication.
1234567890 CUS CUS 019876543
ISBN 0-07-222896-2
Publisher Brandon A. Nordin
Vice President & Associate Publisher Scott Rogers
Acquisitions Editor Lisa McClain
Project Editor Janet Walden
Acquisitions Coordinator Athena Honore
Technical Editor Deborah Bechtold
Development Editor Tom Woodhead
Copy Editor William McManus
Proofreader Laurie Stewart
Indexer Valerie Robbins
Computer Designers Carie Abrew, Tara A. Davis, Lucie Ericksen
Illustrators Lyssa Wald, Melinda Moore Lytle, Kathleen Fay Edwards
Series Designer Peter F. Hancik
Cover Series Designer Pattie Lee
This book was composed with Corel VENTURA™ Publisher.
Information has been obtained by McGraw-Hill/Osborne from sources believed to be reliable. However, because of the possibility
of human or mechanical error by our sources, McGraw-Hill/Osborne, or others, McGraw-Hill/Osborne does not guarantee the
accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained
from the use of such information.
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 /
Blind Folio ii
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:53 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Writing a book isn’t easy, but living with someone
who is writing a book can be, at times, even harder.
I would like to thank my family for their patience,
understanding, and inspiration.
Acknowledgments
I wish to thank all the people who helped to make this book a reality,
in particular:
Tom Woodhead, for straightening the winding course of my writings.
Olga Baranova, who created several examples for Chapters 10, 11, and 15.
Wendy Rinaldi and Lisa McClain, for the opportunity to do this project.
Athena Honore and Janet Walden, for their patience, expertise, and
hard work.
Deborah Bechtold, for her expertise and hard work beyond the call of duty.
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 /
Blind Folio iii
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 1:15:28 PM
Color profile: Generic CMYK printer profile
Composite Default screen
About the Author
Dejan Šunderic is the principal consultant at Trigon Blue, Inc. (www.trigonblue.com).
He specializes in database and application development for Internet and
Windows platforms.
Projects that he has been involved with cover B2C and B2B e-commerce, financial,
document-management, mortgage, asset management, insurance, real-estate, IT
supply chain, process control, communication, data warehouse, and OLAP systems.
Dejan has worked as a database architect, database and application developer, database
administrator, team leader, project manager, writer, and technical trainer.
He is the author of SQL Server 2000 Stored Procedure Programming (www
.trigonblue.com/stored_procedure.htm), coauthor of Windows 2000 Performance
Tuning and Optimization, and three other books, as well as numerous technical
articles for several computer and professional publications.
His career started in Belgrade, Yugoslavia where he graduated on Faculty of
Electrical Engineering. In 1995 he moved to Toronto, Canada and he is currently
in Pittsburgh, U.S.A. He holds certifications for Microsoft Certified Solution
Developer (MCSD), Microsoft Certified Database Administrator (MCDBA), and
Certified SQL Server Programmer Master Level. Dejan is a member of Toronto
SQL Server User Group (www.tssug.com), Visual Basic Developer’s Online
Group (www.visualbyte.com/vbdogs), Pittsburgh SQL Server User Group
(www.pssug.com), and Professional Association for SQL Server (www.sqlpass.org).
Dejan can be contacted by email (dejan’s username on hotmail.com server; to
avoid spam filter, put sp_book in the subject) or the book’s web site (www
.trigonblue.com/sqlxml).
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 /
Blind Folio iv
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:53 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Contents at a Glance
Chapter 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Chapter 2 The SQL Server Environment . . . . . . . . . . . . . . . . . . . . . . . . . 19
Chapter 3 Stored Procedure Design Concepts . . . . . . . . . . . . . . . . . . . . . . 53
Chapter 4 Basic Transact-SQL Programming Constructs . . . . . . . . . . . . . . . . 85
Chapter 5 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Chapter 6 Composite Transact-SQL Constructs: Batches,
Scripts, and Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Chapter 7 Debugging and Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . 207
Chapter 8 Special Types of Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Chapter 9 Advanced Stored Procedure Programming . . . . . . . . . . . . . . . . . . 341
Chapter 10 Interaction with the SQL Server Environment . . . . . . . . . . . . . . . . 389
Chapter 11 Source Code Management and Database Deployment . . . . . . . . . . . 437
Chapter 12 Stored Procedures for Web Search Engines . . . . . . . . . . . . . . . . . 467
Chapter 13 Introduction to XML for Database Developers . . . . . . . . . . . . . . . 489
Chapter 14 Publishing Information Using SQLXML . . . . . . . . . . . . . . . . . . . . 527
Chapter 15 Modifying Databases Using SQLXML . . . . . . . . . . . . . . . . . . . . . 605
Appendix T-SQL and XML Data Types in SQL Server 2000 . . . . . . . . . . . . . . 659
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669
v
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 /
Blind Folio vi
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
This page intentionally left blank
Contents
Chapter 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Who Should Read This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
What You Will Find in This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Stored Procedure Programming Requirements . . . . . . . . . . . . . . . . . . . 5
XML Programming Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Sample Database and Other Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Sample Database Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Purpose and Design of the Sample Database . . . . . . . . . . . . . . . . . . . . 9
Database Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Chapter 2 The SQL Server Environment . . . . . . . . . . . . . . . . . . . . . . . . . 19
SQL Server 2000 Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Service Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Query Analyzer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Enterprise Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
DTS and Import/Export Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
osql and isql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
SQL Server Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
Client Network Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Server Network Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
The Help Subsystem and SQL Server Books Online . . . . . . . . . . . . . . . . . 29
SQL Server on the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Basic Operations with Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 31
What Are Stored Procedures? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Execution of Stored Procedures from Query Analyzer . . . . . . . . . . . . . . . . 32
Managing Stored Procedures from Enterprise Manager . . . . . . . . . . . . . . . 36
vii
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Editing Stored Procedures in Enterprise Manager . . . . . . . . . . . . . . . . . . 41
Editing Stored Procedures in Query Analyzer . . . . . . . . . . . . . . . . . . . . 41
Syntax Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Naming Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Why Bother? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Naming Objects and Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Suggested Convention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
Chapter 3 Stored Procedure Design Concepts . . . . . . . . . . . . . . . . . . . . . . 53
Anatomy of a Stored Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Composition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Functionality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Types of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
The Compilation and Execution Process . . . . . . . . . . . . . . . . . . . . . . . 67
Reuse of Execution Plans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Recompiling Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Storing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Managing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Listing Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Viewing Code of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . 77
Renaming Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Deleting Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Listing Dependent and Depending Objects . . . . . . . . . . . . . . . . . . . . . . 80
The Role of Stored Procedures in the Development of Database Applications . . . . . . . . . 82
Enforcement of Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Consistent Implementation of Complex Business Rules and Constraints . . . . . . . . 83
Modular Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Maintainability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Reduced Network Traffic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Faster Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Enforcement of Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
Chapter 4 Basic Transact-SQL Programming Constructs . . . . . . . . . . . . . . . . 85
T-SQL Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Database Object Qualifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
viii SQL Server 2000 Stored Procedure & XML Programming
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Character Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Unicode Character Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Date and Time Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Integer Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Approximate Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Exact Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Monetary Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
Binary Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Special Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Local Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Global Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Table Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
Flow-Control Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Statement Blocks: Begin…End . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Conditional Execution: The If Statement . . . . . . . . . . . . . . . . . . . . . . . 112
Looping: The While Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . 117
Unconditional Execution: The GoTo Statement . . . . . . . . . . . . . . . . . . . . 119
Scheduled Execution: The WaitFor Statement . . . . . . . . . . . . . . . . . . . . 121
Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Transact-SQL Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
Cursor-Related Statements and Functions . . . . . . . . . . . . . . . . . . . . . . 126
Problems with Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
The Justified Uses of Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Chapter 5 Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Using Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
In Selection and Assignment . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
As Part of the Selection Criteria . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
In Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
As Check and Default Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Instead of Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Types of Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164
Rowset Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Contents i x
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Chapter 6 Composite Transact-SQL Constructs: Batches,
Scripts, and Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Batches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Using Batches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Batches and Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
DDL Batches . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Self-Sufficient Content . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Database Scripting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Autocommit Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Explicit Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
Implicit Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Transaction Processing Architecture . . . . . . . . . . . . . . . . . . . . . . . . . 186
Nested Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Named Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
Distributed Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Typical Locking Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Chapter 7 Debugging and Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . 207
Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
What Is a “Bug”? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208
The Debugging Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Debugging Tools and Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . 212
SQL Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227
Typical Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Raiserror . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Using Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Why Bother? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Tactics of Error Handling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
A Coherent Error Handling Methodology . . . . . . . . . . . . . . . . . . . . . . 243
Xact_Abort . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
Another Coherent Error Handling Methodology . . . . . . . . . . . . . . . . . . . 252
x SQL Server 2000 Stored Procedure & XML Programming
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Chapter 8 Special Types of Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . 259
Types of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
User-Defined Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
System Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
Extended Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Temporary Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
Global Temporary Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . 270
Remote Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Design of User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 271
Table-Valued User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . 276
Inline Table-Valued User-Defined Functions . . . . . . . . . . . . . . . . . . . . . 279
Managing User-Defined Functions in Enterprise Manager . . . . . . . . . . . . . . 281
Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283
Physical Design of After Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . 284
Handling Changes on Multiple Records . . . . . . . . . . . . . . . . . . . . . . . 291
Nested and Recursive Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Trigger Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Instead-of Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 294
Triggers on Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296
Trigger Order of Execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Managing Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Trigger Design Recommendations . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Transaction Management in Triggers . . . . . . . . . . . . . . . . . . . . . . . . 304
Using Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Design of Standard SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
Dynamic Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
INFORMATION_SCHEMA Views . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Partitioned Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
Using SQL Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Chapter 9 Advanced Stored Procedure Programming . . . . . . . . . . . . . . . . . . 341
Dynamically Constructed Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Executing a String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342
Contents x i
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Query By Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Data Script Generator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 347
Using the sp_executesql Stored Procedure . . . . . . . . . . . . . . . . . . . . . 351
Security Implications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353
Optimistic Locking Using timestamp Values . . . . . . . . . . . . . . . . . . . . . . . . . 356
timestamp . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357
TSEqual() Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 359
timestamp Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Full-Text Search and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363
Nested Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 365
Using Temporary Tables to Pass a Recordset to a Nested Stored Procedure . . . . . . 365
Using a Cursor to Pass a Recordset to a Nested Stored Procedure . . . . . . . . . . 368
How to Process the Result Set of a Stored Procedure . . . . . . . . . . . . . . . . . 371
Using Identity Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
A Standard Problem and Solution . . . . . . . . . . . . . . . . . . . . . . . . . . 378
Identity Values and Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 379
Last Identity Value in the Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . 380
GUIDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
A While Loop with Min() or Max() Functions . . . . . . . . . . . . . . . . . . . . . . . . 383
Looping with sp_MSForEachTable and sp_MSForEachDb . . . . . . . . . . . . . . . . . . . 385
Property Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Chapter 10 Interaction with the SQL Server Environment . . . . . . . . . . . . . . . . 389
Execution of OLE Automation/COM Objects . . . . . . . . . . . . . . . . . . . . . . . . . 390
Data Type Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393
Running Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Running Windows Script Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Running/Looping Through DTS Packages . . . . . . . . . . . . . . . . . . . . . . . . . . 395
Interacting with the NT Registry . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
xp_regread . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
xp_regwrite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399
Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
Administration of Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400
An Alternative to Job Scheduler . . . . . . . . . . . . . . . . . . . . . . . . . . . 404
Stored Procedures for Maintaining Jobs . . . . . . . . . . . . . . . . . . . . . . . 405
Operators and Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 406
xii SQL Server 2000 Stored Procedure & XML Programming
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
SQL Server and the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Web Assistant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Web Task Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 411
Web Page Templates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
E-Mail . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
Extended Stored Procedures for Working with E-Mail . . . . . . . . . . . . . . . . 416
Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Security Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
Implementing Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Synchronization of Login and Usernames . . . . . . . . . . . . . . . . . . . . . . 430
Managing Application Security Using Stored Procedures,
User-Defined Functions, and Views . . . . . . . . . . . . . . . . . . . . . . . 432
Managing Application Security Using a Proxy User . . . . . . . . . . . . . . . . . 434
Managing Application Security Using Application Roles . . . . . . . . . . . . . . . 436
Chapter 11 Source Code Management and Database Deployment . . . . . . . . . . . 437
The Concept of Source Code Management . . . . . . . . . . . . . . . . . . . . . . . . . . 438
Introduction to Microsoft Visual SourceSafe . . . . . . . . . . . . . . . . . . . . . 439
Administering the Visual SourceSafe Database . . . . . . . . . . . . . . . . . . . 440
Adding Database Objects to Visual SourceSafe in Visual Studio .NET . . . . . . . . . 440
Managing Create Scripts in Visual Studio .NET . . . . . . . . . . . . . . . . . . . . 443
Visual SourceSafe Explorer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Adding Database Objects to Visual SourceSafe: Traditional Approach . . . . . . . . . 451
Database Deployment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
Deployment of a Complete Database: Traditional Approach . . . . . . . . . . . . . 453
Deployment of Individual Objects . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Chapter 12 Stored Procedures for Web Search Engines . . . . . . . . . . . . . . . . . 467
Characteristics of the Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
A Simple Solution... . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468
...and Its Disadvantages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Available Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Result Splitting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Quick Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
Advanced Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486
Contents xiii
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:54 PM
Color profile: Generic CMYK printer profile
Composite Default screen
Chapter 13 Introduction to XML for Database Developers . . . . . . . . . . . . . . . 489
XML (R)evolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490
Introduction to XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492
Introduction to Markup Languages . . . . . . . . . . . . . . . . . . . . . . . . . 492
Building Blocks of Markup Languages . . . . . . . . . . . . . . . . . . . . . . . . 492
XML Elements and Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
Processing Instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Document Type Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
XML Comments and CDATA sections . . . . . . . . . . . . . . . . . . . . . . . . . 496
Character and Entity References . . . . . . . . . . . . . . . . . . . . . . . . . . 497
XML Namespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 498
Structure of XML Documents . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499
XML Parsers and DOM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
XML Document Quality . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501
XML Schema and XML Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . 501
XML–Data Reduced (XDR) Schema . . . . . . . . . . . . . . . . . . . . . . . . . 502
XML Schema (XSD) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Linking and Querying in XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
XPointer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519
XPath . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 520
Transforming XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522
XSL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
XSLT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Why XML? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524
Exchange of Information Between Organizations . . . . . . . . . . . . . . . . . . 524
Information Publishing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Chapter 14 Publishing Information Using SQLXML . . . . . . . . . . . . . . . . . . . . 527
For XML Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
Auto Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
The Elements Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
The XMLData Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
The BINARY Base64 Option . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Raw Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Explicit Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538
xiv SQL Server 2000 Stored Procedure & XML Programming
D_Base / SQL Server 2000 Stored Procedure & XML Programming / Sunderic / 222896-2 / Front Matter
P:\010Comp\D_Base\896-2\fm.vp
Wednesday, April 30, 2003 12:13:55 PM
Color profile: Generic CMYK printer profile
Composite Default screen