-
Start date
-
Duration
-
Early bird price
Expires 3 weeks before course starts
-
Regular price
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
LAB 09A
Location Details