Advanced T-SQL for Developers and DBAs

Zenler Player

Your course is loading. Hang tight.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Summary and further steps