T-SQL Fundamentals & Control Flow

Key Topics Covered

• Data Type Functions (String, Date/Time, Mathematical, System)
• T-SQL Elements (Predicates, Operators, Expressions)
• Control Flow Statements (IF…ELSE, WHILE, CASE, GOTO, BEGIN…END, TRY…CATCH, RETURN)
• T-SQL Variables (Declaration, Assignment, Variable Types)

Lab / Practice

Hands-on exercises on functions, Control flow statements, Variable usage

Set Theory & SQL Query Processing

Key Topics Covered

• Set Theory Concepts (Unordered, Distinctness, Subset, Union, Intersection, Difference, Cartesian Product)
• Set Theory applied to SQL Server
• Basic SQL Query Structure (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY)
• SQL Query Processing
• Logical Order of Execution in SELECT

Lab / Practice

Query-writing exercises, Logical order validation

Writing SELECT Queries & CASE Expressions

Key Topics Covered

• Writing Simple SELECT Statements
• Standards & Best Practices
• Scalar Expressions
• DISTINCT (Single & Multiple Columns, NULL behavior)
• CASE Statement (Simple, Searched, Nested)
• Aliases (Column & Table)
• Alias Scope & Logical Processing Impact

Lab / Practice

SELECT query practice, CASE & alias-based exercises

Joins & Join Types

Key Topics Covered

• Querying Multiple Tables
• Join Fundamentals
• Join Types (Inner, Left, Right, Full Outer)
• Cross Joins & Self Joins
• Logical Processing Phase in Joins
• Understanding CustomerOrderDB Schema & ER Diagrams

Lab / Practice

Join queries using CustomerOrderDB, Practice with AdventureWorks DB

Multi-Table Joins & Row Count Analysis

Key Topics Covered

• Multi-table Query Concepts
• Join Syntax & Best Practices
• Understanding Relationships (PK–FK)
• Row Count Impact in Joins
• Inner / Left / Right / Full Join Scenarios
• LEFT JOIN returning more rows than left table
• Working with large databases

Lab / Practice

Real-time join scenarios, Row count analysis exercises

Advanced Join Concepts

Key Topics Covered

• Conditional Joins using CASE
• Joins with Subqueries
• Derived Tables (Subquery in FROM)
• Joins vs Subqueries
• When to use Joins vs Subqueries

Lab / Practice

Advanced join scenarios, Subquery-based joins

Sorting Data

Key Topics Covered

• ORDER BY Clause
• Sorting with ASC / DESC
• Sorting by Single & Multiple Columns
• Sorting by Expressions
• NULL Handling in Sorting
• Aliases in ORDER BY

Lab / Practice

Advanced sorting scenarios

Filtering & Pagination

Key Topics Covered

• WHERE Clause
• Comparison Operators
• AND / OR Conditions
• Parentheses & Logical Evaluation
• OFFSET / FETCH (Pagination)
• GROUP BY & HAVING
• Filtering with Functions
• COLLATE (Case Sensitive / Insensitive)
• Filtering using Subqueries

Lab / Practice

Filtering & pagination practice, GROUP BY & HAVING exercises

Aggregate Functions

Key Topics Covered

• Aggregate Functions Overview
• SUM, AVG, COUNT, MIN, MAX
• Handling NULLs in Aggregates
• DISTINCT with Aggregates
• COALESCE
• Partial Aggregate Errors

Lab / Practice

Aggregate queries on Sales data

GROUP BY & HAVING in Depth

Key Topics Covered

• GROUP BY Fundamentals
• Single & Multiple Column Grouping
• NULL Handling in GROUP BY
• Multiple Aggregates
• HAVING vs WHERE
• Logical Order of Operations
• Partial GROUP BY Errors

Lab / Practice

Grouping & HAVING-based exercises

Subqueries – Fundamentals

Key Topics Covered

• Introduction to Subqueries
• Logical Execution Flow
• Types of Subqueries (Self-Contained, Correlated)
• Scalar, Multi-row, Multi-column Subqueries
• Subqueries in SELECT, FROM, WHERE
• Subquery Error Scenarios

Lab / Practice

Writing self-contained subqueries

Subqueries – Advanced & CRUD

Key Topics Covered

• Correlated Subqueries in Detail
• Scalar, Multi-row, Multi-column Correlated Subqueries
• CRUD Operations using Subqueries
• Subqueries vs Joins (When to Use What)

Lab / Practice

Advanced subquery exercises, CRUD with subqueries

Working with Tables

Key Topics Covered

• Temporary Tables (Local #, Global ##)
• Table Variables vs Scalar Variables
• Common Table Expressions (CTEs) – INSERT, UPDATE, DELETE
• Recursive CTEs (Anchor & Recursive Members)
• Identity Columns
• Unique Columns & Constraints
• IDENTITY_INSERT (ON/OFF)
• SCOPE_IDENTITY(), @@IDENTITY, IDENT_CURRENT()

Lab / Practice

Temporary tables & table variables, CTE & recursive CTE examples, Identity & scope identity demos

Working with Views (Fundamentals & Security)

Key Topics Covered

• Introduction to Views
• Tables vs Views
• Internal vs External Users
• Benefits of Views (Security, Abstraction)
• Simple & Complex Views
• Updatable vs Non-Updatable Views
• View Options: SCHEMABINDING, ENCRYPTION, CHECK OPTION, VIEW_METADATA
• Permissions on Views

Lab / Practice

Creating simple & complex views, Securing data using views

Working with Views (Advanced & Performance)

Key Topics Covered

• Indexed Views (Materialized Views)
• Partitioned Views
• Performance Impact of Views
• When to Avoid Views
• Optimizing Views
• Real-world Use Cases for Views

Lab / Practice

Indexed & partitioned view scenarios

Stored Procedures (Basics)

Key Topics Covered

• Introduction to Stored Procedures
• Benefits vs Ad-hoc Queries
• Parameterized Stored Procedures
• Input Parameters
• Output Parameters
• Creating, Executing, Modifying & Dropping Stored Procedures

Lab / Practice

Creating & executing stored procedures, Input & output parameter examples

Stored Procedures (Best Practices & Debugging)

Key Topics Covered

• Best Practices (SET NOCOUNT ON, Avoid SELECT *, Schema Usage)
• Cursors vs WHILE Loops
• Error Handling with TRY…CATCH
• ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE()
• Debugging using PRINT, RAISERROR
• SSMS Debugger & SQL Profiler
• Transaction Management in SPs

Lab / Practice

Debugging stored procedures, Real-world SP scenarios

Transactions & Transaction Management

Key Topics Covered

• Introduction to Transactions
• BEGIN TRANSACTION, COMMIT, ROLLBACK
• TRY…CATCH with Transactions
• @@ERROR & @@TRANCOUNT
• Savepoints (SAVE TRANSACTION)
• Nested Transactions
• Transaction Count Handling

Lab / Practice

Transaction handling scenarios, Nested transaction demos

Functions (Scalar & System)

Key Topics Covered

• Introduction to Functions
• Guidelines & Limitations
• Built-in vs User-Defined Functions
• Scalar UDFs
• System Functions (String, Date, Math, Aggregate)
• Creating & Executing Scalar UDFs

Lab / Practice

Scalar UDF creation & usage

Functions (Table-Valued & Comparisons)

Key Topics Covered

• Inline TVFs
• Multi-Statement TVFs
• Inline vs Multi-Statement TVFs
• Functions vs Stored Procedures
• Functions vs Views
• Performance Considerations & Best Practices

Lab / Practice

TVF implementation examples

Triggers (Fundamentals)

Key Topics Covered

• Introduction to Triggers
• DML Triggers (AFTER, INSTEAD OF)
• DDL Triggers
• Trigger Execution Order
• INSERTED & DELETED Tables
• Triggers vs Stored Procedures

Lab / Practice

DML trigger creation, Hands-on trigger demos

Triggers (Advanced & Best Practices)

Key Topics Covered

• Auditing & Logging
• Cascading Updates & Deletes
• Error Handling & Rollbacks
• Triggers vs Constraints
• Trigger Best Practices
• Trigger Limitations
• ALTER & DROP TRIGGER

Lab / Practice

Real-world trigger use cases

Indexes (Fundamentals)

Key Topics Covered

• What is an Index
• Clustered Index
• Non-Clustered Index
• Clustered vs Non-Clustered
• Benefits & Drawbacks
• Heap vs B-Tree Storage

Lab / Practice

Heap vs index demo

Indexes (Performance & Maintenance)

Key Topics Covered

• PK vs Clustered Index
• Unique Key vs Non-Clustered Index
• Indexes & SELECT Performance
• Index Impact on INSERT/UPDATE/DELETE
• Index Fragmentation
• Fill Factor & Pad Index

Lab / Practice

Index performance comparison

Indexes (Advanced Types)

Key Topics Covered

• Covering Indexes
• Filtered Indexes
• Full-Text Indexes

Lab / Practice

Advanced index labs

T-SQL Enhancements in SQL Server 2022

Key Topics Covered

• Enhanced STRING_SPLIT with ordinal positions
• LEAST() and GREATEST() functions
• Date & Time Enhancements:
  • DATE_BUCKET
  • DATETRUNC
  • GENERATE_SERIES
• Approximate Query Processing:
  • APPROX_COUNT_DISTINCT
• JSON Enhancements:
  • JSON_PATH_EXISTS
  • Improved ISJSON
  • JSON_QUERY
  • JSON_MODIFY

Lab / Practice

Practice string splitting with ordinal output, Compare values using LEAST and GREATEST, Build time-series reports using DATE_BUCKET and GENERATE_SERIES, Use APPROX_COUNT_DISTINCT on large tables, Perform JSON validation, extraction, and updates

Enhancing Data Security and Flexibility in SQL Server 2022

Key Topics Covered

• Dynamic SQL
  • When and why to use Dynamic SQL
  • EXEC vs sp_executesql
  • Preventing SQL Injection
  • Performance considerations
  • Static vs Dynamic SQL comparison
• Dynamic Data Masking (DDM)
  • DDM concepts and use cases
  • Granular masking rules
  • Role-based masking behavior
• Auditing and Compliance
  • SQL Server Auditing concepts
  • Server & Database audits
  • Audit enhancements in 2022
  • Compliance (GDPR, HIPAA)

Lab / Practice

Build parameterized dynamic queries, Demonstrate SQL injection scenarios, Apply DDM on sensitive columns, Test masking for different users, Configure audit logs and track user activity

Intelligent Query Processing (IQP) for Performance in SQL Server 2022

Key Topics Covered

• Batch Mode on Rowstore
• Approximate Query Processing
• Scalar UDF Inlining
• Table Variable Deferred Compilation
• Adaptive Joins

Lab / Practice

Compare row mode vs batch mode execution plans, Measure performance before/after UDF inlining, Analyze execution plans for table variables, Observe adaptive join behavior using actual row counts

Backup and Recovery Enhancements in SQL Server 2022

Key Topics Covered

• Overview of Backup and Restore architecture
• Snapshot backups and faster recovery
• Accelerated Database Recovery (ADR) improvements
• Backup compression and encryption enhancements
• Improved restore performance for large databases
• System database backup strategies

Lab / Practice

Perform full, differential, and log backups, Simulate data loss and perform point-in-time recovery, Enable and test Accelerated Database Recovery (ADR), Configure encrypted backups, Restore large databases and analyze restore time

CLR Integration and Azure SQL Database in SQL Server 2022

Key Topics Covered

• CLR Integration
  • Overview of SQL CLR and use cases
  • Enabling CLR in SQL Server 2022
  • Creating CLR stored procedures and functions
  • Security considerations for CLR
• Azure SQL Database
  • SQL Server vs Azure SQL Database
  • Deployment models (Single, Elastic Pool, Managed Instance)
  • Connecting on-prem SQL Server to Azure SQL
  • Hybrid scenarios and cloud migration basics

Lab / Practice

Create a simple CLR function using C#, Deploy CLR assembly to SQL Server, Call CLR procedure from T-SQL, Create Azure SQL Database, Migrate a sample database to Azure SQL