shape-876@2x

Microsoft SQL Database

Advanced Programming and Query Tuning in SQL Server

מספר הקורס 51761

40 סה"כ שעות אקדמאיות
5 מפגשים
* מספר המפגשים והשעות למפגש עשויים להשתנות בין קורס לקורס
calendar-1.svg

המועדים הקרובים

הקורס נפתח גם במתכונת Live class – למידה און ליין בכיתות אינטראקטיביות עם מיטב המרצים והתכנים של ג'ון ברייס /המי"ל.
ניתן לפתוח קורס בהתאמה אישית לארגונים במועד שיתואם עימנו

19/05/2024

קורס בוקר

סניף

תל אביב

Overview

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 the performance of existing 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 as well as for Azure SQL.

hat.png

On Completion, Delegates will be able to

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

kahal.png

Who Should Attend

The course is intended for developers who are responsible for either writing Transact-SQL code or tuning already written Transact-SQL code.

structure.png

תכנית הלימודים

Full syllabus

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

Database Design

  • Data Types Best Practices
  • Row Identifiers
  • Table Constraints
  • Temporary Tables vs. Table Variables
  • Common Table Expressions
  • Partitioning
  • Memory-Optimized Tables

Understanding Indexes

  • Rowstore vs. Columnstore Indexes
  • How to Design Indexes
  • Composite Indexes
  • Filtered Indexes
  • Covering Indexes
  • Missing Indexes and Redundant Indexes
  • Guidelines for Writing Efficient Queries

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

Prerequisites

Students should be familiar with basic programming and with the syntax of Transact-SQL.

Schedule Appointment

Fill out the form below, and we will be in touch shortly.

לא הצלחנו לאתר את הטופס.

בודק...