Course Outline
Module 1. Query Tuning
- Tools for Query Tuning
- Cached Query Execution Plans
- Clearing the Cache
- Analyzing Execution Plans
- Hints
- Using the Database Engine Tuning Advisor
- Index Tuning
- Table and Index Structures
- Index Access Methods
- Indexing Strategies
Module 2. Subqueries, Table Expression, and Ranking Functions
- Writing Subqueries
- Using Table Expressions
- Using Ranking Functions
Module 3. Optimizing Joins and Set Operations
- Fundamental Join Types
- Join Algorithm
- Set Operations
- Using INTO with Set Operation
Module 4. Aggregating and Pivoting Data
- Using the OVER Clause
- Different types of aggregations (Cumulative, Sliding and Year-To-Date)
- Pivoting and Unpivoting
- Setup Custom Aggregations
- Using GROUPING SETS Subclause
- CUBE and RULLUP Subclauses
- How to materialize Grouping Sets
Module 5. Using TOP and APPLY
- SELECT TOP
- Using the APPLY table operator
- TOP n at the Group Level
- Implementing Paging
Module 6. Optimizing Data Transformation
- Inserting data with Enhanced VALUES Clause
- Using the BULK Rowset Provider
- Using INSERT EXEC
- The Sequence Mechanisms
- DELETE with joins
- UPDATE with joins
- MERGE statement
- The OUTPUT Clause with INSERT
- The OUTPUT Clause with DELETE
- The OUTPUT Clause with UPDATE
- The OUTPUT Clause with MERGE
Module 7. Querying Partitioned Tables
- Partitioning in SQL Server
- How to write queries on partitioned tables
- How to write queries on partitioned views
Requirements
Good SQL knowledge in Microsoft SQL Server 2008/2012 environment.
Testimonials (5)
Philip was very kind, his style of explaining SQL concepts is outstanding. I liked that he give us information and answered to questions which were not part of this course.
Stefan
Course - SQL in SQL Server
It allowed us to have an applied hands on sproc to our company database.
Corneliu Buculei - Standard Building Supplies Ltd
Course - Advanced SQL, Stored Procedures and Triggers for Microsoft SQL Server
Interactive exercises
Matthew Viner - Thames Water Utilites Ltd
Course - Transact SQL Basic
the in depth knowledge and customize approach
Julius Mapili - SYKES Asia, Inc.
Course - Administering in Microsoft SQL Server
The ability to ask questions at any time and the more informal / less structured style. This allowed us to pursue the areas of knowledge we were most interested in.