Introduction to T-SQL Querying

1.  Introducing T-SQL
2.  Understanding Sets
3.  Understanding Predicate Logic
4.  Understanding the Logical Order of Operations in SELECT Statements
5.  Lab:             Introduction to T-SQL Querying

Writing SELECT Queries

1.  Writing Simple SELECT Statements
2. Eliminating Duplicates with DISTINCT
3. Using Column and Table Aliases
4. Writing Simple CASE Expressions
5. Lab:             Writing Basic SELECT Statements

Querying Multiple Tables

1.  Understanding Joins
2.  Querying with Inner Joins
3.  Querying with Outer Joins
4.  Querying with Cross Joins and Self Joins
5. Lab:     Querying Multiple Tables

Sorting and Filtering Data

1. Sorting Data
2. Filtering Data with Predicates
3. Filtering Data with TOP and OFFSET-FETCH
4. Working with Unknown Values
5. Lab:     Sorting and Filtering Data

  Working with SQL Server Data Types

1. Introducing SQL Server Data Types
2. Working with Character Data
3. Working with Date and Time Data
4. Lab:     Working with SQL Server 2022 Data Types

Using DML to Modify Data

1. Adding Data to Tables
2. Modifying and Removing Data
3. Generating Automatic Column Values
4. Lab:     Using DML to Modify Data

 Using Built-In Functions

1. Writing Queries with Built-In Functions
2. Using Conversion Functions
3. Using Logical Functions
4. Using Functions to Work with NULL
5. Lab:      Using Built-in Functions

 Grouping and Aggregating Data

1. Using Aggregate Functions
2. Using the GROUP BY Clause
3. Filtering Groups with HAVING
4. Lab:     Grouping and Aggregating Data

 Using Subqueries

1. Writing Self-Contained Subqueries
2. Writing Correlated Subqueries
3. Using the EXISTS Predicate with Subqueries
4. Lab:     Using Subqueries

Using Table Expressions

1. Using Views
2. Using Inline TVFs
3. Using Derived Tables
4. Using CTEs
5. Lab:     Using Table Expressions

 Using Table Expressions

1. Using Views
2. Using Inline TVFs
3. Using Derived Tables
4. Using CTEs
5. Lab:     Using Table Expressions

Using Set Operators

1. Writing Queries with the UNION Operator
2. Using EXCEPT and INTERSECT
3. Using APPLY
4. Lab:     Using Set Operators

 Using Window Ranking, Offset, and Aggregate Functions

1. Creating Windows with OVER
2. Exploring Window Functions
3. Lab:     Using Window Ranking, Offset, and Aggregate Functions

 Pivoting and Grouping Sets

1. Writing Queries with PIVOT and UNPIVOT
2. Working with Grouping Sets
3. Lab:     Pivoting and Grouping Sets

 Executing Stored Procedures

1. Querying Data with Stored Procedures
2. Passing Parameters to Stored Procedures
3. Creating Simple Stored Procedures
4. Working with Dynamic SQL
5. Lab:     Executing Stored Procedures

 Implementing Error Handling

1. Implementing T-SQL Error Handling
2. Implementing Structured Exception Handling
3. Lab:     Implementing Error Handling

 Implementing Transactions

1. Transactions and the Database Engine
2. Controlling Transactions
3. Lab:     Implementing Transactions

Designing and Implementing Tables

1. Designing Tables
2. Data Types
3. Working with Schemas
4. Creating and Altering Tables
5. Lab:     Designing and Implementing Tables

 Advanced Table Designs

1. Partitioning Data
2. Compressing Data
3. Temporal Tables
4. Lab:     Using Advanced Table Designs

Introduction to Indexes

1. Core Indexing Concepts
2. Data Types and Indexes
3. Heaps, Clustered, and Non-clustered Indexes
4. Single Column and Composite Indexes
5. Lab:     Implementing Indexes

 Designing Optimized Index Strategies

1. Index Strategies
2. Managing Indexes
3. Execution Plans.
4. The Database Engine Tuning Advisor
5. Query Store
6. Lab:     Optimizing Indexes

 Columnstore Indexes

1. Introduction to Columnstore Indexes
2. Creating Columnstore Indexes
3. Working with Columnstore Indexes
4. Lab:     Using Columnstore Indexes

  Designing and Implementing Views

1. Introduction to Views
2. Creating and Managing Views
3. Performance Considerations for Views
4. Lab:     Designing and Implementing Views

 Designing and Implementing Stored Procedures

1. Introduction to Stored Procedures
2. Working with Stored Procedures
3. Implementing Parameterized Stored Procedures
4. Controlling Execution Context
5. Lab:     Designing and Implementing Stored Procedures

Designing and Implementing User-Defined Functions

1. Overview of Functions
2. Designing and Implementing Scalar Functions
3. Designing and Implementing Table-Valued Functions
4. Considerations for Implementing Functions
5. Alternatives to Functions
6. Lab:     Designing and Implementing User-Defined Functions

 Responding to Data Manipulation Via Triggers

1. Designing DML Triggers
2. Implementing DML Triggers
3. Advanced Trigger Concepts
4. Lab:     Responding to Data Manipulation by Using Triggers

 Using In-Memory Tables

1. Memory-Optimized Tables
2. Natively Compiled Stored Procedures
3. Lab:     Using In-Memory Database Capabilities

 Implementing Managed Code in SQL Server

1. Introduction to CLR Integration in SQL Server
2. Implementing and Publishing CLR Assemblies
3. Lab:     Implementing Managed Code in SQL Server

  Storing and Querying XML Data in SQL Server

1. Introduction to XML and XML Schemas
2. Storing XML Data and Schemas in SQL Server
3. Implementing the XML Data Type
4. Using the Transact-SQL FOR XML Statement
5. Getting Started with XQuery
6. Shredding XML
7. Lab:     Storing and Querying XML Data in SQL Server

 Storing and Querying Spatial Data in SQL Server

1. Introduction to Spatial Data
2. Working with SQL Server Spatial Data Types
3. Using Spatial Data in Applications
4. Lab:     Working with SQL Server Spatial Data          

 Storing and Querying BLOBs and Text Documents in SQL Server

1. Considerations for BLOB Data
2. Working with FILESTREAM
3. Using Full-Text Search
4. Lab:     Storing and Querying BLOBs and Text Documents in SQL Server

 Storing and Querying BLOBs and Text Documents in SQL Server

1. Considerations for BLOB Data
2. Working with FILESTREAM
3. Using Full-Text Search
4. Lab:     Storing and Querying BLOBs and Text Documents in SQL Server

 SQL Server Concurrency

1. Concurrency and Transactions
2. Locking Internals
3. Lab:     Concurrency and Transactions

 Performance and Monitoring

1. Extended Events
2. Working with Extended Events
3. Live Query Statistics
4. Optimize Database File Configuration
5. Metrics
6. Lab:     Monitoring, Tracing, and Baselining

What’s New in SQL Server Performance?

1. Operational Analytics
2. In-Memory OLTP Enhancements
3. Query Store
4. Live Query Statistics
5. Native JSON
6. Temporal Tables
7. Lab :    Implementing SQL Server 2016 Performance Improvements

 What’s New in SQL Server Security

1. Using Always Encrypted
2. Row-Level Security
3. Dynamic Data Masking
4. Lab :    SQL Server 2016 Security Improvements
   

What’s New in SQL Server Availability and Scalability?

1. Enhanced Always On Availability Groups
2. What’s New with tempdb?
3. Use Windows Server 2022 with SQL Server 2022
4. Lab :    Monitoring tempdb
   

What’s New in SQL Server Reporting and BI

1. Reporting Services Enhancements
2. Power BI Enhancements
3. Mobile Report Publisher
4. Lab :    Implementing Power BI
   

What’s New in SQL Server Data Access?

1. PolyBase
2. What’s New in Integration Services?
3. Working with SSIS and Azur
4. Lab :    Exploring the New Features of SQL Server Integrated Services (SSIS)
   

New and Enhanced Features in SQL Server OLAP

1. New and Enhanced Features in SQL Server OLAP
2. What’s New in SQL Server Analysis Services?
3. Lab :    OLAP with SQL Server
   

What’s New for SQL Server in the Cloud?

1. Stretch Database
2. Enhanced Backup to Azure
3. What’s New in Azure SQL Database?
4. Lab :    Using Stretch Database