Foundations of Querying               

Understanding the Foundations of T-SQL               

Understanding Logical Query Processing 

2. Getting Started with the SE LECT Statement               

Using the FROM and SELECT Clauses

The FROM Clause

The SELECT Clause

Delimiting Identifiers               

Working with Data Types and Built-in Functions

Choosing the Appropriate Data Type

Choosing a Data Type for Keys

Date and Time Functions

Character Functions

CASE Expression and Related Functions

3. Filtering and Sorting Data               

Filtering Data with Predicates

Predicates, Three-Valued Logic, and Search Arguments

Combining Predicates

Filtering Character Data

Filtering Date and Time Data               

Sorting Data

Understanding When Order Is Guaranteed

Using the ORDER BY Clause to Sort Data               

Filtering Data with TOP and OFFSET-FETCH

Filtering Data with TOP

Filtering Data with OFFSET-FETCH

4. Combining Sets               

Using Joins

Cross Joins

Inner Joins

Outer Joins

Multi-Join Queries               

Using Subqueries, Table Expressions, and the APPLY

Operator

Subqueries

Table Expressions

APPLY               

Using Set Operators

UNION and UNION ALL

INTERSECT

EXCEPT

5. Grouping and Windowing               

Writing Grouped Queries

Working with a Single Grouping Set

Working with Multiple Grouping Sets               

Pivoting and Unpivoting Data

Pivoting Data

Unpivoting Data               

Using Window Functions

Window Aggregate Functions

Window Ranking Functions

Window Offset Functions

6. Creating Tables and Enforcing Data Integrity               

Creating and Altering Tables

Introduction

Creating a Table

Altering a Table

Choosing Table Indexes               

Enforcing Data Integrity

Using Constraints

Primary Key Constraints

Unique Constraints

Foreign Key Constraints

Check Constraints

Default Constraints

7. Designing and Creating Views, Inline Functions and Synonyms               

Designing and Implementing Views and Inline Functions

Views

Inline Functions               

Using Synonyms

Creating a Synonym

Comparing Synonyms with Other Database Objects

8. Inserting, Updating, and Deleting Data               

Inserting Data

INSERT VALUES

INSERT SELECT

INSERT EXEC

SELECT INTO               

Updating Data

UPDATE Statement

UPDATE Based on Join

Nondeterministic UPDATE

UPDATE and Table Expressions

UPDATE Based on a Variable

UPDATE All-at-Once               

Deleting Data

Sample Data

DELETE Statement

TRUNCATE Statement

DELETE Based on a Join

DELETE Using Table Expressions

9. Other Data Modification Aspects               

Using the Sequence Object and IDENTITY Column Property.

Using the IDENTITY Column Property

Using the Sequence Object               

Merging Data

Using the MERGE Statement               

Using the OUTPUT Option

Working with the OUTPUT Clause

INSERT with OUTPUT

DELETE with OUTPUT

UPDATE with OUTPUT

MERGE with OUTPUT

Composable DML

10. Designing and Implementing T-SQL Routines               

Designing and Implementing Stored Procedures

Understanding Stored Procedures

Executing Stored Procedures

Branching Logic

Developing Stored Procedures               

Implementing Triggers

DML Triggers

AFTER Triggers

INSTEAD OF Triggers

DML Trigger Functions               

Implementing User-Defined Functions

Understanding User-Defined Functions

Scalar UDFs

Table-Valued UDFs

Limitations on UDFs

UDF Options

UDF Performance Considerations

11. Implementing Transactions, Error Handling and Dynamic SQL               

Managing Transactions and Concurrency

Understanding Transactions

Types of Transactions

Basic Locking

Transaction Isolation Levels               

Implementing Error Handling

Detecting and Raising Errors

Handling Errors After Detection               

Using Dynamic SQL

Dynamic SQL Overview

SQL Injection

Using sp_executesql

12. Implementing Indexes and Statistics               

Implementing Indexes

Heaps and Balanced Trees

Implementing Nonclustered Indexes

Implementing Indexed Views               

Using Search Arguments

Supporting Queries with Indexes

Search Arguments               

Understanding Statistics

Auto-Created Statistics

Manually Maintaining Statistics

13. Understanding Cursors, Sets, and Temporary Tables               

Evaluating the Use of Cursor/Iterative Solutions vs. Set-Based Solutions

The Meaning of “Set-Based”

Iterations for Operations That Must Be Done Per Row

Cursor vs. Set-Based Solutions for Data Manipulation Tasks               

Using Temporary Tables vs. Table Variables

Scope

DDL and Indexes

Physical Representation in tempdb

Transactions

Statistics

14. Querying and Managing XML Data               

Returning Results As XML with FOR XML

Introduction to XML

Producing XML from Relational Data

Shredding XML to Tables               

Querying XML Data with XQuery

XQuery Basics

Navigation

FLWOR Expressions               

Using the XML Data Type

When to Use the XML Data Type

XML Data Type Methods

Using the XML Data Type for Dynamic Schema