Advanced T-SQL, Programming and Tuning for SQL Server 2005, 2008 and 2012

Description

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.

Audience

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

Prerequisites

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

LAB 03A

Module 04: Aggregating and Pivoting Data

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

LAB 04A

Module 05: TOP, OFFSET-FETCH and APPLY

• TOP and OFFSET-FETCH
• APPLY

LAB 05A

Module 06: Data Modification

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

LAB 06A

Module 07: Working with Date and Time

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

LAB 07A

Module 08: Programmable Objects

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

LAB 08A

Module 09: Graphs, Trees and Hierarchies

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

LAB 09A

Location Details

Sydney	Mentor Itzik Ben-Gan