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
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 2012 Books Online
The AdventureWorks Sample Database
Summary
Chapter 3: Procedural Code and CASE Expressions
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
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
Calling 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: Triggers
DML Triggers
When to Use DML Triggers
Auditing with DML Triggers
Nested and Recursive Triggers
The UPDATE() and COLUMNS_UPDATED() Functions
Triggers on Views
DDL Triggers
Logon Triggers
Summary
Chapter 7: Encryption
The Encryption Hierarchy
Service Master Keys
Database Master Keys
Certificates
Asymmetric Keys
Symmetric Keys
Encryption without Keys
Hashing Data
Extensible Key Management
Transparent Data Encryption
Summary
Chapter 8: Common Table Expressions and Windowing Functions
Common Table Expressions
Multiple Common Table Expressions
Recursive Common Table Expressions
Window 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 functions
FIRST_VALUE and LAST_VALUE
Summary
Chapter 9: Data Types and Advanced Data Types
Basic Data Types
Characters
The Max Data Types
Numerics
Date and Time Data Types
The Uniqueidentifier Data Type
The Hierarchyid Data Type
Hierarchyid Example
Hierarchyid Methods
Spatial Data Types
FILESTREAM Support
Enabling FILESTREAM Support
Creating FILESTREAM Filegroups
FILESTREAM-Enabling Tables
Accessing FILESTREAM Data
FileTable Support
Summary
Chapter 10: Full-Text Search
FTS Architecture
Creating Full-Text Catalogs and Indexes
Creating Full-Text Catalogs
Creating Full-Text Indexes
Full-Text Querying
The FREETEXT Predicate
The CONTAINS Predicate
The FREETEXTTABLE and CONTAINSTABLE Functions
Thesauruses and Stoplists
Stored Procedures and Dynamic Management Views and Functions
Statistical Semantics
Summary
Chapter 11: XML
Legacy XML
OPENXML
OPENXML Result Formats
FOR XML Clause
FOR XML RAW
FOR XML AUTO
FOR XML EXPLICIT
FOR XML PATH
The xml Data Type
Untyped xml
Typed xml
The xml Data Type Methods
The query Method
The value Method
The exist Method
The nodes Method
The modify Method
XML Indexes
XSL Transformations
Summary
Chapter 12: XQuery and XPath
XPath and FOR XML PATH
XPath Attributes
Columns without Names and Wildcards
Element Grouping
The data Function
XPath and NULL
The WITH XMLNAMESPACES Clause
Node Tests
XQuery and the xml Data Type
Expressions and Sequences
The query Method
Location Paths
Node Tests
Namespaces
Axis Specifiers
Dynamic XML Construction
XQuery Comments
Data Types
Predicates
Conditional Expressions (if then else)
Arithmetic Expressions
XQuery Functions
Constructors and Casting
FLWOR Expressions
UTF-16 Support
Summary
Chapter 13: Catalog Views and Dynamic Management Views
Catalog Views
Table and Column Metadata
Querying Permissions
Dynamic Management Views and Functions
Index Metadata
Session Information
Connection Information
Currently Executing SQL
Most Expensive Queries
Tempdb Space
Server Resources
Unused Indexes
Wait Stats
INFORMATION_SCHEMA Views
Summary
Chapter 14: CLR Integration Programming
The Old Way
The CLR Integration Way
CLR Integration Assemblies
User-Defined Functions
Stored Procedures
User-Defined Aggregates
Creating a Simple UDA
Creating an Advanced UDA
CLR Integration User-Defined Types
Triggers
Summary
Chapter 15: .NET Client Programming
ADO.NET
The .NET SQL Client
Connected Data Access
Disconnected Datasets
Parameterized Queries
Nonquery, Scalar, and XML Querying
SqIBulkCopy
Multiple Active Result Sets
LINQ to SQL
Using the Designer
Querying with LINQ to SQL
From LINQ to Entity Framework
Querying Entities
Summary
Chapter 16: Data Services
SQL Server 2012 Express LocalDB
Asynchronous Programming with ADO.NET 4.5
ODBC for Linux
JDBC
Service Oriented Architecture and WCF Data Services
Creating a WCF Data Service
Creating a WCF Data Service Consumer
Summary
Chapter 17: Error Handling and Dynamic SQL
Error Handling
Legacy Error Handling
The RAISERROR Statement
Try...Catch Exception Handling
TRY_PARSE, TRY_CONVERT, and TRY_CAST
Throw Statement
Debugging Tools
PRINT Statement Debugging
Trace Flags
SSMS Integrated Debugger
Visual Studio T-SQL Debugger
Dynamic SQL
The EXECUTE Statement
SQL Injection and Dynamic SQL
Troubleshooting Dynamic SQL
The sp_executesql Stored Procedure
Dynamic SQL and Scope
Client-Side Parameterization
Summary
Chapter 18: Performance Tuning
SQL Server Storage
Files and Filegroups
Space Allocation
Partitions
Data Compression
Sparse Columns
Indexes
Heaps
Clustered Indexes
Nonclustered Indexes
Filtered Indexes
Optimizing Queries
Reading Query Plans
Methodology
Waits
Extended Events
Summary
Appendix A: Exercise Answers
Chapter 1
Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16
Chapter 17
Chapter 18
Appendix B: XQuery Data Types
Appendix C: Glossary
ACID
Adjacency list model
ADO.NET Data Services
Anchor query
Application programming interface (API)
Assembly
Asymmetric encryption
Atomic data types, list data types, and union data types
Axis
Bulk Copy Program (BCP)
Catalog view
Certificate
Check constraint
Closed-world assumption (CWA)
Clustered index
Comment
Computed constructor
Content expression
Context item expression
Context node
Database encryption key
Database master key
Data domain
Data page
Datum
Empty sequence
Entity data model (EDM)
Extended Events (XEvents)
Extensible key management (EKM)
Extent
Extract, Transform, Load (ETL)
Facet
Filter expression
FLWOR expression
Foreign key constraint
Full-text catalog
Full-text index
Full-text search (FTS)
Functions and Operators (F&O)
General comparison
Geography Markup Language (GML)
Grouping set
Hash
Heap
Heterogeneous sequence
Homogenous sequence
Indirect recursion
Inflectional form
Initialization vector (IV)
Language Integrated Query (LINQ)
Location path
Logon triggers
Materialized path model
Multiple Active Result Sets (MARS)
Nested sets model
Node
Node comparison
Node test
Nonclustered index
Optional occurrence indicator
Object-relational mapping (O/RM)
Open-world assumption (OWA)
Parameterization
Path expression
Predicate
Predicate truth value
Primary expression
Query plan
Recompilation
Recursion
Row constructor
Scalar function
Searched CASE expression
Sequence
Server certificate
Service master key (SMK)
Shredding
Simple CASE expression
SOAP
Spatial data
Spatial index
SQL Server Data Tools
SQL injection
Step
Table type
Three-valued logic (3VL)
Transparent data encryption (TDE)
Untyped XML
User-defined aggregate (UDA)
User-defined type (UDT)
Value comparison
Well-formed XML
Well-known text (WKT)
Windowing functions
World Wide Web Consortium (W3C)
XML
XML Schema
XPath
XQuery
XQuery/XPath Data Model (XDM)
XSL
XSLT
Appendix D: SQLCMD Quick Reference
Command-Line Options
Scripting Variables
Commands
Index
