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.

  14 Hours
 

Testimonials (8)

Related Courses

Related Categories