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 +49 6221 345229 For information on translations, please contact Apress directly at 901 Grayson Street, Suite 204, Berkeley, CA, 94710 l'hone:510-549-5937;Fax:510-549-5939; [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