Advanced Transact-SQL Query, Program & Tune

Course Number: ISI-1119

This course was developed and is being delivered by Itzik Ben-Gan.

• Vendor: Microsoft
• Category: Microsoft Technical Courses
• Prerequisites
• List Price

Description

This course was developed and is being delivered by Itzik Ben-Gan. The course focuses on writing and tuning queries and programming in SQL Server 2000 and 2005. In this course you will learn the details and capabilities of T-SQL including: Logical Query Processing; Query Tuning; Subqueries, Ranking Functions, Joins and Set Operations; Aggregating and Pivoting Data (including CLR UDAs); TOP and APPLY; Data Modification; Data Type Related Problems (including XML and CLR UDTs); Programmable Objects (Transactions, Dynamic Execution, User Defined Functions, Stored Procedures, Triggers, CLR Routines); Exception Handling; Graphs, Trees and Hierarchies.

Along the course you will learn how to use T-SQL to solve practical problems such as: Relational Division, TOP Problems, Handling Sequences, Identifying Groups, Missing and Existing Numbers (Islands and Gaps), Trends, Ranking, Paging, Pivoting and Unpivoting, Separating Elements, Custom Aggregations, Running Totals, Median, Histograms, and more. You will learn how to develop efficient T-SQL and CLR routines, work with transactions and dynamic execution. You will also learn how to maintain and query hierarchical structures in SQL Server. Several different solutions will be presented including: Nested Iterations, Enumerated Paths and Nested Sets.

You will also learn how to query hierarchies using the new Recursive Common Table Expressions.

The course provides a dedicated module focusing on query tuning. The module covers internal structures, index access methods, index optimization scale, set-based vs. iterative solutions, and query tuning with query revisions. 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.

Prerequisites

The audience for this course are T-SQL Programmers, DBAs, Architects, and Analysts and those that need to write or review T-SQL code in SQL Server 2000 and 2005. The course assumes at least one year of T-SQL querying and programming experience in SQL Server.

List Price

$3950.00