Advanced T-SQL Querying, Programming and Tuning for SQL Server 2012

We can host this training at your preferred location. Contact us!

Target Audience

This course is intended for:

T-SQL Programmers, DBAs, Data Scientists, Architects, and Analysts
Those that need to write or review T-SQL code in SQL Server 2012 to 2019, and have at least 1 years’ experience.

Before attending this course, it is recommended that students have at least one year of T-SQL querying and programming experience in SQL Server or Azure SQL Database.

Understand logical query processing
Understand SQL Server’s internal data structures
Be able to analyze and tune query performance
Be able to analyze query execution plans
Describe the changes in cardinality estimations in SQL Server 2014 and 2016
Be able to solve complex querying and programming problems
Think in terms of sets
Be able to compare set based and iterative solutions
Use window functions to improve your solutions
Handle date and time data including intervals
Create system-versioned temporal tables
Describe performance problems related to use of user defined functions and possible workarounds
Understand compilations, recompilations, execution plan caching and reuse
Understand transactions and concurrency aspects of database programming
Know how to handle hierarchical data and write recursive queries
Be able to migrate on-disk data to memory optimized data
Describe T-SQL enhancements in SQL Server 2012, 2014 and 2016

Module 01: Logical Query Processing

Logical Query Processing Order
Logical Query Processing Example
Phase Details
Quiz

Module 02: Query Tuning

Internals and Index Tuning
Quiz
New Cardinality Estimator
Temporary Tables
Sets vs. Cursors
Query Tuning with Query Revisions

Module 03 – Multi-Table Queries

Subqueries and Table Expressions
APPLY Operator
Joins
Set Operators
LAB 03

Module 04: Grouping, Pivoting and Windowing

Window Functions
Pivoting and Unpivoting Data
Custom Aggregations
STRING_AGG
Grouping Sets (bonus self-study unit)
LAB 04

Module 05: TOP and OFFSET-FETCH

TOP
OFFSET-FETCH
Top N Per Group
LAB 05

Module 06: Data Modification

Inserting Data
Sequences
Deleting Data
Updating Data
Merging Data
The OUTPUT Clause
LAB 06

Module 07 – Working with Date and Time

Date and Time Datatypes
Date and Time Functions
Date and Time Challenges
System-Versioned Temporal Tables
Date and Time Querying Problems
LAB 07

Module 08: Programmable Objects

Dynamic SQL
User Defined Functions
Stored Procedures
Triggers
Transactions and Concurrency
Exception Handling
LAB 08

Module 09: In-Memory OLTP

Intro to In-Memory OLTP
Architecture
Memory Optimized Tables and Indexes
Natively Compiled Procedures
Transaction Semantics
LAB 09

Appendix A: Graphs and Recursive Queries (Bonus Self-Study Material)

Graphs, Described
Materialized Paths
Custom
Using the HIERARCHYID datatype
Nested Sets
Nested Iterations
Loops
Recursive Queries
SQL Graph
LAB A

Upcoming Trainings

Join our public courses in our Singapore facilities. Private class trainings will be organized at the location of your preference, according to your schedule.

Classroom / Virtual Classroom

09 August 2024