Azure Synapse Details

For pricing and availability

Contact: Tom Coffing

Phone: 513 300-0341

Azure Synapse Architecture and SQL

Description: In this course, students will learn the Azure Synapse architecture with an excellent level of detail. Students will also learn the clever tricks and tips that make Azure Synapse unique. In addition, students will create all types of Synapse tables and views and learn how to performance tune Azure Synapse tables and queries—over 600 examples of managing, creating tables, and Azure Synapse SQL. The class is 90% hands-on training. The students will use the SQL_Class database with 18 tables representing the tables in the book.

Reference Guide: The book is an excellent book for teaching and makes for an excellent reference guide to help on the job.

Description: Students will learn Azure Synapse architecture and SQL starting at the most basic level and going to the most advanced level in this course.

Objectives: After this course, students will have detailed knowledge and understanding of the Azure Synapse architecture, creating and managing tables and objects, utilizing indexes, performance tuning, and essential, intermediate, and advanced SQL hands-on training.

Audience: This course is for anyone who desires to learn Azure Synapse architecture and SQL, from beginners to experienced users.

Prerequisites: None

Duration: 4-Days

Topics:

The Architecture of the Azure Synapse
The 5 Concepts of Azure Synapse Tables
Why Columnar?
Best Practices for Choosing a Distribution Key
Creating a Table That Has a Clustered Index
CREATE Statistics
Fully Qualifying a Database, Schema, and Table
Major Sort vs. Minor Sorts
The Like Command Wildcards are Percent and Underscore
Distinct, Group By, and TOP
There are Five Aggregates
Inner and Outer Joins
Date and Time Functions
Format Functions
Analytics and Window Functions
Temporary Tables
Subqueries
Strings
Interrogating the Data
Views
Set Operators
Creating Tables
Data Manipulation Language (DML)
User-Defined Functions (UDF)
Stored Procedures
Statistical Aggregate Functions
Workload Management
System Views
EXPLAIN Plans

Azure Synapse Architecture and SQL Course Outline

Chapter 1 – Introduction to the Azure SQL Data Warehouse

Introduction to the Family of SQL Server Products

Introduction to the Family Continued

Azure Synapse Data Warehouse

Symmetric Multi-Processing (SMP)

What is Parallel Processing?

The Basics of a Single Computer

Data in Memory is Fast as Lightning

Parallel Processing Of Data

Azure Synapse has Linear Scalability

A Table has Columns and Rows

The Architecture of the Azure Synapse

Nexus is Now Available on the Microsoft Azure Cloud

The MPP Engine is the Optimizer

Azure Synapse System

Azure Synapse System is Scalable

The Control Node

The Data Rack

The Landing Zone

The Backup Node

Software As A Service (SaaS) and the Elastic Database

Azure Data Lake

Azure Disaster Recovery

Security and Compliance

Gen 2 Synapse Capacity Limits

Chapter 2 – Azure Synapse Table Structures

The 5 Concepts of Azure Synapse Tables

Tables are Either Distributed by Hash or Replicated (1 of 5)

Clustered Columnstore Table

Heap Table

Table With a Clustered Index

Partitioned Table CREATE Example

Query to Check Partitioned Tables

Another Create Example of a Partitioned Table

Table With a Distribution Key and Columnar Storage

Creating a Table That is Replicated

Table With a Round Robin Strategy For Even Distribution

Round Robin Strategy – Columnar Table

Example of Tables by Hash or Replicated (1 of 5)

Table Rows are Either Sorted or Unsorted (2 of 5)

Tables are in Either Row or Columnar Format (3 of 5)

Tables Can Be Partitioned (4 of 5)

There Are Permanent, Temporary and External Tables (5 of 5)

Distributed by Hash vs. Replication

The Replication Concept is All About the Joins

Creation of a Hash Distributed Table With a Clustered Index

A Clustered Index Sorts the Data Stored on Disk

Each Node Has 8 Distributions

How Hashed Tables Are Stored Among a Single Node

Hashed Tables Will Distribute Among All Distributions

Creation of a Replicated Table

How Replicated Tables Store Among a Single Node

Replicated Table will Duplicate among Each Node

Distributed by Replication

How Hashed and Replicated Tables Work Together

Tables Store as Row-based or Column-based

Creation of a Columnar Table That Hashes

How Hashed Columnar Tables Store on a Single Node

How Hashed Columnar Tables Stores on All Distributions

Comparing Normal Table Vs. Columnar Tables

Columnar can Move just One Segment to Memory

Segments on Distributions are Aligned to Rebuild a Row

Why Columnar?

Columnar Tables Store Each Column in Separate Pages

Visualize the Data – Rows vs. Columns

Creation of a Columnar Table That is Replicated

Creating a Partitioned Table Per Month

A Visual of One Year of Data with Range Per Month

Another Create Example of a Partitioned Table

Creating a Partitioned Table Per Month That is a Columnstore

Visual of Row Partitioning and Columnar Storage

CREATE TABLE AS (CTAS) Example

Creating a Temporary Table

Facts About Tables

Chapter 3 – Hashing and Data Distribution

Distribution Keys Hashed on Unique Values Spread Even

Distribution Keys With Non-Unique Values Spread Unevenly

Best Practices for Choosing a Distribution Key

The Hash Map Determines which Distribution owns the Row

The First Row is Delivered

The Third Row is Delivered to its Destination Distribution

The Rows Distribute Evenly Because they are Unique Values

A Review of the Hashing Process

Non-Unique Distribution Keys have Skewed Data

Chapter 4 – The Technical Details

Every Distribution has the Exact Same Tables

Hashed Tables are spread across All Distributions

The Table Header and the Data Rows are Stored Separately

To Read a Data Block a Distribution Moves it Into Memory

A Full Table Scan Means All Distributions Read All Rows

Rows Organize inside a Page

Moving Data Blocks is Like Checking In Luggage

As Row-Based Tables Get Bigger, the Page Appends

Data Pages Are Processed One at a Time Per Unit

Creating a Table That is a Heap

Heap Page

Creating a Table That Has a Clustered Index

Clustered Index Page

The Row Offset Array is the Guidance System For Every Row

The Row Offset Array Provides Two Search Options (2 of 2)

The Row Offset Array Helps With Inserts

The Building of a B-Tree For a Clustered Index (1 of 3)

The Building of a B-Tree For a Clustered Index (2 of 3)

The Building of a B-Tree For a Clustered Index (3 of 3)

When Do I Create a Clustered Index?

When Do I Create a Non-Clustered Index?

B-Tree For Non-Clustered Index on a Clustered Table (2 of 2)

Adding A Non-Clustered Index To A Heap

Max Levels on the Azure Synapse

Chapter 5– CREATE Statistics

CREATE Statistics Syntax

CREATE Statistics on a Percentage of a Table

CREATE Statistics on a Sample By Using the System Default

CREATE Statistics on a Multi-Column Join Key

What Column(s) to CREATE Statistics On

CREATE Statistics Using a WHERE Clause

Updating Only Certain Statistics on a Table

Dropping Statistics On Certain Statistics on a Table

Showing the Statistics

DBCC SHOW_STATISTICS

DBCC SHOW_STATISTICS WITH HISTOGRAM

Chapter 6 - The Basics of SQL

Naming of Objects

Setting Your Default Database

Fully Qualifying a Database, Schema, and Table

SELECT * (All Columns) in a Table

SELECT Specific Columns in a Table

Commas in the Front or Back?

Use the Name or the Number in your ORDER BY Statement

Two Examples of ORDER BY using Different Techniques

Changing the ORDER BY to Descending Order

NULL Values sort First in Ascending Mode (Default)

Major Sort vs. Minor Sorts

Multiple Sort Keys using Names vs. Numbers

An Order By That Uses an Expression

Sorts are Alphabetical, NOT Logical

Using A CASE Statement to Sort Logically

Quiz - Can You Add a Minor Sort?

Answer - Can You Add a Minor Sort?

Quiz - Change the CASE Statement to a Searched Case

How to ALIAS a Column Name

Aliasing a Column Name With Spaces or Reserved Words

Using the Alias in the ORDER BY Statements

A Missing Comma can by Mistake become an Alias

Comments using Double Dashes are Single Line Comments

Comments for Multi-Lines

Comments for Multi-Lines As Double Dashes Per Line

A Great Technique for Comments to Look for SQL Errors

Chapter 7 – The Where Clause

The WHERE Clause limits Returning Rows

Double Quoted Aliases are for Reserved Words and Spaces

Using A Column ALIAS In A WHERE Clause

Using A Column ALIAS in an ORDER BY Clause

The Order Azure Synapse Process Queries

Character Data needs Single Quotes in the WHERE Clause

Not Equal

Declaring a Variable

Comparisons Against a Null Value

Use IS NULL or IS NOT NULL when dealing with NULLs

NULL is UNKNOWN DATA so NOT Equal won't Work

Use IS NULL or IS NOT NULL when dealing with NULLs

Using Greater Than Or Equal To (>=)

AND in the WHERE Clause

Troubleshooting AND

OR in the WHERE Clause

Troubleshooting OR

WHY OR must utilize the Column Name Each Time

Troubleshooting Character Data

Using Different Columns in an AND Statement

Quiz – How many rows will return?

Answer to Quiz – How many rows will return?

What is the Order of Precedence?

Using Parentheses to change the Order of Precedence

Using an IN List in place of OR

The IN List is an Excellent Technique

IN List vs. OR Brings the Same Results

The IN List Can Use Character Data

Using a NOT IN List

Null Values in a NOT IN List Return No Rows

A Technique for Handling Nulls with a NOT IN List

Technique 2 for Handling Nulls with a NOT IN List

The BETWEEN Statement is Inclusive

The NOT BETWEEN Statement is also Inclusive

The BETWEEN Statement Works for Character Data

The Like Command Wildcards are Percent and Underscore

LIKE command Underscore is Wildcard for one Character

CASE Does Not Matter with the LIKE Command

Using LIKE for all Cases with Lower and Upper

Using ASCII When You Want Case Comparisons

Another Example of UPPER and LOWER

LIKE Command to Find Multiple Characters

LIKE Command to Find Either Character

Finding Anyone Whose Name Ends in 'Y'

LIKE command Using a Range of Values

LIKE command Using a NOT Range of Values

Declaring a Variable and Using it with the LIKE Command

Chapter 8 – Distinct, Group By, and TOP

The Distinct Command

Distinct vs. GROUP BY

Quiz – How many rows come back from the Distinct?

How many rows will come back from the above SQL?

Answer – How many rows come back from the Distinct?

Top Command

The TOP Command WITH TIES

The TOP Command Using a Variable

Chapter 9 – Aggregation

Quiz – Calculate the Answer Set in your Mind

Answer – Calculate the Answer Set in your Mind

Quiz 2– Calculate the Answer Set in your Mind

Answer Quiz 2– Calculate the Answer Set in your Mind

There are Five Aggregates

Quiz – How many Rows Return?

Answer – How many Rows Return?

Casting a Data Type

Troubleshooting Aggregates

GROUP BY Delivers One Row Per Group

Limiting Rows and Improving Performance with WHERE

WHERE Clause in Aggregation limits unneeded Calculations

Keyword HAVING tests Aggregates after they are Totaled

Keyword HAVING is like an Extra WHERE Clause for Totals

Group By Grouping Sets

Chapter 10 - Join Functions

Azure Synapse Join Quiz

Azure Synapse Join Quiz Answer

Big Table Small Table Join Strategy

Duplication of the Smaller Table across All-Distributions

If the Join Condition is the Distribution Key No Movement

Matching Rows On The Same Distribution Naturally

What if the Join Condition Columns are Not Distribution Keys

Strategy 1 of 3 – The Merge Join

Quiz – Redistribute the Employees by their Dept_No

Quiz –Dept_No landed on Distribution with Matches

Quiz – Redistribute the Orders to the Proper Distribution

Answer to Redistribute the Employees by their Dept_No Quiz

Strategy 2 of 3 – The Hash Join

Strategy 3 of 3 – The Product Join

A Two-Table Join Using Traditional Syntax

A Join using Traditional Syntax with Table Alias

You Can Fully Qualify All Columns

Both Queries Have the Same Results and Performance

Quiz – Can You Finish the Join Syntax?

Answer to Quiz – Can You Finish the Join Syntax?

Quiz – Can You Find the Error?

Answer to Quiz – Can You Find the Error?

Super Quiz – Can You Find the Difficult Error?

Answer to Super Quiz – Can You Find the Difficult Error?

Quiz – Which Rows from Both Tables Won't Return?

Answer– Which Rows from Both Tables Won't Return?

LEFT OUTER JOIN

LEFT OUTER Join Results

RIGHT OUTER JOIN

RIGHT OUTER Join Example and Results

FULL OUTER JOIN

FULL OUTER Join Results

Which Tables are the Left and Which are Right?

Answer - Which Tables are the Left and Which are Right?

INNER JOIN with Additional AND Clause

ANSI INNER JOIN with Additional AND Clause

OUTER JOIN with Additional WHERE Clause

OUTER JOIN with Additional AND Clause

The DREADED Product Join

The DREADED Product Join Results

Cartesian Product Join with Traditional Syntax

Cartesian Product Join with ANSI Syntax

The CROSS JOIN

The CROSS JOIN Answer Set

The SELF JOIN

The SELF JOIN with ANSI Syntax

An Associative Table is a Bridge that Joins Two Tables

Quiz – Can you Write the Three-Table Join?

Quiz – Can you Place the ON Clauses at the End?

Answer – Can you Place the ON Clauses at the End?

The Five-Table Join – Logical Insurance Model

Quiz - Write a Five Table Join Using ANSI Syntax

Answer - Write a Five Table Join Using ANSI Syntax

Quiz - Write a Five Table Join Using Traditional Syntax

Answer - Write a Five Table Join Using Non-ANSI Syntax

Chapter 11– Date Functions

DATEADD Function

A Real-World Example for DATEADD Using the Order Table

DATEPART Function

DATEPART Function Examples

YEAR, MONTH, and DAY Functions

A Better Technique for YEAR, MONTH, and DAY Functions

DATENAME Function

Incrementing Date Values Using the Dateadd Function

Incrementing Time Values Using the Dateadd Function

Dateadd Function Details

Formatting Dates for the United States and Great Britain

Formatting Dates for Germany and China

Formatting Numeric Data

Formatting Time

Formatting Time Some More

DATEDIFF Using Techniques forAliasing and Date Formats

GETDATE and GETUTCDATE

ISDATE Function

Chapter 12 - Temporary Tables

Temporary Tables

Naming the Derived Table

Aliasing the Column Names in The Derived Table

Multiple Ways to Alias the Columns in a Derived Table

CREATING A Derived Table using the WITH Command

The Same Derived Query is shown Three Different Ways

MULTIPLE Derived Tables using the WITH Command

Column Alias Can Default For Normal Columns

Most Derived Tables Are Used To Join To Other Tables

A Join Example Showing Different Column Alias Styles

The Three Components of a Derived Table

Visualize This Derived Table

Our Join Example With The WITH Syntax

Quiz - Answer the Questions

Answer to Quiz - Answer the Questions

Clever Tricks on Aliasing Columns in a Derived Table

A Derived Table Lives Only for the Life the Query

An Example of Two Derived Tables in a Single Query

What is TEMPDB?

Creating a Temporary Table

The Three Steps to Use a Temporary Table

Creating a Temporary Table With a Clustered Index

Creating a Columnstore Temporary Table From a CTAS

External Tables

External Table Examples

Creating an External Table

Chapter 13 – Sub-query Functions

An IN List is much like a Subquery

An IN List Never has Duplicates – Just like a Subquery

An IN List Ignores Duplicates

The Subquery

The Three Steps of How a Basic Subquery Works

These are Equivalent Queries

The Answer Set from the Subquery

Quiz- Answer the Difficult Question

Should you use a Subquery or a Join?

Quiz - Write the Subquery

Answer to Quiz- Write the Subquery

Quiz - Write the More Difficult Subquery

Answer to Quiz - Write the More Difficult Subquery

Quiz – Write the Extreme Subquery

Answer to Quiz – Write the Extreme Subquery

Quiz - Write the Subquery with an Aggregate

Answer to Quiz- Write the Subquery with an Aggregate

Quiz- Write the Correlated Subquery

Answer to Quiz- Write the Correlated Subquery

The Basics of a Correlated Subquery

Correlated Subquery Example vs. a Join with a Derived Table

Quiz- A Second Chance to Write a Correlated Subquery

Answer - A Second Chance to Write a Correlated Subquery

Quiz- A Third Chance to Write a Correlated Subquery

Answer - A Third Chance to Write a Correlated Subquery

Quiz- Last Chance To Write a Correlated Subquery

Answer – Last Chance to Write a Correlated Subquery

Quiz – Write the Extreme Correlated Subquery

Answer To Quiz – Write the Extreme Correlated Subquery

NOT IN Subquery Returns Nothing when NULLs are Present

Fixing a NOT IN Subquery with Null Values

Quiz- Write the NOT IN Subquery

Answer to Quiz- Write the NOT Subquery

Quiz - Write the Subquery using a WHERE Clause

Answer - Write the Subquery using a WHERE Clause

Quiz- Write the Subquery with Two Parameters

Answer to Quiz- Write the Subquery with Two Parameters

How the Double Parameter Subquery Works

More on how the Double Parameter Subquery Works

Quiz – Write the Triple Subquery

Answer to Quiz – Write the Triple Subquery

IN is equivalent to =ANY

Using a Correlated Exists

How a Correlated Exists Matches Up

Chapter 14 – Analytics

Quiz – How did the Row_Number Reset?

Answer – How did the Row_Number Reset?

Filtering With a Derived Table

Quiz – Return Two Students Per Class_Code with Highest Grades

RANK vs. DENSE_RANK

Getting RANK to Sort in DESC Order

Using a Derived Table

PERCENT_RANK() OVER with 14 rows in Calculation

PERCENT_RANK() OVER with 21 rows in Calculation

PERCENT_RANK and PARTITION BY

Cumulative Sum

Cumulative Sum – The Sort Explained

Cumulative Sum – Rows Unbounded Preceding Explained

Cumulative Sum – Major and Minor Sort Keys

Reset with a PARTITION BY Statement

Totals and Subtotals through Partition By

Moving Sum

Moving SUM Every Three Rows vs. a Continuous Average

Partition By Resets the Calculations

Moving Average

The Moving Window is Current Row and Preceding n

How Moving Average Handles the Order By

Quiz – How is that Total Calculated?

Answer to Quiz – How is that Total Calculated?

Answer to Quiz – How is that 4th Row Calculated?

Moving Average every 3-rows Vs. a Continuous Average

The Partition By Statement

Partition By Resets an ANSI OLAP

Moving Difference

Moving Difference with Partition By

Finding a Value of a Column in the Next Row with MIN

Finding a Next Row Value with MIN and PARTITION BY

Finding Multiple Values of a Column in Upcoming Rows

Finding The Next Date using MAX

COUNT OVER for a Sequential Number

.COUNT OVER using ROWS UNBOUNDED PRECEDING

The MAX OVER Command

MAX OVERwith PARTITION BY Reset

The MIN OVER Command

The MIN OVER Command with PARTITION BY

Using FIRST_VALUE

FIRST_VALUE With Partitioning

Daily_Sales Minus FIRST_VALUE With Partitioning

FIRST_VALUE After Sorting by the Highest Value

FIRST_VALUE and a Derived Table

Last_Value Can Be Confusing

Last_Value Working Properly

Last_Value With Partitioning

Last_Value And First_Value with Partitioning

First and Last Value Difference

Using LEAD

Using LEAD with a PARTITION Statement

Using LEAD With an Offset of 2

Using LEAD With an Offset of 2 and a PARTITION

Using LAG

Using LAG with a PARTITION Statement

Using LAG With an Offset of 2

Using Two LAG Statements

Using LAG With an Offset of 2 and a PARTITION

CUME_DIST With a Tie Value

CUME_DIST and a Derived Table

CUME_DIST and a Partition By Statement

CUME_DIST with 14 Rows

CUME_DIST With a Partition on 7 Rows

CURRENT ROW AND UNBOUNDED FOLLOWING

Different Windowing Options

How Ntile Works

Ntile Example

Ntile Continued

Ntile Percentile

Using Quantiles (Partitions of Four)

Using Deciles (Partitions of Ten)

NTILE With a Partition

PERCENTILE_CONT Function Description and Syntax

Result Information About PERCENTILE_CONT

PERCENTILE_CONT Function Arguments

PERCENTILE_CONT Example

PERCENTILE_CONT Example with Percentage Change

PERCENTILE_CONT With PARTITION Example

PERCENTILE_CONT With PARTITION and (0.4)

PERCENTILE_DISC Function Description and Syntax

PERCENTILE_DISC Function Arguments

PERCENTILE_DISC Example

PERCENTILE_DISC Example with Percentage Change

PERCENTILE_DISC With PARTITION Example

PERCENTILE_DISC With PARTITION and (0.4)

Chapter 15 - Working with Strings

The SUBSTRING Command

Using SUBSTRING to move Backwards

How SUBSTRING Works with a Starting Position of -1

How SUBSTRING Works with an Ending Position of 0

Concatenation and SUBSTRING

SUBSTRING and Different Aliasing

Four Concatenations Together

Concat and Concat_WS for Concatenation

CHARINDEX Function Finds a Letter(s) Position in a String

The LEFT and RIGHT Functions

The LEN Function

The DATALENGTH Function

UPPER and lower Functions

The LTRIM and RTRIM Command trims Spaces

PATINDEX Function to Find a Character Pattern

The REPLACE Function

LEN and REPLACE Functions for Number of Occurrences

The REVERSE String Function

The RIGHT Function

STUFF Without Deleting Function

The DATALENGTH Function and RTRIM

A Visual of the TRIM Command Using Concatenation

The CHARINDEX Command is brilliant with SUBSTRING

The CHARINDEX Command Using a Literal

PATINDEX Function

SOUNDEX Function to Find a Sound

DIFFERENCE Function to Quantile a Sound

Chapter 16 - Interrogatingthe Data

Quiz – What would the Answer be?

Answer to Quiz – What would the Answer be?

The NULLIF Command

Quiz – Fill in the Answers for the NULLIF Command

Answer– Fill in the Answers for the NULLIF Command

The COALESCE Command – Fill In the Answers

The COALESCE Answer Set

Quiz – Who Are You Going to Call?

Answer – Who Are You Going to Call?

COALESCE is Equivalent to this CASE Statement

Some Great CAST (Convert And Store) Examples

A Rounding Example

Using an ELSE in the Case Statement

Using an ELSE as a Safety Net

Rules For a Searched Case Statement

Valued Case Vs. A Searched Case

Quiz - Valued Case Statement

Answer - Valued Case Statement

Quiz - Searched Case Statement

Quiz - When NO ELSE is present in CASE Statement

Answer - When NO ELSE is present in CASE Statement

Quiz -When an Alias is NOT used in a CASE Statement

Combining Searched Case and Valued Case

A Trick for getting a Horizontal Case

Nested Case

Put a CASE in the ORDER BY

Chapter 17– Table Create and Data Types

Creating a Database

Best Practices for Azure Synapse Data Types

Azure Synapse Data Types

Character Data Types for SQL Server

Numeric Data Types for SQL Server

Date and Time Data Types for SQL Server

The Three Major Table Types

Clustered Columnstore Table

Heap Table

Table With a Clustered Index

Partitioned Table

Query to Check Partitioned Tables

Another Create Example of a Partitioned Table

Creating a Table With a Distribution Key

Creating a Table with a Style of Replicate

Table With a Round Robin Strategy – Even Distribution

Round Robin Strategy – Columnar Table

Tables Can Be Partitioned (4 of 5)

Creation of a Hash Distributed Table With a Clustered Index

Creation of a Replicated Table

Creation of a Columnar Table That Hashes

A Visual of One Year of Data with Range Per Month

Creating a Partitioned Table Per Month That is a Columnstore

Query to Check Partitioned Tables

Heap Tables are Not Sorted

Heap Page

Creating a Table That Has a Clustered Index

Clustered Index Page

When Do I Create a Clustered Index?

The Building of a B-Tree For a Clustered Index (1 of 3)

The Building of a B-Tree For a Clustered Index (2 of 3)

The Building of a B-Tree For a Clustered Index (3 of 3)

The Row Offset Array is the Guidance System For Every Row

The Row Offset Array Provides Two Search Options (1 of 2)

The Row Offset Array Provides Two Search Options (2 of 2)

The Row Offset Array Helps With Inserts

What is a Uniquefier?

Adding An Index

When Do I Create a Non-Clustered Index?

B-Tree For Non-Clustered Index on a Clustered Table (1 of 2)

B-Tree For Non-Clustered Index on a Clustered Table (2 of 2)

Adding A Non-Clustered Index To A Heap

B-Tree For Non-Clustered Index on a Heap Table (1 of 2)

B-Tree For Non-Clustered Index on a Heap Table (2 of 2)

Default Values

CREATE TABLE AS

CREATE Temporary TABLE AS

Chapter 18 – View Functions

The Fundamentals of Views

Creating a Simple View to Restrict Sensitive Columns

Creating a Simple View to Restrict Rows

Basic Rules for Views

Two Exceptions to the ORDER BY Rule inside a View

Views sometimes CREATED for Row Security

Creating a View to Join Tables

Another Way to Alias Columns in a View CREATE

The Standard Way for Aliasing

What Happens When Both Aliasing Options Are Present

Resolving Aliasing Problems in a View CREATE

What will happen in the above query?

Answer to Resolving Aliasing Problems in a View CREATE

Aggregates on View Aggregates

Altering A Table

Altering a Table after the Creation of a View

A View that Errors After An ALTER

Troubleshooting a View

Loading Data through a View

Chapter 19 – Data Manipulation Language (DML)

INSERT Syntax # 1

INSERT Example with Syntax 1

INSERT Syntax # 2

INSERT Example with Syntax 2

INSERT/SELECT Command

INSERT/SELECT Example using All Columns (*)

INSERT/SELECT Example with Less Columns

The UPDATE Command Basic Syntax

Two UPDATE Examples

Subquery UPDATE Command Syntax

Example of Subquery UPDATE Command

Join UPDATE Command Syntax

Example of an UPDATE Join Command

The DELETE Command Basic Syntax

Two DELETE Examples to DELETE ALL Rows in a Table

To DELETE or to TRUNCATE?

A DELETE Example Deleting only Some of the Rows

Subquery and Join DELETE Command Syntax

Example of Subquery DELETE Command

MERGE INTO

MERGE INTO Continued

Chapter 20 – Set Operators Functions

Rules of Set Operators

INTERSECT Explained Logically

UNION Explained Logically

UNION ALL Explained Logically

EXCEPT Explained Logically

Another EXCEPT Example

EXCEPT Explained Logically in Reverse Order

An Equal Amount of Columns in both SELECT List

The Top Query handles all Aliases

The Bottom Query does the ORDER BY

Great Trick:Place your Set Operator in a Derived Table

UNION Vs. UNION ALL

Using UNION ALL and Literals

A Great Example of how EXCEPT works

This query brought back all Departments without any employees.

USING Multiple SET Operators in a Single Request

Changing the Order of Precedence with Parentheses

Building Grouping Sets Using UNION

Three Grouping Sets Using a UNION

Chapter 21 – Stored Procedures and User-Defined Functions (UDFs)

Nexus Servers - Control Network and Data Movement

Creating a User-Defined Function

Another User-Defined Function Example

A User-Defined Function with an Input Parameter

User-Defined Function to Find Views and Stored Procedures

Creating a Stored Procedure

Executing a Stored Procedure

There are Three Ways to Execute a Stored Procedure

Creating a Stored Procedure With a CASE Statement

Our Answer Set

Dropping a Stored Procedure

Passing an Input Parameter to a Stored Procedure

Executing With Positional Parameter vs. Named Parameters

Passing an Output Parameter to a Stored Procedure

Changing a Stored Procedure with an ALTER

Answer Set for the Altered Stored Procedure

Using a Stored Procedure To Delete a Row

A Different Method To Delete a Row

Deleting a Row Using an Input Parameter

Using Loops in Stored Procedures

Stored Procedure Workshop

Looping with a WHILE Statement

Create a Stored Procedure that Sorts Using a Valued CASE

Answer - Create a Stored Procedure that Sorts Using CASE

Create a Stored Procedure that Sorts Using a Searched CASE

Answer - Create a Stored Procedure that Sorts Using CASE

Passing an Input Parameter to a Stored Procedure

Executing With Positional Parameters vs. Named Parameters

Quiz - Using IF, ELSE IF, and ELSE

Answer - Using IF, ELSE IF, and ELSE

Quiz - Using the PRINT Command

Answer - Using the PRINT Command

Quiz - Using the CASE Command

Answer - Using the CASE Command

QUIZ – Get a Horizontal Case

Answer – Get a Horizontal Case

Another Way to Display a Stored Procedure's Definition

Quiz - Aggregation in a Stored Procedure With CAST

Answer - Aggregation in a Stored Procedure With CAST

Using PIVOT

Using PIVOT For Daily Data

Using PIVOT For Quarterly and Yearly Data

Using PIVOT For Monthly Data

Using UNPIVOT

Using Dynamic SQL – Method One

Using Dynamic SQL – Method Two

Special Stored Procedures and Static Queries

Special Stored Procedures and Dynamic Queries

Chapter 22 – Statistical Aggregate Functions

The STATS_TABLE

The VAR and VARP Functions

A VAR Example

A VARP Example

The STDEV and STDEVP Functions

A STDEV Example

A STDEVP Example

Chapter 23 – Systems Views

System Views

sys.all_columns

sys.all_objects

sys.all_sql_modules

sys.all_views

sys.columns

sys.data_spaces

sys.database_principals

sys.database_role_members

sys.databases

sys.filegroups

sys.identity_columns

sys.objects

sys.partition_range_values

sys.schemas

sys.server_role_members

Chapter 24 – Resource Classes

What are Resource Classes?

A Larger Resource Class can Improve Query Performance

Smaller Resource Classes Help with Concurrency

The Two Types of Resource Classes

Static Resource Classes

Dynamic Resource Classes

Operations that are Not Governed by Resource Classes

What are Concurrency Slots?

Viewing the Resource Classes

Resource Classes have a Precedence

Best Practices

Adjust Classifier and Run Again

ETL Workload Classifier

Chapter 25 – Monitoring Jobs

What are Dynamic Management Views?

Monitoring Connections

Monitoring Connections Example

Monitoring Query Execution

Finding the Top 20 Longest Running Queries

Simplifying the Lookup of a Query using LABEL

Investigating the Query Plan

Investigate SQL on the Distributed Databases

Investigate Data Movement on the Distributed Databases

Monitor and Investigate Waiting Queries

Queued Queries

Monitoring tempdb

Monitoring Memory

Monitor Transaction Log Size and Transaction Log Rollback

Monitor PolyBase load

Chapter 26 – Labs and EXPLAIN

Clustered Columnstore Index

Ordered Clustered Columnstore Index with Round Robin

Heap Tables for Staging

Clustered Index

Adding a Clustered Index to an Existing Table

Adding a Non-Clustered Index to an Existing Table s

Distribution = Hash

Populate Tables with an INSERT/SELECT

Explain Select from Columnstore

Explain Join with NO Data Movement

Explain Subquery with NO Data Movement

Explain Join with Data Movement

Explain Subquery with Data Movement

Explain Correlated Subquery

Explain Aggregate Query

Explain Aggregate Query with Dept_No = Hash

BACK TO BIG DATA CLASSES