Advanced SQL II

• Intermediate-level understanding of SQL
• Experience with database design and management
• Familiarity with basic indexing concepts

Audience

• Database Administrators
• SQL Developers
• Data Analysts

This instructor-led, live training (online or onsite) is aimed at database administrators, SQL developers, and data analysts who wish to deepen their expertise in SQL. It covers advanced topics in database management, optimization, and performance tuning.

By the end of this training, participants will be able to:

• Optimize database performance through effective preparation of databases and DBO.
• Utilize and manage user-defined data types (UDDT) and user-defined types (UDT).
• Effectively handle spatial data types, including Geography and Geometry.
• Enhance hierarchical data management, including structure elements, storage methods, and indexing strategies.
• Improve query performance through advanced techniques such as data indexing, query execution plan analysis, and control of SQL query execution.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Preparation of the database and DBO2.Own data types

•    UDDT (User-Defined Data Types)
•    UDT (User-Defined Types) 3.

Spatial data

•    Geography
•    Geometry

Hierarchical data type

•    Structure elements
•    Storage methods
•    Indexing strategies
•    Methods

XML data type

•    Creating variables
•    OPENXML standard
•    FOR XML clause
•    Data type conversion
•    WITH XMLNAMESPACES clause
•    Namespaces
•    XQUERY language
•    XPATH language
•    FLWOR expressions
•    Methods
•    Indexing of XML documents
•    Examples of using XML data type

APPLY operator

•    CROSS APPLY
•    OUTER APPLY

Ranking and analysis functions

•    OVER clause
•    Window functions
•    Frame clauses
•    Operators: GROUPING SETS, GROUPING_ID, ROLLUP, CUBE, PIVOT, UNPIVOT

Temporary data

•    Temporary tables
•    Differences and similarities in the use of array variables and temporary tables
•    Other temporary objects

Improving query performance through data indexing

•    Row indexes,
•    Advantages and disadvantages of using indexes
•    Types of indexes
•    Index selectivity index
•    Server suggestions for missing indexes
•    HEAP tables
•    Hints to the server
•    Measurement of execution time for operations with and without indexes
•    Columnar indexes (COLUMNSTORE INDEX)

Maintenance and upkeep of indexes

•    Fragmentation of indexes
•    Rebuilding of indexes: REBUILD
•    Reorganising indexes: REORGANIZE
•    Level of index fragmentation

Creation and maintenance of statistics

•    Construction of statistics and the principle of their operation
•    Monitoring and maintenance of statistics
•    Cardinality estimation errors and updating of statistics

Query execution plan analysis

•    Query optimiser
•    CASE: obtaining information for a specific query
•    Principles of the query optimiser
•    Query plans: QUERY EXECUTION PLAN
•    Types of query execution plans
•    Running and reading query plans
•    CASE: INDEX SCAN and INDEX SEEK operations
•    PARAMETER SNIFFING
•    Code recompilation

Controlling the execution of SQL queries

•    Hints (hints to the server)
•    SQL Server Profiler - (SSP)
•    Extended Events - (EE)
•    Database Engine Tuning Advisor - (DTA)
•    Data Collector - (DC)
•    Query Store - (QS)