You are previewing Pro T-SQL 2012 Programmer's Guide, Third Edition.
O'Reilly logo
Pro T-SQL 2012 Programmer's Guide, Third Edition

Book Description

Pro T-SQL 2012 Programmer's Guide is every developer's key to making full use of SQL Server 2012's powerful, built-in Transact-SQL language. Discussing new and existing features, the book takes you on an expert guided tour of Transact-SQL functionality. Fully functioning examples and downloadable source code bring technically accurate and engaging treatment of Transact-SQL into your own hands. Step-by-step explanations ensure clarity, and an advocacy of best-practices will steer you down the road to success.

Transact-SQL is the language developers and DBAs use to interact with SQL Server. It's used for everything from querying data, to writing stored procedures, to managing the database. New features in T-SQL 2012 include full support for window functions, stored sequences, the ability to throw errors, data paging, and more. All these important new features are covered in this book. Developers and DBAs alike can benefit from the expressive power of Transact-SQL, and Pro T-SQL 2012 Programmer's Guide provides the gateway to success in applying this increasingly important database language to everyday business and technical tasks.

What you'll learn

  • Take full advantage of window functions and other new features in T-SQL 2012

  • Move business logic into the database via procedures and functions

  • Develop using advanced features such as encryption and full-text search

  • Store and retrieve XML data using XQuery and XPath

  • Debug and Optimize T-SQL Execution

  • Build ADO.NET Client Applications

Who this book is for

Pro T-SQL 2012 Programmer's Guide is written for SQL Server and Transact-SQL developers who want to implement best-practices and take full advantage of all that SQL Server has to offer. NET programmers will find the book helpful in showing how to run .NET code within SQL Server. Database administrators who need to write triggers and the occasional stored procedure will also benefit from the book.

Table of Contents

  1. Title
  2. Dedication
  3. Contents at a Glance
  4. Contents
  5. About the Authors
  6. About the Technical Reviewer
  7. Acknowledgments
  8. Introduction
  9. Chapter 1: Foundations of T-SQL
    1. A Short History of T-SQL
    2. Imperative vs. Declarative Languages
    3. SQL Basics
    4. Elements of Style
    5. Summary
  10. Chapter 2: Tools of the Trade
    1. SQL Server Management Studio
    2. The SQLCMD Utility
    3. SQL Server Data Tools
    4. SQL Profiler
    5. Extended Events
    6. SQL Server Integration Services
    7. The Bulk Copy Program
    8. SQL Server 2012 Books Online
    9. The AdventureWorks Sample Database
    10. Summary
  11. Chapter 3: Procedural Code and CASE Expressions
    1. Three-Valued Logic
    2. Control-of-Flow Statements
    3. The CASE Expression
    4. Cursors
    5. Summary
  12. Chapter 4: User-Defined Functions
    1. Scalar Functions
    2. Multistatement Table-Valued Functions
    3. Inline Table-Valued Functions
    4. Restrictions on User-Defined Functions
    5. Summary
  13. Chapter 5: Stored Procedures
    1. Introducing Stored Procedures
    2. Metadata Discovery
    3. Calling Stored Procedures
    4. Managing Stored Procedures
    5. Stored Procedures Best Practices
    6. Stored Procedure Example
    7. Recursion in Stored Procedures
    8. Table-Valued Parameters
    9. Temporary Stored Procedures
    10. Recompilation and Caching
    11. Summary
  14. Chapter 6: Triggers
    1. DML Triggers
    2. DDL Triggers
    3. Logon Triggers
    4. Summary
  15. Chapter 7: Encryption
    1. The Encryption Hierarchy
    2. Service Master Keys
    3. Database Master Keys
    4. Certificates
    5. Asymmetric Keys
    6. Symmetric Keys
    7. Encryption without Keys
    8. Hashing Data
    9. Extensible Key Management
    10. Transparent Data Encryption
    11. Summary
  16. Chapter 8: Common Table Expressions and Windowing Functions
    1. Common Table Expressions
    2. Window Functions
    3. Aggregate Functions, Analytic Functions, and the OVER Clause
    4. Analytic Function Examples
    5. Summary
  17. Chapter 9: Data Types and Advanced Data Types
    1. Basic Data Types
    2. The Uniqueidentifier Data Type
    3. The Hierarchyid Data Type
    4. Spatial Data Types
    5. FILESTREAM Support
    6. Summary
  18. Chapter 10: Full-Text Search
    1. FTS Architecture
    2. Creating Full-Text Catalogs and Indexes
    3. Full-Text Querying
    4. Thesauruses and Stoplists
    5. Stored Procedures and Dynamic Management Views and Functions
    6. Summary
  19. Chapter 11: XML
    1. Legacy XML
    2. FOR XML Clause
    3. The xml Data Type
    4. The xml Data Type Methods
    5. XSL Transformations
    6. Summary
  20. Chapter 12: XQuery and XPath
    1. XPath and FOR XML PATH
    2. XQuery and the xml Data Type
    3. UTF-16 Support
    4. Summary
  21. Chapter 13: Catalog Views and Dynamic Management Views
    1. Catalog Views
    2. Dynamic Management Views and Functions
    3. INFORMATION_SCHEMA Views
    4. Summary
  22. Chapter 14: CLR Integration Programming
    1. The Old Way
    2. The CLR Integration Way
    3. CLR Integration Assemblies
    4. User-Defined Functions
    5. Stored Procedures
    6. User-Defined Aggregates
    7. CLR Integration User-Defined Types
    8. Triggers
    9. Summary
  23. Chapter 15: .NET Client Programming
    1. ADO.NET
    2. The .NET SQL Client
    3. Parameterized Queries
    4. Nonquery, Scalar, and XML Querying
    5. SqIBulkCopy
    6. Multiple Active Result Sets
    7. LINQ to SQL
    8. From LINQ to Entity Framework
    9. Summary
  24. Chapter 16: Data Services
    1. SQL Server 2012 Express LocalDB
    2. Asynchronous Programming with ADO.NET 4.5
    3. ODBC for Linux
    4. JDBC
    5. Service Oriented Architecture and WCF Data Services
    6. Summary
  25. Chapter 17: Error Handling and Dynamic SQL
    1. Error Handling
    2. Debugging Tools
    3. Dynamic SQL
    4. The sp_executesql Stored Procedure
    5. Summary
  26. Chapter 18: Performance Tuning
    1. SQL Server Storage
    2. Indexes
    3. Summary
  27. Appendix A: Exercise Answers
    1. Chapter 1
    2. Chapter 2
    3. Chapter 3
    4. Chapter 4
    5. Chapter 5
    6. Chapter 6
    7. Chapter 7
    8. Chapter 8
    9. Chapter 9
    10. Chapter 10
    11. Chapter 11
    12. Chapter 12
    13. Chapter 13
    14. Chapter 14
    15. Chapter 15
    16. Chapter 16
    17. Chapter 17
    18. Chapter 18
  28. Appendix B: XQuery Data Types
  29. Appendix C: Glossary
  30. Appendix D: SQLCMD Quick Reference
    1. Command-Line Options
    2. Scripting Variables
    3. Commands
  31. Index