Pro T-SQL Programmer's Guide, 4th Edition

818 Pages·2015·24.56 MB·English

by  Miguel Cebollero

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 [phone], e-mail orders-ny@springer-
sbm.com, or visit www.springeronline.com.
For information on translations, please e-mail [e-mail], 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