Duration
7 hours (usually 1 day including breaks)
Overview
Delegates will gain an understanding of some of the more advanced features of Transact SQL as well as being able to do each of the following:
- Use queries to return complex result sets
- Manage database objects to aid query performance
- Tune queries to perform more efficiently
This course is for anyone who currently uses Transact SQL to extract data from a Microsoft SQL Server database and wishes to expand their knowledge particularly in the areas of data analysis and improving query speed.
Course Outline
Analytical Functions
- Use of advanced summary functions
- Use of hierarchical queries
- Use of analytical summary functions, e.g. moving averages, running totals
- Use of ranking functions
Useful Database Objects
- Principles of using indexes
- How to create and maintain an index
- Use of clustered tables
- Use of partitioned tables
- Use of metadata in the master database
Query Performance Tracing
- Principles of query execution and optimisation
- Use of Execution Plan
- Use of table & index statistics
- Use of hints
Basic Data Warehouse Techniques
- Use of Indexed Views
- Use of Dimension & fact tables
- Use of Star & Snowflake designs