⚠️ This page has been archived

✅ New page with updated info: ssw.com.au/events

  • Start date
    • To be confirmed

  • Duration
    • 5 days
  • Early bird price
    Expires 3 weeks before course starts
    • $2,765 + GST

  • Regular price
    • $3,250 + GST


The course focuses on writing and tuning queries and programming with T-SQL in SQL Server 2005, 2008 and 2012. Along the course, you will learn how to use T-SQL to solve practical problems.

You will learn how to tune your queries , how to develop efficient routines including user defined functions, stored procedures and triggers, work in multi-user environments with transactions and isolation levels, and use dynamic SQL securely and efficiently.

You will also learn how to maintain and query hierarchical data . Moreover, query tuning is in the heart of this course and is incorporated in the different modules throughout the course. With each querying/programming problem the discussions will revolve around logical aspects, set-based vs. procedural programming and optimization of the solutions.

Language: English

Early Bird Discount:

2765 AUD + GST - discount expires 3 weeks before course start

Interstate early birds: If you are from Melbourne or Brisbane and want to attend, we will fly you to Sydney for free . You will be responsible for your own accommodation.


This course is intended for:

  • T-SQL programmers, DBAs, Architects and Analysts
  • Those that need to write or review T-SQL code in SQL Server 2005, 2008, and 2012


Before attending this course, it is recommended that students have the following skills:

  • At least one year of T-SQL querying and programming experience in SQL Server

Course Objectives

Upon completion of this course, the student will:

  • Understand logical query processing
  • Understand SQL Server’s internal data structures
  • Be able to analyze and tune query performance
  • Be able to analyze query execution plans
  • Be able to solve complex querying and programming problems
  • Think in terms of sets
  • Be able to compare set based and cursor based solutions
  • Use window functions to improve solutions
  • Handle date and time data including intervals
  • Describe performance problems related to use of user defined functions and possible workarounds
  • Understand execution plan caching and reuse
  • Understand transactions and concurrency aspects of database programming
  • Know how to handle hierarchical data and write recursive queries
  • Describe T-SQL enhancements in SQL Server 2008 and 2012

Course Outline

Module 01: Logical Query Processing

  • Logical Query Processing Order
  • Logical Query Processing Example
  • Phase Details
  • Set Operations

Module 02: Query Tuning

  • Internals and Index Tuning
  • Temporary Tables
  • Sets vs. Cursors
  • Query Tuning with Query Revisions

Module 03: Multi-Table Queries, Ranking and Offset Functions

  • Subqueries and Table Expressions
  • Window Ranking and Offset Functions
  • Joins
  • Set Operators

Module 04: Aggregating and Pivoting Data

  • Window Aggregate Functions
  • Pivoting and Unpivoting Data
  • Custom Aggregations
  • Grouping Sets



Module 06: Data Modification

  • Inserting Data
  • Sequences
  • Deleting Data
  • Updating Data
  • Merging Data
  • The OUTPUT Clause

Module 07: Working with Date and Time

  • Date and Time Datatypes
  • Date and Time Functions
  • Date and Time Challenges
  • Date and Time Querying Problems

Module 08: Programmable Objects

  • Dynamic SQL
  • Views and Inline Table Functions
  • User Defined Functions
  • Stored Procedures
  • Triggers
  • Transactions and Concurrency
  • Exception Handling

Module 09: Graphs, Trees and Hierarchies

  • Graphs, Described
  • Materialized Paths
    • Custom
    • Using the HIERARCHYID datatype
  • Nested Sets
  • Nested Iterations
    • Loops
    • Recursive Queries

Location Details

Sydney Mentor Itzik Ben-Gan