Advanced Transact SQL Training Course

This workshop is aimed at experienced SQL Developers, Database Administrators, Data Engineers, and Analysts who are comfortable with basic T-SQL and relational database concepts.

You should have attended our Intro to Transact SQL training course or have equivalent real-world experience.

This advanced T-SQL course is available for private / custom delivery for your team - as an in-house face-to-face workshop at your location of choice, or as online instructor-led training via MS Teams (or your own preferred platform).

Get in touch to find out how we can deliver tailored training which focuses on your project requirements and learning goals.

Performance Tuning and Optimization

• Understanding Query Execution Plans:

  • In-depth analysis of execution plans (Graphical and Text).

  • Identifying performance bottlenecks (scans, seeks, key lookups, etc.).

  • Using SET SHOWPLAN_ALL and SET STATISTICS IO/TIME.

• Index Strategies for Performance:

  • Advanced index types: Filtered Indexes, Columnstore Indexes (Clustered and Non-Clustered), Included Columns.

  • Index fragmentation and maintenance (rebuild vs. reorganize).

  • Index tuning for specific query patterns.

  • Monitoring index usage and identifying unused indexes.

• Query Hints and Their Impact:

  • Understanding different query hints (e.g., OPTIMIZE FOR, INDEX, JOIN).

  • When to use and when to avoid query hints.

  • Potential performance implications of using hints.

• Statistics Management:

  • Importance of up-to-date statistics.

  • Automatic vs. manual statistics updates.

  • Sampling rates and their impact.

  • Troubleshooting performance issues related to outdated statistics.

• Execution Context and Caching:

  • Understanding the procedure cache (plan cache).

  • Factors that affect plan reuse.

  • Identifying and addressing plan cache bloat.

  • Using DBCC FREEPROCCACHE (with caution).

• Parameter Sniffing Issues and Solutions:

  • Understanding parameter sniffing and its potential negative impact.

  • Strategies to mitigate parameter sniffing issues (e.g., OPTION (RECOMPILE), OPTIMIZE FOR UNKNOWN, local variables).

Advanced Querying Techniques

• Window Functions Deep Dive:

  • Understanding partitioning, ordering, and framing clauses.

  • Using various window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM() OVER(), AVG() OVER(), etc.

  • Practical applications of window functions for complex analysis.

• Common Table Expressions (CTEs):

  • Recursive CTEs for hierarchical data and complex traversals.

  • Using CTEs for improved query readability and modularity.

  • Optimizing CTE performance.

• Working with JSON and XML Data:

  • Querying and manipulating JSON data using OPENJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY.

  • Querying and manipulating XML data using XML data type methods.

  • Storing and indexing JSON and XML data effectively.

• Temporal Tables (System-Versioned Tables):

  • Understanding temporal tables and their use cases for auditing and point-in-time analysis.

  • Querying historical data using FOR SYSTEM_TIME.

  • Managing and configuring temporal tables.

• Graph Databases and T-SQL Extensions (Introduction):

  • Overview of graph database concepts.

  • Basic syntax for creating and querying graph objects (NODE, EDGE, MATCH).

  • Use cases for graph databases within SQL Server.

Programmability and Advanced Objects

• Advanced Stored Procedures:

  • Error handling using TRY...CATCH.

  • Transaction management within stored procedures.

  • Dynamic SQL and its security implications.

  • Using table-valued parameters (TVPs).

  • Optimizing stored procedure performance.

• User-Defined Functions (UDFs):

  • Scalar vs. table-valued functions (inline and multi-statement).

  • Performance considerations for UDFs.

  • Deterministic vs. non-deterministic functions.

• Triggers:

  • AFTER and INSTEAD OF triggers.

  • Understanding the inserted and deleted logical tables.

  • Use cases for triggers (auditing, data integrity).

  • Performance impact of triggers.

• Working with CLR Integration (Optional, depending on environment):

  • Overview of CLR integration.

  • Creating and deploying CLR stored procedures and functions.

  • Use cases and security considerations.

Concurrency and Isolation

• Understanding Transaction Isolation Levels:

  • Read Uncommitted, Read Committed, Repeatable Read, Serializable, Snapshot.

  • Trade-offs between concurrency and data consistency.

  • Choosing the appropriate isolation level for different scenarios.

• Locking Mechanisms:

  • Shared (S), Exclusive (X), Update (U) locks.

  • Lock compatibility and blocking.

  • Identifying and resolving blocking issues using DMVs (sys.dm_tran_locks, sys.dm_os_waiting_tasks).

• Deadlocking:

  • Understanding the causes of deadlocks.

  • Identifying deadlocks using SQL Server Profiler or Extended Events.

  • Strategies for preventing and resolving deadlocks.

• Optimistic and Pessimistic Concurrency Control:

  • Understanding the concepts and implementation approaches.

Advanced Data Types and Features

• Working with Large Value Types (LOBs):

  • VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX).

  • Performance considerations when working with LOBs.

  • FILESTREAM and FILETABLE for unstructured data.

• Spatial Data Types:

  • Introduction to spatial data types (geometry, geography).

  • Basic spatial queries and functions.

  • Indexing spatial data.

• Sequence Objects:

  • Generating sequential numbers.

  • Differences from identity columns.

  • Use cases for sequence objects.

• Sparse Columns:

  • Understanding sparse columns and their storage benefits.

  • When to use sparse columns.

• https://learn.microsoft.com/en-us/sql/t-sql/language-reference - reference guide and links to dev tools

• https://azure.microsoft.com/en-us/products/azure-sql/database - SQL in the cloud

• https://code.visualstudio.com/docs/languages/tsql - T-SQL on VS Code