www.it-ebooks.info Pro T-SQL Programmer’s Guide 4th Edition Miguel Cebollero Jay Natarajan Michael Coles www.it-ebooks.info Pro T-SQL Programmer’s Guide Copyright © 2015 by Miguel Cebollero, Jay Natarajan, and Michael Coles This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN-13 (pbk): 978-1-4842-0146-6 ISBN-13 (electronic): 978-1-4842-0145-9 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Lead Editor: Jonathan Gennick Technical Reviewer: Edgar Lanting Editorial Board: Steve Anglin, Gary Cornell, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing, Matt Wade, Steve Weiss Coordinating Editor: Jill Balzano Copy Editor: Tiffany Taylor Compositor: SPi Global Indexer: SPi Global Artist: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer- sbm.com, or visit www.springeronline.com. For information on translations, please e-mail [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales– eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers at www.apress.com/9781430245964. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. www.it-ebooks.info Contents at a Glance About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Foundations of T-SQL Chapter 2: Tools of the Trade Chapter 3: Procedural Code Chapter 4: User-Defined Functions Chapter 5: Stored Procedures Chapter 6: In-Memory Programming Chapter 7: Triggers Chapter 8: Encryption Chapter 9: Common Table Expressions and Windowing Functions Chapter 10: Data Types and Advanced Data Types Chapter 11: Full-Text Search Chapter 12: XML Chapter 13: XQuery and XPath Chapter 14: Catalog Views and Dynamic aent Views Chapter 15: .NET Client Programming Chapter 16: CLR Integration Programming Chapter 17: Data Services Chapter 18: Error Handling and Dynamic SQL Chapter 19: Performance Tuning Appendix A: Exercise Answers Appendix B: XQuery Data Types Appendix C: Glossary www.it-ebooks.info Appendix D: SQLCMD Quick Reference Index www.it-ebooks.info Contents About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Foundations of T-SQL A Short History of T-SQL Imperative vs. Declarative Languages SQL Basics Statements Databases Transaction Logs Schemas Tables Views Indexes Stored Procedures User-Defined Functions SQL CLR Assemblies Elements of Style Whitespace Naming Conventions One Entry, One Exit Defensive Coding The SELECT * Statement Variable Initialization Summary Chapter 2: Tools of the Trade SQL Server Management Studio IntelliSense Code Snippets Keyboard Shortcut Schemes T-SQL Debugging SSMS Editing Options www.it-ebooks.info Context-Sensitive Help Graphical Query Execution Plans Project-Management Features The Object Explorer The SQLCMD Utility SQL Server Data Tools SQL Profiler Extended Events SQL Server Integration Services The Bulk Copy Program SQL Server 2014 Books Online The AdventureWorks Sample Database Summary Chapter 3: Procedural Code Three-Valued Logic Control-of-Flow Statements The BEGIN and END Keywords The IF…ELSE Statement The WHILE, BREAK, and CONTINUE Statements The GOTO Statement The WAITFOR Statement The RETURN Statement The CASE Expression The Simple CASE Expression The Searched CASE Expression CASE and Pivot Tables The IIF Statement CHOOSE COALESCE and NULLIF Cursors Summary Chapter 4: User-Defined Functions Scalar Functions www.it-ebooks.info Recursion in Scalar User-Defined Functions Procedural Code in User-Defined Functions Multistatement Table-Valued Functions Inline Table-Valued Functions Restrictions on User-Defined Functions Nondeterministic Functions State of the Database Summary Chapter 5: Stored Procedures Introducing Stored Procedures Metadata Discovery Natively Compiled Stored Procedures Managing Stored Procedures Stored Procedures Best Practices Stored Procedure Example Recursion in Stored Procedures Table-Valued Parameters Temporary Stored Procedures Recompilation and Caching Stored Procedure Statistics Parameter Sniffing Recompilation Summary Chapter 6: In-Memory Programming The Drivers for In-Memory Technology Hardware Trends Getting Started with In-Memory Objects Step 1: Add a New Memory-Optimized Data FILEGROUP Step 2: Add a New Memory-Optimized Container Step 3: Create Your New Memory-Optimized Table Limitations on Memory-Optimized Tables www.it-ebooks.info In-Memory OLTP Table Indexes Hash Indexes Range Indexes Natively Compiled Stored Procedures Chapter 7: Triggers DML Triggers Multiple Triggers When to Use DML Triggers Inserted and Deleted Virtual Tables Auditing with DML Triggers Using Change Data Capture Instead Sharing Data with Triggers Nested and Recursive Triggers The UPDATE( ) and COLUMNS_UPDATED( ) Functions Triggers on Views DDL Triggers DDL Event Types and Event Groups Logon Triggers Summary Chapter 8: Encryption The Encryption Hierarchy Service Master Keys Database Master Keys Certificates Limitations of Asymmetric Encryption Asymmetric Keys Asymmetric Key “Backups” Symmetric Keys Temporary Symmetric Keys Salt and Authenticators Encryption Without Keys www.it-ebooks.info Hashing Data Extensible Key Management Transparent Data Encryption Summary Chapter 9: Common Table Expressions and Windowing Functions Common Table Expressions Multiple Common Table Expressions CTE Readability Benefits Recursive Common Table Expressions Windowing Functions ROW_NUMBER Function Query Paging with OFFSET/FETCH The RANK and DENSE_RANK Functions The NTILE Function Aggregate Functions, Analytic Functions, and the OVER Clause Analytic Function Examples CUME_DIST and PERCENT_RANK PERCENTILE_CONT and PERCENTILE_DISC LAG and LEAD FIRST_VALUE and LAST_VALUE Summary Chapter 10: Data Types and Advanced Data Types Basic Data Types Characters The Max Data Types Numerics Date and Time Data Types UTC and Military Time Date and Time Functions Time Zones and Offsets The Uniqueidentifier Data Type The Hierarchyid Data Type Representing Hierarchical Data Hierarchyid Example www.it-ebooks.info