Pro T-SQL 2012 Programmer's Guide, Third Edition

Pro T-SQL 2012 Programmer's Guide, Third Edition

Released September 2012
Publisher(s): Apress
ISBN: 9781430245964

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

    Product information

    • Title: Pro T-SQL 2012 Programmer's Guide, Third Edition
    • Author(s):
    • Release date: September 2012
    • Publisher(s): Apress
    • ISBN: 9781430245964