|
|
Class Outline*
|
|
| Request Info | Contact Us
|
|
Advanced T-SQL Querying, Programming and Tuning for SQL Server 2005 & 2008
Duration:
5 Days
Overview:
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
- Know how to handle date and time data
- Understand compilations, recompilations and reuse of execution plans
- Understand transactions and concurrency aspects of database programming
- Know how to handle hierarchical data and write recursive queries
- Be familiar T-SQL enhancements in SQL Server 2005 and 2008
Prerequisite(s) or equivalent knowledge:
|
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
|
|
|
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: Subqueries, Ranking Functions, Joins and Set Operations
|
- Subqueries
- Analytical Ranking Functions
- Joins
- Set Operations
- Lab
|
|
Module 04: Aggregating and Pivoting Data
|
- The OVER Clause
- Tie Breakers
- Running Aggregations
- Pivoting and Unpivoting Data
- Custom Aggregations
- Histograms
- Grouping Sets
- Lab
|
|
Module 05: TOP and APPLY
|
|
|
|
Module 06: Data Modification
|
- Inserting Data
- Deleting Data
- Updating Data
- Merging Data
- The OUTPUT Clause
- Lab
|
|
Module 07: Datatype Related Problems
|
- Date and Time Manipulation
- Datatype Enhancements
- Lab
|
|
Module 08: Programmable Objects
|
- Dynamic SQL
- Views
- User Defined Functions
- Stored Procedures
- Triggers
- Transactions and Concurrency
- Exception Handling
- Lab
|
|
Module 09: Graphs, Trees and Hierarchies
|
- Graphs, Described
- Common Requests against Graphs
- Enumerated Paths Model
- Nested Sets Model
- Recursive Queries
- The HIERARCHYID Datatype
- Lab
|
|
*Content, days, and times vary depending on your location. Please view the outline prior to purchase or contact the local center for more information.
|