Inside Microsoft SQL Server 2005 T-SQL Programming.doc

inside microsoft sql server 2005t-sql programminginside microsoft sql server 2005 t-sql programmingtable of contents1. table of contents62. back cover73. inside microsoft sql server 2005 t-sql programming84. foreword135. preface146. introduction166.1 system requirements166.2 installing sample databases176.3 updates176.4 code samples176.5 support for this book177. chapter 1: datatype-related problems, xml, and clr udts187.1 datetime datatypes187.2 character-related problems417.3 large objects477.4 implicit conversions517.5 clr-based user-defined types557.6 xml data type797.7 conclusion968. chapter 2: temporary tables and table variables968.1 temporary tables978.2 table variables1068.3 tempdb considerations1108.4 table expressions1118.5 comparison summary1128.6 summary exercise—relational division1138.7 conclusion1189. chapter 3: cursors1199.1 using cursors1209.2 cursor overhead1219.3 dealing with each row individually1229.4 order-based access1239.5 conclusion14610. chapter 4: dynamic sql14710.1 exec14810.2 sp_executesql15710.3 environmental settings16010.4 uses of dynamic sql16110.5 sql injection18110.6 conclusion18811. chapter 5: views18911.1 order by in a view19011.2 refreshing views19411.3 modular approach19611.4 updating views20611.5 view options21011.6 indexed views21411.7 conclusion21812. chapter 6: user-defined functions21812.1 some facts about udfs21912.2 scalar udfs22012.3 table-valued udfs24612.4 per-row udfs25912.5 conclusion26213. chapter 7: stored procedures26313.1 types of stored procedures26313.2 the stored procedure interface27213.3 resolution27813.4 compilations, recompilations, and reuse of execution plans27913.5 execute as29113.6 parameterizing sort order29213.7 dynamic pivot29813.8 clr stored procedures31013.9 conclusion31914. chapter 8: triggers31914.1 after triggers32014.2 instead of triggers34014.3 ddl triggers34914.4 clr triggers35714.5 conclusion36715. chapter 9: transactions36715.1 what are transactions?36815.2 locks37015.3 isolation levels37615.4 save points38515.5 deadlocks38715.6 conclusion39416. chapter 10: exception handling39416.1 exception handling in sql server 200539816.2 conclusion41317. chapter 11: service broker41317.1 dialog conversations41417.2 sample dialog43117.3 dialog security44217.4 routing and distribution44717.5 scenarios45717.6 where does service broker fit?45817.7 conclusion46018. appendix a: companion to clr routines46018.1 create the clrutilities database: sql server46018.2 development: visual studio46118.3 deployment and testing: visual studio and sql server48819. index50119.1 a50119.2 b50219.3 c50319.4 d50719.5 e51419.6 f51519.7 g51519.8 i51619.9 k-l51719.10 m51819.11 n51919.12 o52019.13 p52019.14 q52219.15 r52219.16 s52319.17 t52919.18 u53319.19 v53519.20 w53619.21 x53620. list of figures53721. list of tables53922. list of listings54323. list of sidebars5475451. table of contentsinside microsoft sql server 2005: t-sql programmingbyitzik ben-gan, dejan sarkaandroger woltermicrosoft press 2006 (532 pages)isbn:0735621977written by a t-sql guru, this thorough, hands-on reference for database developers and administrators focuses on language features and how they are interpreted and processed by the sql server execution engine.table of contents inside microsoft sql server 2005 t-sql programming (see 3.)foreword(see 4.)preface(see 5.)introduction(see 6.)chapter 1(see 7.)-datatype-related problems, xml, and clr udtschapter 2(see 8.)-temporary tables and table variableschapter 3(see 9.)-cursorschapter 4(see 10.)-dynamic sqlchapter 5(see 11.)-viewschapter 6(see 12.)-user-defined functionschapter 7(see 13.)-stored procedureschapter 8(see 14.)-triggerschapter 9(see 15.)-transactionschapter 10(see 16.)-exception handlingchapter 11(see 17.)-service brokerappendix a(see 18.)-companion to clr routinesindex (see 11.6)list of figures (see 20.)list of tables (see 21.)list of listings (see 22.)list of sidebars (see 23.)2. back coverthis thorough, hands-on reference for database developers and administrators delivers expert guidance on sophisticated uses of transact-sql (t-sql)one of the most familiar and powerful programming languages for sql server. written by a t-sql guru, this guide focuses on language features and how they are interpreted and processed by the sql server execution engine. youll get in-depth coverage of the sophisticated uses of t-sql, including triggers, user-defined functions, exception handling, and more. the book explains and compares solutions to database-development problems in both sql server 2000 and sql server 2005, discussing the new t-sql programming features added to sql server 2005 in detail. includes extensive code samples, table examples, and logic puzzles to help database developers and administrators understand the intricacies and help promote mastery of t-sql.about the authors itzik ben-gan is a mentor and founder of solid quality learning. a microsoft sql server mvp (most valuable professional) since 1999, itzik has delivered numerous training events around the world focused on t-sql querying, query tuning, and programming. itzik is the author of several books on microsoft sql server. he has written many articles for sql server magazine, as well as articles and white papers for msdn. itziks speaking engagements include tech ed, devweek, various sql user groups around the world, pass, sql server magazine connections, and solid quality learnings events, to name a few.since 1992, itzik has been involved in many projects covering various database and computer systemsrelated technologies. in addition to helping customers with their pressing needs, fixing their problems, optimizing their databases, teaching, and mentoring, itzik has helped developers and database administrators shift to a relational/set-based mindset, improving both the performance of their code and its maintainability. itziks main expertise is t-sql querying, query tuning, programming, and internals, but hes proficient in other database areas as well. in 1999, itzik founded the israeli sql server and olap user group, and he has been managing it since then.dejan sarkamcp (microsoft certified professional), mcdba (microsoft certified database administrator), mct (microsoft certified trainer), sql server mvp, solid quality learning mentoris a trainer and consultant working for many certified partners for learning solutions (cpls) centers and development companies in slovenia and other countries. in addition to providing professional training, he continuously works on online transaction processing (oltp), olap, and data mining projects, especially at the design stage. he is a regular speaker at some of the most important international conferences, such as teched, pass, and mct. he is also indispensable at regional microsoft technet meetings, the nt conference, which is the largest microsoft conference in central and eastern europe, and other events. he is the founder of the slovenian sql server users group. dejan sarka also developed two courses for solid quality learning: data modeling essentials and data mining with sql server 2005. roger wolter has close to 30 years experience in the computer industry, the last 7 years with microsoft. he is currently a program manager on the sql server team specializing in sql server service broker and sql server express. his projects at microsoft have included com+, sqlxml, the soap toolkit, sql server service broker, and sql express.steve kass is associate professor of mathematics and computer science at drew university in madison, new jersey. steve graduated from pomona college and holds a ph.d. in mathematics from the university of wisconsinmadison. he is also a microsoft sql server mvp.lubor kollar is a group program manager for sql server at microsoft. his team led the development of key enhancements to the relational engine, including some of the t-sql enhancements in sql server 2005.3. inside microsoft sql server 2005 t-sql programmingitzik ben-gan (solid quality learning)dejan sarkaroger wolterpublished by microsoft press a division of microsoft corporationone microsoft way redmond, washington 98052-6399 copyright 2006 by itzik ben-ganall rights reserved. no part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.isbn-13: 978-0-7356-2197-8isbn-10:0-7356-2197-7library of congress control number 2006924463printed and bound in the united states of america.1 2 3 4 5 6 7 8 9 qwt 1 0 9 8 7 6distributed in canada by h.b. fenn and company ltd.a cip catalogue record for this book is available from the british library.microsoft press books are available through booksellers and distributors worldwide. for further information about international editions, contact your local microsoft corporation office or contact microsoft press international directly at fax (425) 936-7329. visit our web site at /mspress. send comments to .microsoft, biztalk, excel, microsoft press, msdn, visio, visual basic, visual c#, visual studio, windows, windows nt, and windows server are either registered trademarks or trademarks of microsoft corporation in the united states and/or other countries. other product and company names mentioned herein may be the trademarks of their respective owners.the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious. no association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred.this book expresses the authors views and opinions. the information contained in this book is provided without any express, statutory, or implied warranties. neither the authors, microsoft corporation, nor its resellers, or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.acquisitions editor: ben ryanproject editor: kristine haugsethtechnical editor: steve kassindexers: tony ross and lee rosscopy editor: roger leblancbody part no. x11-97537 this book is dedicated to my grandparents in memory of:lazar finethe nobletamara ben-ganthe saintabraham ben-ganthe strong and courageousi miss you all so much and to rita, my nana, my friend, whom i love deeplyi wish us many more years together about the contributors itzik ben-gan itzik ben-gan is a mentor and founder of solid quality learning. a microsoft sql server mvp (most valuable professional) since 1999, itzik has delivered numerous training events around the world focused on t-sql querying, query tuning, and programming. itzik is the author of several books on microsoft sql server. he has written many articles for sql server magazine, as well as articles and white papers for msdn. itziks speaking engagements include tech ed, devweek, various sql user groups around the world, pass, sql server magazine connections, and solid quality learnings events, to name a few.since 1992, itzik has been involved in many projects covering various database and computer systemsrelated technologies. in addition to helping customers with their pressing needs, fixing their problems, optimizing their databases, teaching, and mentoring, itzik has helped developers and database administrators shift to a relational/set-based mindset, improving both the performance of their code and its maintainability. itziks main expertise is t-sql querying, query tuning, programming, and internals, but hes proficient in other database areas as well. in 1999, itzik founded the israeli sql server and olap user group, and he has been managing it since then.dejan sarka dejan sarkamcp (microsoft certified professional), mcdba (microsoft certified database administrator), mct (microsoft certified trainer), sql server mvp, solid quality learning mentoris a trainer and consultant working for many certified partners for learning solutions (cpls) centers and development companies in slovenia and other countries. in addition to providing professional training, he continuously works on online transaction processing (oltp), olap, and data mining projects, especially at the design stage. he is a regular speaker at some of the most important international conferences, such as teched, pass, and mct. he is also indispensable at regional microsoft technet meetings, the nt conference, which is the largest microsoft conference in central and eastern europe, and other events. he is the founder of the slovenian sql server users group. dejan sarka also developed two courses for solid quality learning: data modeling essentials and data mining with sql server 2005. roger wolter roger wolter has close to 30 years experience in the computer industry, the last 7 years with microsoft. he is currently a program manager on the sql server team specializing in sql server service broker and sql server express. his projects at microsoft have included com+, sqlxml, the soap toolkit, sql server service broker, and sql express.steve kass steve kass is associate professor of mathematics and computer science at drew university in madison, new jersey. steve graduated from pomona college and holds a ph.d. in mathematics from the university of wisconsinmadison. he is also a microsoft sql server mvp.lubor kollar lubor kollar is a group program manager for sql server at microsoft. his team led the development of key enhancements to the relational engine, including some of the t-sql enhancements in sql server 2005.acknowledgments most readers usually skip the acknowledgments section, and many authors usually make this section very short. i really dont want to judge anyone; i can only guess that authors think it might look kitsch if they exposed their emotions, or that doing so is awkward. well, many people contributed their heart and soul to these books, and i dont care how this section might seem. id like them to be recognized for their contribution!my deepest gratitude goes to all those who took part or contributed in any way to the books. some spent countless hours directly involved in the project, and some of them had an impact on me and my work that implicitly affected the books.to the guest authors lubor kollar, dejan sarka, and roger wolter: thanks for taking part in this project and adding your invaluable insight. its been an honor and a pleasure to work with you. lubor, your depth of knowledge and passion are a source of inspiration. dejko, my good friend, im constantly learning new things from you. i find your views of the database world simply fascinating. roger, i greatly appreciate the fact that you agreed to contribute to the books. service brokeryour babybrings an important dimension to sql server that was never there before. im sure that companies will find it extremely valuable, and im eager to see how the queuing technology will be implementedit has such great potential.to steve kass, the technical editor of the books: steve, i have no words to describe how much i value your contribution. youre simply brilliant and amazing, and one can only hope to have half the wit and logic that you were blessed with. remind me never to end up in a battle of wits with you. youve spent so much time on the project and provided so many insightful suggestions that i feel youve practically helped author the books. i hope that in future editions of the books you will take an official authoring role.to david campbell and lubor kollar who wrote the forewords: your work and achievements are a guiding light to many of us. sql server has grown up to be a mature and fascinating productone well worth dedicating our professional careers to and to focus our passion on. thank you both for agreeing to write the forewords. this is truly an honor! to all contributors, im looking forward to doing many future projects together. i, for one, have already started cooking up ideas for future editions of the books.many thanks to the team at microsoft press: ben ryan, kristine haugseth, roger leblanc, and probably numerous others who took part in the making of the books. ben, im sorry to have been such a pain, and for wanting to be involved in every small detail that i could. perfection and candor are my two guidelines, though one can only strive to achieve the former. i believe that by following this path, the end result can only improve, and regardless, i believe that theres no other path. thanks for being so attentive. kristine, you are simply great! devoted, professional, caring, and steering the project so elegantly. on a more personal level, i feel that ive earned a new friend. roger, i dont envy you for the countless hours you had to spend on editing the books. thanks for helping to improve their quality. and im sure there were many others at microsoft press who worked long hours behind the scenes to allow the books to see the light of day.id like to thank kalen delaney. kalens previous inside microsoft sql server books were a bible to me with regard to sql server internals, and im eager to read her new coverage of sql server 2005s internals in her new volumes. kalen was also the one who initially asked me to write the t-sql volumes now that the product has grown so large.many people provided extremely valuable