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

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

Released March 2015
Publisher(s): Apress
ISBN: 9781484201459

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 Programmer’s Guide is your guide to making the best use of the powerful, Transact-SQL programming language that is built into Microsoft SQL Server's database engine. This edition is updated to cover the new, in-memory features that are part of SQL Server 2014. 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. Support for in-memory stored procedures running queries against in-memory tables is new in the language and gets coverage in this edition. Also covered are must-know features such as window functions and data paging that help in writing fast-performing database queries. Developers and DBAs alike can benefit from the expressive power of T-SQL, and Pro T-SQL Programmer's Guide is your roadmap to success in applying this increasingly important database language to everyday business and technical tasks.

  • Covers the newly-introduced, in-memory database features
  • Shares the best practices used by experienced professionals
  • Goes deeply into the subject matter - an advanced book for the serious reader
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Contents at a Glance
    5. Contents
    6. About the Authors
    7. About the Technical Reviewer
    8. Acknowledgments
    9. Introduction
    10. Chapter 1: Foundations of T-SQL
      1. A Short History of T-SQL
      2. Imperative vs. Declarative Languages
      3. SQL Basics
        1. Statements
        2. Databases
        3. Transaction Logs
        4. Schemas
        5. Tables
        6. Views
        7. Indexes
        8. Stored Procedures
        9. User-Defined Functions
        10. SQL CLR Assemblies
      4. Elements of Style
        1. Whitespace
        2. Naming Conventions
        3. One Entry, One Exit
        4. Defensive Coding
        5. The SELECT * Statement
        6. Variable Initialization
      5. Summary
    11. Chapter 2: Tools of the Trade
      1. SQL Server Management Studio
        1. IntelliSense
        2. Code Snippets
        3. Keyboard Shortcut Schemes
        4. T-SQL Debugging
        5. SSMS Editing Options
        6. Context-Sensitive Help
        7. Graphical Query Execution Plans
        8. Project-Management Features
        9. The Object Explorer
      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 2014 Books Online
      9. The AdventureWorks Sample Database
      10. Summary
    12. Chapter 3: Procedural Code
      1. Three-Valued Logic
      2. Control-of-Flow Statements
        1. The BEGIN and END Keywords
        2. The IF...ELSE Statement
        3. The WHILE, BREAK, and CONTINUE Statements
        4. The GOTO Statement
        5. The WAITFOR Statement
        6. The RETURN Statement
      3. The CASE Expression
        1. The Simple CASE Expression
        2. The Searched CASE Expression
        3. CASE and Pivot Tables
        4. The IIF Statement
        5. CHOOSE
        6. COALESCE and NULLIF
      4. Cursors
      5. Summary
    13. Chapter 4: User-Defined Functions
      1. Scalar Functions
        1. Recursion in Scalar User-Defined Functions
        2. Procedural Code in User-Defined Functions
      2. Multistatement Table-Valued Functions
      3. Inline Table-Valued Functions
      4. Restrictions on User-Defined Functions
        1. Nondeterministic Functions
        2. State of the Database
      5. Summary
    14. Chapter 5: Stored Procedures
      1. Introducing Stored Procedures
      2. Metadata Discovery
      3. Natively Compiled 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
        1. Stored Procedure Statistics
        2. Parameter Sniffing
        3. Recompilation
      11. Summary
    15. Chapter 6: In-Memory Programming
      1. The Drivers for In-Memory Technology
      2. Hardware Trends
      3. Getting Started with In-Memory Objects
        1. Step 1: Add a New Memory-Optimized Data FILEGROUP
        2. Step 2: Add a New Memory-Optimized Container
        3. Step 3: Create Your New Memory-Optimized Table
        4. Limitations on Memory-Optimized Tables
      4. In-Memory OLTP Table Indexes
        1. Hash Indexes
        2. Range Indexes
      5. Natively Compiled Stored Procedures
    16. Chapter 7: Triggers
      1. DML Triggers
        1. Multiple Triggers
        2. When to Use DML Triggers
        3. Inserted and Deleted Virtual Tables
        4. Auditing with DML Triggers
      2. Using Change Data Capture Instead
        1. Sharing Data with Triggers
        2. Nested and Recursive Triggers
        3. The UPDATE() and COLUMNS_UPDATED() Functions
      3. Triggers on Views
      4. DDL Triggers
        1. DDL Event Types and Event Groups
      5. Logon Triggers
      6. Summary
    17. Chapter 8: Encryption
      1. The Encryption Hierarchy
      2. Service Master Keys
      3. Database Master Keys
      4. Certificates
      5. Limitations of Asymmetric Encryption
      6. Asymmetric Keys
        1. Asymmetric Key “Backups”
      7. Symmetric Keys
        1. Temporary Symmetric Keys
      8. Salt and Authenticators
      9. Encryption Without Keys
        1. Hashing Data
      10. Extensible Key Management
      11. Transparent Data Encryption
      12. Summary
    18. Chapter 9: Common Table Expressions and Windowing Functions
      1. Common Table Expressions
        1. Multiple Common Table Expressions
        2. CTE Readability Benefits
      2. Recursive Common Table Expressions
      3. Windowing Functions
        1. ROW_NUMBER Function
        2. Query Paging with OFFSET/FETCH
        3. The RANK and DENSE_RANK Functions
        4. The NTILE Function
      4. Aggregate Functions, Analytic Functions, and the OVER Clause
      5. Analytic Function Examples
        1. CUME_DIST and PERCENT_RANK
        2. PERCENTILE_CONT and PERCENTILE_DISC
        3. LAG and LEAD
        4. FIRST_VALUE and LAST_VALUE
      6. Summary
    19. Chapter 10: Data Types and Advanced Data Types
      1. Basic Data Types
        1. Characters
        2. The Max Data Types
        3. Numerics
        4. Date and Time Data Types
        5. UTC and Military Time
        6. Date and Time Functions
        7. Time Zones and Offsets
      2. The Uniqueidentifier Data Type
      3. The Hierarchyid Data Type
        1. Representing Hierarchical Data
        2. Hierarchyid Example
        3. Hierarchyid Methods
      4. Spatial Data Types
        1. Hemisphere and Orientation
        2. Michigan and the Great lakes
      5. FILESTREAM Support
        1. Enabling FILESTREAM Support
        2. Creating FILESTREAM Filegroups
        3. FILESTREAM-Enabling Tables
        4. Accessing FILESTREAM Data
        5. FileTable Support
        6. Filetable Functions
        7. Triggers on Filetables
      6. Summary
    20. Chapter 11: Full-Text Search
      1. FTS Architecture
        1. Creating Full-Text Catalogs and Indexes
        2. Creating Full-Text Catalogs
        3. Creating Full-Text Indexes
        4. Full-Text Querying
        5. The FREETEXT Predicate
        6. FTS Performance Optimization
        7. The CONTAINS Predicate
        8. The FREETEXTTABLE and CONTAINSTABLE Functions
        9. Thesauruses and Stoplists
        10. Stored Procedures and Dynamic Management Views and Functions
        11. Statistical Semantics
      2. Summary
    21. Chapter 12: XML
      1. Legacy XML
      2. OPENXML
      3. OPENXML Result Formats
      4. FOR XML Clause
      5. FOR XML RAW
      6. FOR XML AUTO
      7. FOR XML EXPLICIT
      8. FOR XML PATH
      9. The xml Data Type
      10. Untyped xml
      11. Typed xml
      12. The xml Data Type Methods
      13. The query Method
      14. The value Method
      15. The exist Method
      16. The nodes Method
      17. The modify Method
      18. XML Indexes
      19. XSL Transformations
      20. SQL CLR Security Settings
      21. Summary
    22. Chapter 13: XQuery and XPath
      1. XPath and FOR XML PATH
        1. XPath Attributes
        2. Columns without Names and Wildcards
        3. Element Grouping
        4. The data Function
        5. Node Tests and Functions
        6. XPath and NULL
        7. The WITH XMLNAMESPACES Clause
        8. Node Tests
        9. XQuery and the xml Data Type
        10. Expressions and Sequences
        11. The query Method
        12. Location Paths
        13. Node Tests
        14. Namespaces
        15. Axis Specifiers
        16. Dynamic XML Construction
        17. XQuery Comments
        18. Data Types
        19. Predicates
        20. Value Comparison Operators
        21. General Comparison Operators
        22. Xquery Date Format
        23. Node Comparisons
        24. Conditional Expressions (if...then...else)
        25. Arithmetic Expressions
        26. Integer Division in XQuery
        27. XQuery Functions
        28. Constructors and Casting
        29. FLWOR Expressions
        30. The for and return Keywords
        31. The where Keyword
        32. The order by Keywords
        33. The let Keyword
        34. UTF-16 Support
      2. Summary
    23. Chapter 14: Catalog Views and Dynamic aent Views
      1. Catalog Views
        1. Table and Column Metadata
        2. Querying Permissions
      2. Dynamic Management Views and Functions
        1. Index Metadata
        2. Session Information
        3. Connection Information
        4. Currently Executing SQL
        5. Memory-Optimized System Views
        6. Most Expensive Queries
        7. Tempdb Space
        8. Server Resources
        9. Unused Indexes
        10. Wait Stats
      3. INFORMATION_SCHEMA Views
      4. Summary
    24. Chapter 15: .NET Client Programming
      1. ADO.NET
      2. The .NET SQL Client
      3. Connected Data Access
      4. Disconnected Datasets
      5. Parameterized Queries
      6. Nonquery, Scalar, and XML Querying
      7. SqIBulkCopy
      8. Multiple Active Result Sets
      9. LINQ to SQL
      10. Using the Designer
      11. Querying with LINQ to SQL
      12. Basic LINQ to SQL Querying
      13. Deferred Query Execution
      14. From LINQ to Entity Framework
      15. Querying Entities
      16. Summary
    25. Chapter 16: 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
        1. Creating a Simple UDA
        2. Creating an Advanced UDA
      7. CLR Integration User-Defined Types
      8. Triggers
      9. Summary
    26. Chapter 17: Data Services
      1. SQL Server 2014 Express LocalDB
      2. Asynchronous Programming with ADO.NET 4.5
      3. ODBC for Linux
      4. JDBC
      5. Service-Oriented Architecture and WCF Data ServicesService Oriented Architecture and WCF
      6. Creating a WCF Data Service
      7. Defining the Data Source
      8. Creating the Data Service
      9. Creating a WCF Data Service Consumer
      10. Summary
    27. Chapter 18: Error Handling and Dynamic SQL
      1. Error Handling
      2. Legacy Error Handling
      3. The RAISERROR Statement
      4. Try...Catch Exception Handling
        1. TRY_PARSE, TRY_CONVERT, and TRY_CAST
      5. Throw Statement
      6. Debugging Tools
        1. PRINT Statement Debugging
        2. Trace Flags
        3. SSMS Integrated Debugger
        4. Visual Studio T-SQL Debugger
      7. Dynamic SQL
        1. The EXECUTE Statement
        2. SQL Injection and Dynamic SQL
        3. Troubleshooting Dynamic SQL
      8. The sp_executesql Stored Procedure
        1. Dynamic SQL and Scope
        2. Client-Side Parameterization
      9. Summary
    28. Chapter 19: Performance Tuning
      1. SQL Server Storage
        1. Files and Filegroups
        2. Space Allocation
        3. Partitions
        4. Data Compression
        5. Sparse Columns
      2. Indexes
        1. Heaps
        2. Clustered Indexes
        3. Nonclustered Indexes
        4. Filtered Indexes
        5. Optimizing Queries
        6. Reading Query Plans
        7. Methodology
        8. Waits
        9. Extended Events
      3. Summary
    29. 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
      19. Chapter 19
    30. Appendix B: XQuery Data Types
    31. Appendix C: Glossary
      1. ACID
      2. adjacency list model
      3. ADO.NET Data Services
      4. anchor query
      5. application programming interface (API)
      6. assembly
      7. asymmetric encryption
      8. atomic, list, and union data types
      9. axis
      10. Bulk Copy Program (BCP)
      11. catalog view
      12. certificate
      13. check constraint
      14. closed-world assumption (CWA)
      15. clustered index
      16. comment
      17. computed constructor
      18. content expression
      19. context item expression
      20. context node
      21. database encryption key
      22. database master key
      23. data domain
      24. data page
      25. datum
      26. empty sequence
      27. entity data model (EDM)
      28. Extended Events (XEvents)
      29. extensible key management (EKM)
      30. extent
      31. Extract, Transform, Load (ETL)
      32. facet
      33. filter expression
      34. FLWOR expression
      35. foreign key constraint
      36. full-text catalog
      37. full-text index
      38. full-text search (FTS)
      39. Functions and Operators (F&O)
      40. general comparison
      41. Geography Markup Language (GML)
      42. grouping set
      43. hash
      44. heap
      45. heterogeneous sequence
      46. homogenous sequence
      47. indirect recursion
      48. inflectional forms
      49. initialization vector (IV)
      50. Language Integrated Query (LINQ)
      51. location path
      52. logon trigger
      53. materialized path model
      54. Multiple Active Result Sets (MARS)
      55. nested sets model
      56. node
      57. node comparison
      58. node test
      59. nonclustered index
      60. object-relational mapping (O/RM)
      61. open-world assumption (OWA)
      62. optional occurrence indicator
      63. parameterization
      64. path expression
      65. predicate
      66. predicate truth value
      67. primary expression
      68. query plan
      69. recompilation
      70. recursion
      71. row constructor
      72. scalar function
      73. searched CASE expression
      74. sequence
      75. server certificate
      76. service master key (SMK)
      77. shredding
      78. simple CASE expression
      79. SOAP
      80. spatial data
      81. spatial index
      82. SQL Server Data Tools
      83. SQL injection
      84. step
      85. table type
      86. three-valued logic (3VL)
      87. transparent data encryption (TDE)
      88. untyped XML
      89. user-defined aggregate (UDA)
      90. user-defined type (UDT)
      91. value comparison
      92. well-formed XML
      93. well-known text (WKT)
      94. windowing functions
      95. World Wide Web Consortium (W3C)
      96. XML
      97. XML schema
      98. XPath
      99. XQuery
      100. XQuery/XPath Data Model (XDM)
      101. XSL
      102. XSLT
    32. Appendix D: SQLCMD Quick Reference
      1. Command-Line Options
      2. Scripting Variables
      3. Commands
    33. Index

    Product information

    • Title: Pro T-SQL Programmer’s Guide, 4th Edition
    • Author(s):
    • Release date: March 2015
    • Publisher(s): Apress
    • ISBN: 9781484201459