Transact SQL Advanced

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.

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