SQL Server 2000/2005: Transact-SQL Programming and Query Tuning

Course Overview

The course focuses on writing advanced queries and code in SQL Server 2000 and 2005.

In this course you will learn the details and capabilities of T-SQL including: Understanding Query Logical Processing, Joins, Table Expressions and Ranking Functions, Aggregating and Pivoting Data, TOP, APPLY, DML with Results, Error Handling, Datatypes issues, Dynamic Execution, Data Modification, Tuning T-SQL, Hierarchies and Recursive Queries.

Audience

T-SQL Programmers, Architects, Analysts and DBAs. Those that need to write or review T-SQL code in SQL Server 2000 and 2005

• Skills Gained

  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 Numbers, Trends, Ranking, Paging, Pivoting and Unpivoting, Separating Elements, Custom Aggregations, Running Totals, Median, Histograms and more.

• You will also learn how to maintain and query hierarchies in SQL Server. Several different solutions will be presented including: Nested Iterations, Enumerated Paths Nested Sets and Recursive Common Table Expressions.

• Additional appendixes are provided for reference and for self-learning including the subjects: Programmable Objects (Views, Stored Procedures, Triggers, User Defined Functions and Partitioned Tables and Indexes)

Prerquisites

The course assumes at least a year of programming experience in SQL Server, mainly in T-SQL querying and code writing.

Course Outline

• Module 01: Understanding Query Logical Processing
• Module 02: Joins
• Module 03: Table Expressions and Ranking Functions
• Module 04: Aggregating and Pivoting Data
• Module 05: TOP, APPLY and DML with Results
• Module 06: Error Handling
• Module 07: Datatypes
• Module 08: Dynamic Execution
• Module 09: Data Modification
• Module 10: Tuning T-SQL
• Module 11: Hierarchies and Recursive Queries

SQL Server 2000/2005: Transact-SQL Programming and Query Tuning / MSQ-PQT