Duration 3 days – 21 hrs
Overview
This hands-on course is designed for database professionals who want to deepen their expertise in SQL Server. It focuses on advanced SQL programming, including writing efficient queries, creating and managing stored procedures, and implementing triggers to automate database actions. Participants will gain practical knowledge and skills to enhance performance, maintain data integrity, and build complex business logic directly within the SQL Server environment.
Objectives
- Write and optimize complex SQL queries using advanced techniques.
- Design and implement stored procedures for modular and reusable SQL code.
- Create, test, and manage triggers to enforce business rules and automate tasks.
- Understand and manage SQL Server execution plans and performance tuning.
- Handle error management and transactions within procedures and triggers.
- Use dynamic SQL and control-of-flow language to create robust logic.
Audience
- Database Developers
- SQL Programmers
- Data Analysts
- Database Administrators (DBAs)
- Software Developers working with SQL Server
- IT Professionals who manage or design data-driven applications
Pre- requisites
- Basic knowledge of SQL and relational database concepts
- Familiarity with Microsoft SQL Server and T-SQL fundamentals
Course Content
Module 1: Advanced SQL Queries
- Subqueries and correlated subqueries
- Common Table Expressions (CTEs)
- Window functions (ROW_NUMBER, RANK, NTILE, etc.)
- Advanced joins and set operations
- Pivoting and unpivoting data
- Working with XML and JSON in SQL Server
Module 2: Stored Procedures Essentials
- Benefits and use cases of stored procedures
- Creating, altering, and executing stored procedures
- Parameter handling (input, output, default values)
- Control-of-flow logic (IF…ELSE, WHILE, CASE)
- Using system stored procedures and information schema views
Module 3: Advanced Stored Procedures Techniques
- Nested procedures and modularization
- Error handling with TRY…CATCH
- Transactions in stored procedures
- Working with temporary tables and table variables
- Dynamic SQL with EXEC and sp_executesql
- Debugging stored procedures
Module 4: Triggers in SQL Server
- Understanding the types of triggers (AFTER, INSTEAD OF)
- Creating and managing DML triggers (INSERT, UPDATE, DELETE)
- Trigger execution order and nesting
- Preventing recursive triggers
- Best practices for using triggers (when and when not to use them)
Module 5: Performance Optimization
- Understanding query execution plans
- Index usage and optimization strategies
- Avoiding common performance pitfalls in stored procedures and triggers
- Using SQL Profiler and Database Engine Tuning Advisor
Module 6: Security and Maintenance
- Permissions for stored procedures and triggers
- Schema binding and encryption
- Version control and change tracking
- Managing stored procedures and triggers in deployment
Module 7: Workshop and Hands-On Labs
- Writing advanced queries based on business scenarios
- Building a set of stored procedures for a sample system
- Implementing triggers to automate data integrity rules
- Performance tuning exercises
