Transact SQL (T-SQL) Advanced Training in Thimphu, Bhutan

This advanced Transact-SQL course is designed for professionals who already have a basic understanding of T-SQL and want to enhance their expertise in writing complex queries, optimizing performance, and leveraging advanced database programming techniques. Participants will explore advanced querying techniques, stored procedures, triggers, error handling, and dynamic SQL, with a focus on performance tuning and real-world application.

Advanced Querying & Stored Programs

Module 1: Advanced Querying Techniques

• Review of Core T-SQL Concepts
• Review SELECT, JOINs, and subqueries
• Understanding query processing order
• Advanced JOIN Techniques
• INNER, OUTER, CROSS, SELF joins revisited
• APPLY operators (CROSS APPLY, OUTER APPLY)
• Practical exercises combining APPLY with table-valued functions
• Common Table Expressions (CTEs)
• Syntax and use cases
• Recursive CTEs for hierarchical data
• Performance considerations vs. temp tables

Module 2: Window Functions

• Window Function Fundamentals
• OVER(), PARTITION BY, ORDER BY
• Ranking & Aggregation
• RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER()
• Running totals, moving averages, and percentiles
• Hands-on Labs

Module 3: Stored Programs & Error Handling

• User-Defined Functions
• Scalar, inline table-valued, and multi-statement UDFs
• Performance implications
• Stored Procedures
• Creating, executing, and managing stored procedures
• Input/output parameters and return values
• Error Handling
• TRY...CATCH blocks
• Raising custom errors with THROW
• Logging and transaction rollback patterns

Transactions, Performance Tuning & Dynamic SQL

Module 4: Transactions & Concurrency

• Transactions
• ACID principles and transaction control (BEGIN, COMMIT, ROLLBACK)
• Isolation Levels
• READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT
• Locking & Deadlocks
• Detecting and resolving deadlocks
• Best practices for minimizing blocking

Module 5: Performance Tuning & Optimization

• Query Execution Plans
• Reading and interpreting execution plans
• Identifying costly operators
• Indexing Strategies
• Clustered vs. nonclustered indexes
• Filtered and covering indexes
• Statistics
• How SQL Server uses statistics
• Updating and maintaining statistics

Module 6: Dynamic SQL & Advanced Features

• Dynamic SQL
• Using EXEC and sp_executesql safely
• SQL injection prevention techniques
• PIVOT and UNPIVOT
• Transforming and aggregating data sets
• Triggers
• AFTER and INSTEAD OF triggers
• Auditing and enforcing business rules
• Best Practices & Real-World Scenarios
• Code readability and maintainability
• Common performance pitfalls
• Review and Q&A