Course Outline
1. Preparing the database and DBO 2. Own data types
- UDDT (User-Defined Data Types)
- UDT (User-Defined Types)
3. Spatial data
- Geography
- Geometry
4. Hierarchical data type
- Elementy structures Storage methods Indexing strategies Methods
5. Data type XML
- Creating variables Standard OPENXML FOR clause XML Data type conversion WITH clause XMLNAMESPACES Namespaces XQUERY language XPATH language FLWOR expressions Methods Document indexing XML Examples of using data types XML
6. Operator APPLY
- CROSS APPLY
- OUTER APPLY
7. Ranking and analytical functions
- OVER clause Window functions Frame clauses Operators: GROUPING SETS, GROUPING_ID, ROLLUP, CUBE, PIVOT, UNPIVOT
8. Temporary data
- Temporary tables Differences and similarities in the use of array variables and temporary tables Other temporary objects
9. Improve query performance by indexing data
- Row indexes, Advantages and disadvantages of using indexes Types of indexes Index selectivity index Server suggestions regarding missing indexes HEAP tables (heap) Hints (tips, hints for the server) Measurement of operation time with and without the use of indexes Column indexes (COLUMNSTORE INDEX)
10. Maintenance and maintenance of indexes
- Index fragmentation Index reconstruction: REBUILD Index reorganization: REORGANIZE Index fragmentation level
11. Creating and maintaining statistics
- Construction of statistics and the principle of their operation. Monitoring and maintenance of statistics. Cardinality estimation errors and updating of statistics
12. Analysis of query execution plans
- CASE query optimizer: obtaining information for a specific query Principles of operation of the query optimizer Query plans: QUERY EXECUTION PLAN Types of query execution plans Running and reading CASE query plans: INDEX SCAN and INDEX SEEK PARAMETER SNIFFING operations Code recompilations
13. Query execution control SQL
- Hints (tips, hints for the server) SQL Server Profiler - (SSP) Extended Events - (EE) Database Engine Tuning Advisor - (DTA) Data Collector - (DC) Query Store (query store) - (QS)
Requirements
- Intermediate-level understanding of SQL
- Experience with database design and management
- Familiarity with basic indexing concepts
Audience
- Database Administrators
- SQL Developers
- Data Analysts
Testimonials (11)
Interakcja z prowadzącym, możliwość zadawania pytań na gorąco
Grzegorz Ziegert
Course - Administration with Powershell
Poruszanie zazębiających się aspektów przy operowaniu na bazach danych
Michał Marzec
Course - SQL Advanced in MySQL
Szkolenie przeprowadzone gruntownie od zagadnień podstawowych po te bardzo zaawansowane. Trener przyjazny, chętnie udzielający odpowiedzi na zadawane pytania.
Anna Gerlich - UBS Business Solutions Poland Sp. z o.o.
Course - SQL in Microsoft Access
The adjustment made in the lecture/lessons by the trainer once he understood the current SSIS application that we are bound to maintain. The topics became more suitable/usable to us.
Angelito Aguilar - Metrobank
Course - Introduction to SQL Server 2012 Integration Services (SSIS)
I like this training because it was interactive
Amalia - Banca Transilvania
Course - SQL Fundamentals
Lot of content and exactly the requested one.
Pascal - Diehl
Course - Advanced SQL, Stored Procedures and Triggers for Microsoft SQL Server
i really liked the pace of the training, and the exercises given inbetween the explanations
Trana
Course - SQL in SQL Server
I enjoyed the balance of lectures and exercises. When I was stuck Luke was very good at helping me to understand the problem and work towards solutions which made a really positive impact on my understanding.
Tom - Welsh Revenue Authority
Course - Transact SQL Basic
Really enjoyed compression data components
James - Medtech Limited
Course - Transact SQL Advanced
The course built lesson to lesson, and the pacing was a big deal for me.
Bradford Moore - Regis College
Course - SQL in MySQL
I appreciated Folio's wide breadth of knowledge. Not only was he familiar with the course content, but he also knew of constructs in languages we were familiar with to make examples more meaningful to us. During intervals he shared his knowledge of technologies and solutions outside the training scope to provide insights into other solutions we could use in future (and future training).