Advanced Querying Microsoft SQL Server with Transact-SQL Course

Syllabus – Key points

Course Contents – DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: USING TABLE EXPRESSIONS

• Creating and using Views
• Using Inline Table-Valued Functions
• Using Derived Tables
• Using Common Table Expressions

Session 2: PIVOTING AND ENHANCED GROUPING

• Using PIVOT and UNPIVOT in Queries
• Working with GROUPING SETS
• Using ROLLUP, CUBE,GROUPING and GROUPING_ID

Session 3: USING WINDOWING FUNCTIONS

• Using Window AGGREGATE functions in Queries
• Using Window RANK functions in Queries
• Using Window OFFSET functions in Queries

Course Contents – DAY 2

Session 4: IMPLEMENTING STORED PROCEDURES

• Overview of Stored Procedures
• Creating simple Stored Procedures
• Executing Stored Procedures
• Passing parameters to Stored Procedures

Session 5: PROGRAMMING WITH TRANSACT-SQL

• Using Programming Elements in Transact-SQL
• Controlling Program Flow with IF/ELSE
• Controlling Program Flow with WHILE
• Working with Dynamic SQL

Session 6: IMPLEMENTING ERROR HANDLING

• Structured error handling with TRY / CATCH block
• Unstructured error handling
• Generating error message using THROW and RAISEERROR
• Error handling in conjunction with Transaction Control

Session 7: IMPROVING QUERY PERFORMANCE

• Factors in Query Performance
• Displaying Query Performance Data