Advanced Programming and Query Tuning in SQL Server

4 days
AdvSQLProg - Version:1
Description
This course takes programming and query tuning in SQL Server from the basic level to the advanced, providing students with tools and techniques that will allow them to write code for a broad range of requirements while optimizing performance, and on the other hand, optimize performance of existing SQL Server queries and processes. Throughout the course, the students will analyze different case studies while comparing several approaches for each one and choosing the best solutions. The course is based on SQL Server 2019, but it is relevant also for previous versions of SQL Server.
Intended audience
The course is intended for developers who are responsible for either writing Transact-SQL code or tuning already written Transact-SQL code. Students should be familiar with basic programming and with the syntax of Transact SQL.
▼Expand All

  • Basic Querying Techniques
    • Query Logical Order
    • Joining Multiple Tables
    • Grouping and Sorting
    • Using Sub-Queries
    • System Functions (String Functions, Date Functions, etc.)
    • UNION, INTERSECT and EXCEPT

  • Data Structures
    • Data Types Best Practices
    • Row Identifiers
    • Common Table Expressions
    • Temporary Tables vs. Table Variables
    • Partitioning

  • Understanding Indexes
    • Index Types
    • Missing Indexes and Redundant Indexes
    • Guidelines for Writing Efficient Queries
    • Index Tuning Guidelines

  • Understanding Statistics
    • Getting to Know Statistics
    • What is the Cardinality Estimator?
    • Multi-Column Statistics
    • Filtered Statistics
    • Scans, Seeks and SARGability

  • Query Processor Internals
    • The Plan Cache
    • Compilation-Execution Sequence
    • How Execution Plans are Made?
    • Recompilations

    • Parameterization
      • Simple vs. Forced
      • Skewed Data Distribution
      • Parameters vs. Local Variables
      • Changing Parameter Values

  • Fundamentals of Query Tuning
    • How to Read and Analyze Execution Plans?
    • Problems in Execution Plans
    • Computed Columns
    • Implicit Conversions
    • Memory Grants
    • Query Parallelism
    • Live Query Statistics

  • Advanced Programming Techniques
    • Ranking Functions and Window Functions
    • Implementing Query Paging
    • Pivoting and Unpivoting Techniques
    • Grouping Sets
    • The MERGE Statement
    • Error Handling
    • Working with Hierarchies

  • Programming Objects Best Practices
    • Views
    • User-Defined Functions
    • Stored Procedures
    • Triggers
    • Dynamic SQL

  • Transactions and Locks
    • Transactions Overview
    • Lock Types
    • Concurrency Issues
    • Transaction Isolation Levels
    • Locking Hints
    • Deadlocks
    • Nested Transactions
  • Understand the various data structures and data types within SQL Server
  • Be able to design and utilize indexes and statistics efficiently
  • Learn best practices for using programming objects in SQL Server
  • Learn to write efficient Transact-SQL code using advanced techniques
  • Understand how to read and analyze execution plans
  • Acquire techniques for efficient query tuning and troubleshooting
Contact Us
03-6176666
03-6176677 info@sela.co.il

SEND