Introduction to T-SQL Querying

? Introducing T-SQL

? Understanding Sets

? Understanding Predicate Logic

? Understanding the Logical Order of Operations in SELECT Statements

? Lab:             Introduction to T-SQL Querying

   Writing SELECT Queries

? Writing Simple SELECT Statements

? Eliminating Duplicates with DISTINCT

? Using Column and Table Aliases

? Writing Simple CASE Expressions

? Lab:             Writing Basic SELECT Statements

   Querying Multiple Tables

? Understanding Joins

? Querying with Inner Joins

? Querying with Outer Joins

? Querying with Cross Joins and Self Joins

? Lab:     Querying Multiple Tables

   Sorting and Filtering Data

? Sorting Data

? Filtering Data with Predicates

? Filtering Data with TOP and OFFSET-FETCH

? Working with Unknown Values

? Lab:     Sorting and Filtering Data

   Working with SQL Server Data Types

? Introducing SQL Server Data Types

? Working with Character Data

? Working with Date and Time Data

? Lab:     Working with SQL Server 2016 Data Types

   Using DML to Modify Data

? Adding Data to Tables

? Modifying and Removing Data

? Generating Automatic Column Values

? Lab:     Using DML to Modify Data

   Using Built-In Functions

? Writing Queries with Built-In Functions

? Using Conversion Functions

? Using Logical Functions

? Using Functions to Work with NULL

? Lab:      Using Built-in Functions

   Grouping and Aggregating Data

? Using Aggregate Functions

? Using the GROUP BY Clause

? Filtering Groups with HAVING

? Lab:     Grouping and Aggregating Data

   Using Subqueries

? Writing Self-Contained Subqueries

? Writing Correlated Subqueries

? Using the EXISTS Predicate with Subqueries

? Lab:     Using Subqueries

   Using Table Expressions

? Using Views

? Using Inline TVFs

? Using Derived Tables

? Using CTEs

? Lab:     Using Table Expressions

   Using Table Expressions

? Using Views

? Using Inline TVFs

? Using Derived Tables

? Using CTEs

? Lab:     Using Table Expressions

   Using Set Operators

? Writing Queries with the UNION Operator

? Using EXCEPT and INTERSECT

? Using APPLY

? Lab:     Using Set Operators

   Using Window Ranking, Offset, and Aggregate Functions

? Creating Windows with OVER

? Exploring Window Functions

? Lab:     Using Window Ranking, Offset, and Aggregate Functions

   Pivoting and Grouping Sets

? Writing Queries with PIVOT and UNPIVOT

? Working with Grouping Sets

? Lab:     Pivoting and Grouping Sets

   Executing Stored Procedures

? Querying Data with Stored Procedures

? Passing Parameters to Stored Procedures

? Creating Simple Stored Procedures

? Working with Dynamic SQL

? Lab:     Executing Stored Procedures

   Implementing Error Handling

? Implementing T-SQL Error Handling

? Implementing Structured Exception Handling

? Lab:     Implementing Error Handling

   Implementing Transactions

? Transactions and the Database Engine

? Controlling Transactions

? Lab:     Implementing Transactions

Designing and Implementing Tables

? Designing Tables

? Data Types

? Working with Schemas

? Creating and Altering Tables

? Lab:     Designing and Implementing Tables

   Advanced Table Designs

? Partitioning Data

? Compressing Data

? Temporal Tables

? Lab:     Using Advanced Table Designs

   Introduction to Indexes

? Core Indexing Concepts

? Data Types and Indexes

? Heaps, Clustered, and Non-clustered Indexes

? Single Column and Composite Indexes

? Lab:     Implementing Indexes

   Designing Optimized Index Strategies

? Index Strategies

? Managing Indexes

? Execution Plans.

? The Database Engine Tuning Advisor

? Query Store

? Lab:     Optimizing Indexes

   Columnstore Indexes

? Introduction to Columnstore Indexes

? Creating Columnstore Indexes

? Working with Columnstore Indexes

? Lab:     Using Columnstore Indexes

   Designing and Implementing Views

? Introduction to Views

? Creating and Managing Views

? Performance Considerations for Views

? Lab:     Designing and Implementing Views

   Designing and Implementing Stored Procedures

? Introduction to Stored Procedures

? Working with Stored Procedures

? Implementing Parameterized Stored Procedures

? Controlling Execution Context

? Lab:     Designing and Implementing Stored Procedures

   Designing and Implementing User-Defined Functions

?  Overview of Functions

? Designing and Implementing Scalar Functions

? Designing and Implementing Table-Valued Functions

? Considerations for Implementing Functions

? Alternatives to Functions

? Lab:     Designing and Implementing User-Defined Functions

   Responding to Data Manipulation Via Triggers

?  Designing DML Triggers

? Implementing DML Triggers

? Advanced Trigger Concepts

? Lab:     Responding to Data Manipulation by Using Triggers

   Using In-Memory Tables

?  Memory-Optimized Tables

? Natively Compiled Stored Procedures

? Lab:     Using In-Memory Database Capabilities

   Implementing Managed Code in SQL Server

?  Introduction to CLR Integration in SQL Server

? Implementing and Publishing CLR Assemblies

? Lab:     Implementing Managed Code in SQL Server

   Storing and Querying XML Data in SQL Server

?  Introduction to XML and XML Schemas

? Storing XML Data and Schemas in SQL Server

? Implementing the XML Data Type

? Using the Transact-SQL FOR XML Statement

? Getting Started with XQuery

? Shredding XML

? Lab:     Storing and Querying XML Data in SQL Server

   Storing and Querying Spatial Data in SQL Server

? Introduction to Spatial Data

? Working with SQL Server Spatial Data Types

? Using Spatial Data in Applications

? Lab:     Working with SQL Server Spatial Data          

   Storing and Querying BLOBs and Text Documents in SQL Server

? Considerations for BLOB Data

? Working with FILESTREAM

? Using Full-Text Search

? Lab:     Storing and Querying BLOBs and Text Documents in SQL Server

   Storing and Querying BLOBs and Text Documents in SQL Server

? Considerations for BLOB Data

? Working with FILESTREAM

? Using Full-Text Search

? Lab:     Storing and Querying BLOBs and Text Documents in SQL Server

   SQL Server Concurrency

? Concurrency and Transactions

? Locking Internals

? Lab:     Concurrency and Transactions

   Performance and Monitoring

? Extended Events

? Working with Extended Events

? Live Query Statistics

? Optimize Database File Configuration

? Metrics

? Lab:     Monitoring, Tracing, and Baselining

What’s New in SQL Server Performance?

? Operational Analytics

? In-Memory OLTP Enhancements

? Query Store

? Live Query Statistics

? Native JSON

? Temporal Tables

? Lab :    Implementing SQL Server 2016 Performance Improvements

  What’s New in SQL Server Security

? Using Always Encrypted

? Row-Level Security

? Dynamic Data Masking

? Lab :    SQL Server 2016 Security Improvements

  What’s New in SQL Server Availability and Scalability?

? Enhanced Always On Availability Groups

? What’s New with tempdb?

? Use Windows Server 2019 with SQL Server 2019

? Lab :    Monitoring tempdb

  What’s New in SQL Server Reporting and BI

? Reporting Services Enhancements

? Power BI Enhancements

? Mobile Report Publisher

? Lab :    Implementing Power BI

  What’s New in SQL Server Data Access?

? PolyBase

? What’s New in Integration Services?

? Working with SSIS and Azure

? Lab :    Exploring the New Features of SQL Server Integrated Services (SSIS)

  New and Enhanced Features in SQL Server OLAP

? New and Enhanced Features in SQL Server OLAP

? What’s New in SQL Server Analysis Services?

? Lab :    OLAP with SQL Server

 What’s New for SQL Server in the Cloud?

? Stretch Database

? Enhanced Backup to Azure

? What’s New in Azure SQL Database?

? Lab :    Using Stretch Database