Advanced Transact SQL in SQL Server equips database developers and administrators with the skills to write advanced T-SQL queries, create stored procedures and functions, optimize query performance, implement triggers, and develop high-performance SQL Server database solutions.
Duration: 5 days – 35 hrs.
Overview
This course is designed to teach advanced T-SQL programming techniques for SQL Server. Participants will learn how to write efficient and scalable T-SQL code using advanced features and techniques. The course covers topics such as advanced querying, performance tuning, database design, and programming constructs.
Objectives
- Write complex T-SQL queries using advanced features such as subqueries, window functions, and common table expressions.
- Use advanced T-SQL programming constructs such as user-defined functions, stored procedures, and triggers to improve performance and maintainability.
- Optimize query performance using indexing, query tuning, and execution plans.
- Design and implement efficient database schemas using normalization, constraints, and best practices.
- Work with XML and JSON data in SQL Server.
- Understand security and permissions in SQL Server.
Audience
- Database Administrators
- Programmers
- Developers
- BI (Business Intelligence) professionals
- Data Analysts
- Database and data warehousing professionals
Pre- requisites
- Deep understanding on basic SQL development using SQL server.
- SQL server experience basic.
Course Content
Module 1: Advanced Querying
- Subqueries and correlated subqueries
- Window functions
- Pivoting and unpivoting data
- Hierarchical queries using recursive CTEs
Module 2: Advanced T-SQL Programming
- User-defined functions (UDFs)
- Stored procedures and parameters
- Triggers and their use cases
- Dynamic SQL and its dangers
Module 3: Query Performance Tuning
- Indexing strategies and techniques
- Query tuning using execution plans
- Understanding query optimizer behavior
- Monitoring and troubleshooting query performance
Module 4: Database Design and Best Practices
- Normalization and denormalization
- Constraints and integrity
- Partitioning and archiving data
- Handling database growth and maintenance
Module 5: Working with XML and JSON Data
- Introduction to XML and JSON formats
- XML and JSON data types and functions
- Parsing and shredding XML and JSON data
- Indexing and querying XML and JSON data
Module 6: Security and Permissions
- Understanding SQL Server security architecture
- Managing users and logins
- Securing data at rest and in transit
- Implementing security best practices
Module 7 Stored Procedure
Explanation of Transact-SQL (T-SQL) and stored procedures Importance of using advanced techniques in T-SQL for creating efficient and effective stored procedures
II. Using Table Variables
- Explanation of table variables and their benefits over temporary tables
- Syntax for creating and using table variables in stored procedures
- Examples of how to use table variables in stored procedures
III. Using the APPLY Operator
- Explanation of the APPLY operator and its benefits for simplifying complex queries and improving performance
- Syntax for using the CROSS APPLY and OUTER APPLY operators in stored Procedures
- Examples of how to use the APPLY operator in stored procedures
IV. Using Window Functions
- Explanation of window functions and their benefits for performing calculations on
a set of rows defined by a window or partition - Syntax for using window functions such as ROW_NUMBER (), RANK (), and LEAD () in stored procedures
- Examples of how to use window functions in stored procedures
Dynamic SQL
- Explanation of dynamic SQL and its benefits for creating flexible and adaptable
stored procedures - Syntax for using dynamic SQL in stored procedures
- Examples of how to use dynamic SQL in stored procedures
Module 8 Derived Table
- Explanation of Transact-SQL (T-SQL) and derived tables
- Importance of using advanced techniques in T-SQL for working with derived
tables
II. Using Derived Tables with SELECT
- Explanation of how to use derived tables in the SELECT statement
- Syntax for creating and using derived tables in the SELECT statement
- Examples of how to use derived tables in the SELECT statement
III. Using Derived Tables with JOIN
- Explanation of how to use derived tables in JOIN operations
- Syntax for creating and using derived tables in JOIN operations
- Examples of how to use derived tables in JOIN operations
IV. Using Common Table Expressions (CTEs)
- Explanation of CTEs and their benefits for creating reusable subqueries
- Syntax for creating and using CTEs with derived tables
- Examples of how to use CTEs with derived tables
V. Using Derived Tables with Subqueries
- Explanation of how to use derived tables with subqueries
- Syntax for creating and using derived tables with subqueries
- Examples of how to use derived tables with subqueries
Module 9 Cursors
Explanation of Transact-SQL (T-SQL) and cursors
Importance of using advanced techniques in T-SQL for working with cursors
II. Types of Cursors in SQL Server
- Explanation of the different types of cursors available in SQL Server
- Comparison of forward-only, static, keyset-driven, and dynamic cursors
- Examples of when to use each type of cursor
III. Cursor Properties and Options
- Explanation of cursor properties and options that can be used to customize cursor behavior
- Syntax for setting cursor properties and options
- Examples of how to use cursor properties and options
IV. Using Cursors in Stored Procedures
- Explanation of how to use cursors in stored procedures
- Syntax for declaring and using cursors in stored procedures
- Examples of how to use cursors in stored procedures
V. Cursor Performance and Optimization
- Explanation of how to optimize cursor performance in SQL Server
- Best practices for cursor usage and optimization
- Examples of how to optimize cursor performance
