Advanced T-SQL for Developers and DBAs

Take your T-SQL skills to the next level

Enroll now for $295 USD !

Course Summary

  • Have you been working with T-SQL for a while but not sure you’re using it well?
  • Do you think there are probably simpler ways to write your T-SQL queries?
  • Do your queries look awkward and not have the elegance that other people’s queries seem to have?
  • Do you know the basics but think it’s time to extend your knowledge?
  • Do you want to learn from an expert

If any of these apply to you, this course is for you! And as well as detailed instruction, the course also offers optional practical exercises and quizzes to reinforce your learning.


Enroll now for $295 USD !

Modules and Lessons

Module 0: Getting Started

Who is this course for ?
Who is Greg ?
What will I learn in this course ?
Overview of lab setup

Module 1: Using Common Data Types Effectively

Exact Numeric Types
Approximate Numeric Types
Character Types
Unicode
Collations
SC Collations
UTF-8
Embedding Unicode with Unistr
Splitting Strings
Splitting Strings
Aggregating Strings
ANSI String Concatenation
LTRIM, RTRIM, and TRIM Extensions
Date and Time Data Types
Date and Time String Formats
Date and Time Functions
Getting the CURRENT_DATE
Date Truncation and Date Bucket
Quiz 1
Lab 1
Lab 1 Solution

Module 2: Using Special Data Types

HierarchyID
Rowversion
XML
UniqueIdentifier
Graph Data Types
Other Data Types
Sparse Data and Column Sets
Implicit and Explicit Data Type Casts
Concat
Concat with Separator
Generate Series
Is Distinct From
Working with Bits
Quiz 2
Lab 2
Lab 2 Solution

Module 3: Working with SQL Server Spatial Data

Target Applications
Types of Spatial Data
Flat Earth vs Round Earth
Open Geographic Consortium
Spatial Reference Systems
SQL Server Spatial Data Types
System CLR Types
Data Formats
Sources of Spatial Data
OGC Methods and Collections
Microsoft Extension Methods and Formats
Spatial Indexes
Spatial Index Restrictions
Circular Arc Support
Spatial Aggregates
Full Globe Support
Quiz 3
Lab 3
Lab 3 Solution

Module 4: Using Joins and Set Operations

Cross Join
Using Cross Join to Simplify Queries
Inner Join
Outer Joins
Self Join
Non-Equi Join
Multi-Table Join
Forcing Join Order
Join Strategies
Forcing Join Strategy
Union
Except
Intersect
Adaptive Joins
Quiz 4
Lab 4
Lab 4 Solution

Module 5: Using Subqueries and Common Table Expressions

Scalar Subqueries
Table Subqueries
Correlated Subqueries
Exists
Null Logic
Issues with Not In
Derived Tables
Row Constructors
Common Table Expressions
Recursive Common Table Expressions
OpenRowSet and OpenQuery
TableSample
Random Ordering and Sampling
Quiz 5
Lab 5
Lab 5 Solution

Module 6: Modifying Data

Identity Columns
Identity Insert
Multi-Row Insert
Select Into
Insert Exec
Delete with Join
Delete Duplicates with CTEs
Update and Assign
Merge
Output Clause
Sequences
Truncate Table
Quiz 6
Lab 6
Lab 6 Solution

Module 7: Ranking, Pivoting, and Grouping Data

Ranking Functions
Row Number
Rank
Dense Rank
Ntile
Partition By
Cumulative Distribution
Percent Rank
Percentile
Approximate Percentile
Other Uses for Over
Pivoting Data
Pivot
Unpivot
Rollup
Cube
Grouping
Grouping ID
Grouping Sets
Greatest and Least
Quiz 7
Lab 7
Lab 7 Solution

Module 8: Using TOP, APPLY and Window Functions

Top
Top with Ties
Top in Data Modification
Paging Rows
Offset and Fetch
Cross Apply
Outer Apply
Lag and Lead
Partitions Over Ranges
Named Window Clauses
NULL Treatment Clauses for Windows
Quiz 8
Lab 8
Lab 8 Solution

Module 9: Working with XML Data

Why XML Data in SQL Server
XML Data Type
For XML
OpenXML
Typed vs Untyped XML
XQuery
Nodes
XML Indexes
Promoting XML Nodes
Quiz 9
Lab 9
Lab 9 Solution

Module 10: Working with JSON Data

Why JSON Data in SQL Server?
Storing JSON Data in SQL Server
FOR JSON
INCLUDE_NULL_VALUES
Root and Array Options
OPENJSON
OPENJSON
JSON_VALUE
JSON_QUERY
JSON_MODIFY
ISJSON
JSON_PATH_EXISTS
JSON Constructors
JSON Aggregates
Quiz 10
Lab 10
Lab 10 Solution

Module 11: Using Full Text Indexing

What End Users Want
Limitations of Like
Full Text Terminology
Wordbreakers
Full Text Index Implementation
Full Text Internal Architecture
Stoplists
Indexing Performance
Upgrade Options
Property Scoped Searching
Customizable Proximity
Statistical Semantic Search
FileTable
Quiz 11
Lab 11
Lab 11 Solution

Module 12: Dynamic SQL

Dynamic SQL
Limitations of Dynamic SQL
Executing Dynamic SQL
sp_executesql
Execute with Result Sets
Quiz 12

Module 13: Creating Code Objects

Views
Stored Procedures
Input and Output Parameters
Return Values
Inline Scalar Functions
Multi-Statement Scalar Functions
Table-Valued Functions
T-SQL Function Performance
Types of Triggers
DML Triggers
DDL Triggers
Logon Triggers
Table Types
Table-Valued Parameters
Metadata Discovery
Quiz 13
Lab 13
Lab 13 Solution

Module 14: Using Error and Exception Handling

How T-SQL Code Executes
T-SQL and Errors
Error Levels
Available Error Information
Error Severities
@@Error
Raiserror
Throw
Try and Catch
Error Handling Functions
Transactions
Errors and Transactions
Nesting Transactions
Errors and Clients
Quiz 14

Module 15: Next steps

Summary and further steps


Enroll now for $295 USD !