Azure Synapse Architecture and SQL – Software connecting all databases

For pricing and availability
Contact: Tom Coffing
Email: Tom.Coffing@CoffingDW.com
Phone: 513 300-0341

Description

You can expect three things from a Tom Coffing Azure Synapse Architecture and SQL class: hands-on, hands-on, and hands-on training. Students will begin each chapter covering the fundamentals and continue to step up to the most advanced techniques in a building block approach. Each student can create tables, understand different data distribution options, create indexes, run thousands of queries, and join many tables combined with subqueries, derived tables, date functions and formats, and advanced analytics. We can also customize the course based on your needs. Welcome to one of the best classes you will ever attend.

Reference Guide

The Azure Synapse Architecture and SQL book by Tom Coffing and David Cook is a 1296-page masterpiece of clear explanations into the Synapse architecture, tips and tricks for performance tuning, and every SQL statement. The PDF delivered to each student will be the backbone of the class and provide an excellent reference guide for all work done on any Synapse system.

Objectives

Whether you are new to Azure Synapse and SQL or already have enormous experience, Tom Coffing will enrich your knowledge immensely. One thing is for sure: you will be extremely confident and proficient at working with an Azure Synapse system and writing Synapse SQL.

Audience, prerequisites, and duration

This course is for everyone who has the opportunity to work on an Azure Synapse system, from beginners to experts. There are never any prerequisites in a Tom Coffing classroom because Tom has mastered mixing new and experienced students within the same classroom. The course ranges from 2-5 days, depending on your needs, but the more days you choose, the more incredible the outcome will be, as we will customize the classes based on your needs.

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 Azure Synapse

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
Extents
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
B-Trees
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

Introduction
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

Current_Timestamp
Getdate
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 for Aliasing 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
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

ROW_NUMBER
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
Dense_Rank
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 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 OVER with PARTITION BY Reset
The MIN OVER Command
The MIN OVER Command with PARTITION BY
FIRST_VALUE
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
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
Concatenation
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
QUOTENAME
The REPLACE Function
LEN and REPLACE Functions for Number of Occurrences
REPLICATE Function
The REVERSE String Function
The RIGHT Function
STUFF 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
SPACE

Chapter 16 – Interrogating the 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
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
Extents
Creating a Table That Has a Clustered Index
Clustered Index Page
When Do I Create a Clustered Index?
B-Trees
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
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
INTERSECT Explained Logically
UNION Explained Logically
UNION Explained Logically
UNION ALL 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

Chapter 27 – Mathematical Functions

Numeric Manipulation Functions
ABS
ACOS
ASIN
Ceiling
Floor
COS
DEGREES
EXP
POWER
SIGN
SIN
SQRT