Advanced Programming and Query Tuning in SQL Server
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.
Topics
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
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
האם אתה בטוח שאתה רוצה לסגור את הטופס ולאבד את כל השינויים?
