Course: SQL Server Beyond Classical RDBMS (SQL-BC)

Summary

Many times, you wish you could have some more powerful tool than the Transact-SQL (T-SQL) language for working with data. You might need to enable your users to use word search in your string columns or in Office documents to give them similar experience like they get when they search the web. You might need to implement constraints and do the data analysis with functions that are simply not implemented in T-SQL. Or you might need to maintain multiple versions of data over the time and be able to query the state of the data from the past.

All these features mentioned are sometimes referred as “beyond relational”. However, data type and language support in a specific database management product has nothing to do with the relational model, which is purely logical model. For many years, we used to associate a RDBMS with the SQL language and simple data types; but there are no limitations for both in the relational model. Therefore, I prefer to use the term “beyond classical RDBMS”.

Abstract

In this seminar, you will learn how to manipulate the data stored in your SQL Server and Azure SQL Database with advanced T-SQL language elements and with other languages. You will learn about Full-Text Search (FTS), integrating CLR functions written in Visual C# or Visual Basic in your T-SQL code, and work with system-versioned tables within SQL Server.

Besides executing CLR code inside the Database Engine, SQL Server supports executing code in external engines with external languages. R, Python, and Java support are covered in the seminar as well.

Tabular structures are not always the best for storing some kinds of data. Maybe your data has a bit more dynamic structure than you can represent with tables, or has a very specific structure, like hierarchy. This seminar introduces how to deal with such data inside a SQL Server database.

You will learn how to store and retrieve semi-structured data in XML or JSON format. You can extend the collection of the data types supported by SQL Server with your own CLR data types, written in C# or VB. This infrastructure was used also by Microsoft through the for spatial data types. Hierarchical data, like XML and JSON, are just a special case of a general data structures called graphs. You will learn also how to use graphs in SQL Server.

Modules:

1. Advanced search with Full-Text Search
2. CLR integration
3. Temporal support
4. Leveraging external engines: R, Python, Java
5. XML Support in SQL Server
6. Using JSON
7. CLR and Spatial data
8. Graph data

Course: SQL Database Internals, Query Optimization, and Transactions (SQL-OT)

Summary

Even the best logical model can’t help when the physical implementation is bad. Therefore, it is important to know how SQL Server, Azure SQL Database, and partially also Azure Synapse Analytics, store data in row and columnar format and how the engines access this data. You will learn about heaps, balanced trees, and columnar indexes. In addition, this course introduces query optimization for both, transactional and analytical queries.

In order to achieve a good compromise between data integrity and performance suitable for the business needs, it is essential to select the appropriate transaction isolation level. A developer needs to thoroughly understand the possible problems by selecting an inappropriate isolation level. Fortunately, SQL Server offers all possible isolation levels with both, optimistic and pessimistic locking. Memory-optimized tables do not use locking, but introduce their own set of possible issues. With locking hints, it is possible to control the locking on a lower granularity than with the session-level options. The seminar explains the possible problems and shows how the problems are solved with different transaction isolation levels.

Basic familiarity with SQL Server is recommended for this course. If you are a complete beginner in this area, then we recommend that you watch the Starting with SQL Server and Azure SQL Database course first.

Abstract

You will learn about physical structures in a SQL Server database, including pages, extents, heaps, and balanced trees. By knowing the internals, you will be able to use the appropriate structure and design optimal indices. The seminar will guide you how to write efficient queries and support them with indices for a transactional system. Optimizing storage and queries for analytical applications is many times quite different than for transactional applications. In this module, you will learn about the columnar storage inside SQL Server and how to write efficient analytical queries.

In the pessimistic concurrency approach, locks are used to prevent users from modifying data in a way that affects other users. Once a lock has been applied, other users cannot perform actions that would conflict with the lock until the owner releases it. Locks are used for reading and writing. Only one copy of data elements at any time. Update conflicts can’t occur.

In the optimistic concurrency approach, multiple copies (with multiple versions) of data elements are created. The copies are from the state before updates for on-disk tables and show the new data for in-memory tables. Locks might be used for writing only for on-disk tables. Readers read stored old versions of data elements. Update conflicts can occur. Memory-optimized tables always use the optimistic approach. There is no locking at all. The module starts by introducing the memory-optimized tables. The worst issue with the locks is deadlocks. The module explains how a deadlock can occur, and shows how to solve it. Then the module introduces table-level locking hints.

Modules:

1. Table and index structures
2. Optimizing queries for a transactional system
3. Columnar storage and optimizing analytical queries
4. Pessimistic isolation levels
5. Optimistic isolation levels
6. Memory-optimized tables and isolation levels
7. Deadlocks and locking hints

Course: Advanced Analytics with Transact-SQL (SQL-AA)

Description

If you want to learn how to get information from your data with Transact-SQL, or shortly T-SQL language, then this course is the course for you. It will teach you how to calculate statistical measures from descriptive statistics including centers, spreads, skewness and kurtosis of a distribution, find the associations between pairs of variables, including calculating the linear regression formula, calculate the confidence level with definite integration, find the amount of information in your variables, and do also some machine learning or data science analysis, including predictive modeling and text mining.

T-SQL language in latest editions of SQL Server, Azure SQL Database, and Azure Synapse Analytics, has so many business intelligence (BI) improvements that it might become your primary analytic database system. Many database developers and administrators are already proficient with T-SQL. Occasionally they need to analyze the data with statistical or data science methods, but they do not want to or have time to learn a completely new language for these tasks. In addition, they need to analyze huge amounts of data, where specialized languages like R and Python might not be fast enough. SQL Server has been optimized for work with big datasets for decades.

In order to get the maximum out of these language constructs, you need to learn how to properly use them. This in-depth course shows extremely efficient statistical queries that use the window functions and are optimized through algorithms that use mathematical knowledge and creativity. The formulas and usage of those statistical procedures are explained as well.

Any serious analysis starts with data preparation. The course introduces some common data preparation tasks and shows how to implement them in T-SQL. No analysis is good without good data quality. The course introduces data quality issues, and shows how you can check for completeness and accuracy with T-SQL, and how to measure improvements of data quality over time. And since the talk is already about the time, the course shows how you can optimize queries with temporal data, for example when you search for overlapping intervals. More advanced time-oriented information includes hazard and survival analysis.

Then the course switches to the currently most fashionable topic, the data science. Some of quite advanced algorithms can also be implemented in T-SQL. The reader learns about the market basket analysis with association rules using different measures like support and confidence, and even sequential market basket analysis, when there is a sequence in the basket. Then the course shows how to develop predictive models with a mixture of k-nearest neighbor and decision trees algorithms and with Bayesian inference analysis.

Analyzing text, or text mining, is another modern topic. However, many people do not realize that you can do really a lot of text mining also in pure T-SQL. SQL Server can also become a text mining engine. The course shows how to analyze text in multiple natural languages with pure T-SQL, using also features from the full-text search (FTS).

In short, this course teaches you how to use T-SQL for:

• Statistical analysis
• Data science methods
• Text mining.

What Attendees Will Learn?

• Describe the distribution of a variable with statistical measures.
• Find associations between pairs of variables.
• Evaluate the quality of the data.
• Analyze data over time.
• Do the market basket analysis.
• Predict outcome of a target variable by using few input variables.
• Extract the key words from text data in order to categorize the texts.

Audience

Advanced Analytics with Transact-SQL is a course for database developers and database administrators who want to take their T-SQL programming skills to the max. It is for the attendees who want to analyze huge amounts of data in an efficient way by using their existing knowledge of the T-SQL language. It is also for the attendees who want to improve their querying by teaching them new and original optimization techniques.

Modules:

1. Descriptive Statistics
2. Associations between Pairs of Variables
3. Data Preparation
4. Data Quality
5. Time-Oriented Data
6. Time-Oriented Analyses
7. Data Mining
8. Text Mining