Code Centric: T-SQL Programming with Stored Procedures and Triggers

704 Pages·2001·25.69 MB·English

by  Garth Wells (auth.)

Code Centric: T-SOL
...
Programming with Stored
Procedures and Triggers
GARTH WELLS
APress Media, LLC
Code Centric: T-SQL Programming with Stored Procedures and Triggers
Copyright ©2001 by Garth Wells
Originally published by Apress in 2001
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 stor
age or retrieval system, without the prior written permission of the copyright owner and the
publisher.
ISBN 978-1-893115-83-5 ISBN 978-1-4302-1145-7 (eBook)
DOI 10.1007/978-1-4302-1145-7
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.
Editorial Directors: Dan Appleman, Gary Cornell, Karen Watterson
Technical Editor: Diane Brockman
Projects Manager: Grace Wong
Developmental Editor: Martin Minner
Copy Editors: Kristen Brown, Kari Brooks
Production Editor: Kari Brooks
Page Composition: Tony Jonick, Rappid Habbit Publishing
Indexer: Carol Burbo
Artist: Karl Miyajima
Part Opener Design: Tony Jonick, Happid Rabbit Publishing
Cover: Derek Yee. Derek Yee Design
In the United States, phone 1-800-SPRINGER; orders@springer-ny. com;
http://www.springer-ny.com
Outside the United States, contact orders@springer. de; http: I lwww. springer. de;
fax [phone]
For information on translations, please contact Apress directly at 901 Grayson Street, Suite 204,
Berkeley, CA, 94710
l'hone:510-549-5937;Fax:[phone]; [email protected];http://www.apress.com
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 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.
For my daughter, Sara Nicole, who brings me an immeasurable amount of joy
Contents at a Glance
Foreword ............................................................................................................ xvii
Preface ................................................................................................................ xix
Acknowledgments ............................................................................................. xxiii
Part I Transact-SQL Basics ................................................................ 1
Chapter 1 Transact-SQL Overview ............................................................ 3
Chapter 2 Data Types ................................................................................. 19
Chapter 3 Data Definition Language ................................................... 35
Chapter 4 DML: SELECT, JOINs, and Control-of-Flow .................... 71
Chapter 5 DML: Inserting, Updating, and Deleting Data ......... 133
Chapter 6 Built-In Functions .............................................................. 149
Chapter 7 User-Defined Functions ...................................................... 193
Chapter 8 Views .......................................................................................... 213
Part II Procedures and Triggers .................................................... 239
Chapter 9 System Stored Procedures ................................................. 241
Chapter 10 Extended Stored Procedures ............................................. 293
Chapter 11 User-Defined Stored Procedures ..................................... 317
Chapter 12 Triggers ................................................................................... 383
Part III Putting SQL to Work ........................................................... 407
Chapter 13 Procedures and Triggers by Example ............................ 409
Chapter 14 Building an Application .................................................... 487
Appendix A HTML Primer ............................................................................. 531
Appendix B ASP Primer ............................................................................... 555
Appendix C Query Optimization and Application
Performance Primer ............................................................... 591
Appendix D XML Primer ............................................................................... 625
Index ................................................................................................................... 661
v
Contents
Foreword ...............................................................................................................x uii
Preface .................................................................................................................. xix
Acknowledgments ........................................................................................... xxiii
Part I Transact-SQL Basics ..............................................
1
Chapter 1 Transact-SQL Overview ................................................... 3
Before You Get Started ...................................................................................... 4
Query Analyzer ..................................................................................................... 4
Transact SQL .......................................................................................................... 10
ANSI versus T -SQL Example ............................................................................. 12
T -SQL Syntax Elements .. ............ ..... .. ....... ... .. ......... ........... ..... ........... ..... ........ ... 14
T -SQL DOL and DML ......................................................................................... 15
Naming Convention ............................................................................................... 16
Before You Go ........................................................................................................ 18
Chapter 2 Data Types ............................................................................. 19
Before You Get Started .................................................................................... 19
Unicode ............................................................................................................... 19
Collation in SQL Server 2000 ............................................................................. 21
Data Types ............................................................................................................... 21
Character Data Types (Non-Unicode) ............................................................. 22
Character Data Types (Unicode) ...................................................................... 24
Binary String Data Types ................................................................................... 25
Exact Numeric Data Types ................................................................................ 25
Approximate Numeric Data Types ................................................................... 27
Date Data Types ................................................................................................. 28
Identifier Data Types ......................................................................................... 28
Variant Data Types ............................................................................................. 29
Other Data Types ............................................................................................... 30
vii
Contents
User-Defined Data Types .................................................................................. 31
Implicit Conversion ........................................................................................... 32
Before You Go ........................................................................................................ 33
Chapter 3 Data Definition Language .......................................... 35
Before You Get Started ..................................................................................... 35
Object Ownership and Referencing .................................................................. 35
Permissions ......................................................................................................... 37
Data Definition Language (DOL) ................................................................... 39
CREATE DATABASE ........................................................................................... 39
ALTER DATABASE .............................................................................................. 47
DROP DATABASE ............................................................................................... 49
CREATE TABLE ................................................................................................... 50
ALTER TABLE ...................................................................................................... 57
CREATE INDEX ................................................................................................... 61
DROP INDEX ...................................................................................................... 69
Before You Go ........................................................................................................ 70
Chapter 4 DML: SELECT, JOINs, and
Control-of-Flow .................................................................
71
Before You Get Started ..................................................................................... 71
SELECT ........................................................................................................................ 73
SELECT Clauses .................................................................................................. 73
Selecting Data ..................................................................................................... 74
Additional SELECT Clauses and Operators ...................................................... 84
Table and Column Aliasing ............................................................................... 97
JOINs .......................................................................................................................... 99
INNER JOIN ...................................................................................................... 100
OUTER JOIN ..................................................................................................... 100
CROSS JOIN ...................................................................................................... 101
INNER JOIN Exan1ples ..................................................................................... 101
OUTER JOIN Exan1ples .................................................................................... 109
Subqueries ............................................................................................................. 112
Correlated Subqueries ..................................................................................... 115
Derived Tables .................................................................................................... 117
Control-of-Flow Statements .......................................................................... 119
Control-of-Flow Keywords .............................................................................. 121
viii
Contents
CURSORS ................................................................................................................... 128
Before You Go ...................................................................................................... 131
Chapter 5 DML: Inserting, Updating,
and Deleting Data .........................................................
133
Before You Get Started .................................................................................. 133
INSERT ..................................................................................................................... 135
Statement Permissions .................................................................................... 135
Inserting Data ................................................................................................... 136
UPDATE ..................................................................................................................... 141
Statement Permissions.................................................................................... 142
Updating Data.................................................................................................. 142
DELETE ..................................................................................................................... 145
Statement Permissions.................................................................................... 145
Deleting Data .................................................................................................... 146
Before You Go ...................................................................................................... 148
Chapter 6 Built-In Functions ....................................................... 149
Before You Get Started .................................................................................. 149
Deterministic versus Nondeterministic Functions ....................................... 150
Database Compatibility Levels ....................................................................... 150
Object Browser and Function Syntax ............................................................. 151
String Functions ............................................................................................... 152
ASCII .................................................................................................................. 152
CHAR ................................................................................................................. 154
CHARINDEX ..................................................................................................... 155
LEFT .................................................................................................................. 156
LEN .................................................................................................................... 156
LOWER .............................................................................................................. 156
LTRIM ............................................................................................................... 157
NCHAR .............................................................................................................. 158
PATINDEX ........................................................................................................ 158
REPLACE ........................................................................................................... 159
REVERSE ........................................................................................................... 160
RIGHT ............................................................................................................... 160
RTRIM ............................................................................................................... 161
STR .................................................................................................................... 162
SUBSTRING ...................................................................................................... 162
ix
Contents
UNICODE .......................................................................................................... 163
UPPER ............................................................................................................... 164
Date and Time Functions ................................................................................ 164
DATEADD ......................................................................................................... 165
DATEDIFF ......................................................................................................... 167
DATENAME ...................................................................................................... 168
DATEPART ........................................................................................................ 168
DAY .................................................................................................................... 170
GETDATEO ........................................................................................................ 170
GETUTCDATE() ................................................................................................ 171
MONTH ............................................................................................................. 171
YEAR .................................................................................................................. 171
Mathematical Functions ................................................................................... 172
CEILING ............................................................................................................ 172
EXP ..................................................................................................................... 172
FLOOR ............................................................................................................... 173
POWER .............................................................................................................. 173
RAND ................................................................................................................. 173
ROUND ............................................................................................................. 174
Aggregate Functions ......................................................................................... 176
AVG .................................................................................................................... 176
COUNT .............................................................................................................. 177
MAX ................................................................................................................... 178
MIN .................................................................................................................... 179
SUM ................................................................................................................... 179
System Functions ................................................................................................ 180
CASE .................................................................................................................. 180
CAST .................................................................................................................. 182
CONVERT .......................................................................................................... 183
COALESCE ........................................................................................................ 184
CURRENT_TIMESTAMP .................................................................................. 184
CURRENT_USER .............................................................................................. 185
DATALENGTH .................................................................................................. 185
@@ERROR ......................................................................................................... 186
@@IDENTITY .................................................................................................... 187
IDENTITY .......................................................................................................... 187
ISDATE .............................................................................................................. 188
ISNULL .............................................................................................................. 189
ISNUMERIC ...................................................................................................... 190
NEWID() ............................................................................................................ 190
@@ROWCOUNT ............................................................................................... 191
Before You Go ...................................................................................................... 191
X
Contents
Chapter 7 User-Defined Functions ............................................ 193
Before You Get Started .................................................................................. 193
User-Defined Functions .................................................................................. 194
Three Types of User-Defined Functions ........................................................ 195
CREATE FUNCTION ........................................................................................ 199
Inline Table-Valued Function ......................................................................... 207
Multi-Statement Table-Valued Functions ..................................................... 208
ALTER FUNCTION .................................................................................................... 208
Scalar Functions ............................................................................................... 209
Inline Table-Valued Functions ....................................................................... 209
Multi-Statement Table-Valued Functions ..................................................... 209
DROP FUNCTION ...................................................................................................... 210
Function Considerations ................................................................................ 210
Statement Permissions .................................................................................... 210
Nondeterministic Built-In Functions ............................................................. 211
TEXT IN ROW ................................................................................................... 211
Before You Go ...................................................................................................... 211
Chapter 8 Views ........................................................................................
213
Before You Get Started .................................................................................. 213
Views ........................................................................................................................ 214
CREATE VIEW ................................................................................................... 215
ALTER VIEW ..................................................................................................... 230
DROP VIEW ...................................................................................................... 234
Information Schema Views ............................................................................. 235
Before You Go ...................................................................................................... 238
Part II Procedures and Triggers .............................
239
Chapter 9 System Stored Procedures .......................................
241
Before You Get Started .................................................................................. 241
System Databases ............................................................................................ 242
System Tables ................................................................................................... 243
xi