SQL Server & T-SQL Training (LIVE, Instructor-Led)

DAY 1: INTRODUCTION, INSTALLATION

• Data, Databases and RDBMS Software
• Database Types : OLTP,DWH,OLAP,HTAP
• Microsoft SQL Server Advantages, Use
• DB Engine, BI, Data Science Components
• SQL : Purpose, Real-time Usage Options
• SQL Versus Microsoft T-SQL [MSSQL]
• Microsoft SQL Server - Career Options

• Real-time Projects & Job Responsibilities
• SQL Server @ Cloud: Azure, AWS, G Cloud
• Versions and Editions of SQL Server
• SQL Server and SSMS Installation Plan
• SQL Server Pre-requisites : S/W, H/W
• System Configuration Checker (SCC) Tool
• SQL Server 2019 : Installation [Overview]

DAY 7: JOINS, T-SQL QUERIES - Level 1

• JOINS - Table Comparisons Queries
• INNER JOIN - Examples, WHERE, ON
• OUTER JOIN - Examples, WHERE, ON
• Left Outer Joins with Example Queries
• Right Outer Joins with Example Queries
• FULL Outer Joins - Real-time Scenarios
• MERGE, LOOP, HASH Join Options

• Big Table Versus Small Table Joins
• Join Types Versus Join Options in T-SQL
• CROSS JOIN Versus CROSS APPLY
• Using Joins for DB Metadata Audits
• Joining more than 2 Tables in T-SQL
• Joining Tables with Query Conditions
• Joining Unrelated Tables, Join Options

DAY 14: STORED PROCEDURES - Level 2

• Table Valued Parameters (TVP) - Usage
• SQL Injection Attacks - Type Precautions
• READONLY Parameters - Stored Procedures
• OUTPUT Parameters - Stored Procedures
• User Defined Data Types, Real-time Use
• Dynamic Data Insertions with Stored Procs
• Table Cloning, Data Inserts @ Table Variables

• CTE : Common Table Expressions
• Real-time Scenarios with CTEs - Usage
• ROW_NUMBER() with CTE Queries
• Using CTEs for Avoiding Self Joins
• Using CTEs for Avoiding Sub Queries
• Recursive CTEs and ANCHOR Element
• Termination Checks in Recursive CTEs

Day 2: INSTALLATIONS [DETAILED]

• SQL Server 2016 Installation, Guidance
• SQL Server 2017 Installation, Guidance
• Instance Types: Default, Named Instances
• SQL Server Features and Importance
• SQL Server Database Engine For OLTP
• File Stream and Collation Properties
• SQL Server Authentication Modes

• Windows Login & SQL Server Login Types
• System Databases: Master, Model, MSDB
• TempDB and Resource Database Locations
• "sa" System Account, File Stream Property
• SQL Server Management Studio. SSMS
• Test Connection to Local Servers
• Test Connection to Remote Servers

DAY 8: JOINS, T-SQL QUERIES - Level 2

• GROUP BY Queries and Aggregations
• GROUP BY Queries with Having Clause
• Group By Queries - Query Design Rules
• ROLLUP( ) & CUBE( ) Summary Values
• GROUPING() Function for Row Status
• Replacing Nulls: ISNULL, COALESCE
• Joining Tables with Group By, Having

• Sub Queries and Alternatives to Joins
• Using Joins with Group By Queries
• Using Joins with Nested Sub Queries
• Sub Queries with Joins and Group By
• Using UNION and UNION ALL in Queries
• Nested Sub Queries with Group By, Joins
• Comparing WHERE, HAVING Conditions

DAY 15: STORED PROCEDURES - Level 3

• Views on Tables - SCHEMABINDING
• ENCRYPTION and CHECK OPTION
• Cascaded Views, Encrypted Views
• Updatable Views, Joins with Triggers
• Error Handling in T-SQL: TRY & CATCH
• Error Handling, THROW in Procedures
• Stored Procedures - WITH RESULT SETS

• Cursors - Benefits, Cursors in SProcs
• ForwardOnly, Scroll & Local Cursors
• Static, Dynamic & Global Cursors
• Keyset Cursors and @@FetchStatus
• Nesting of Stored Procedures - Dynamic
• Data Formatting and WHILE Loops
• Using Temporary Tables for Formatting

Day 3: DATABASE & SQL BASICS - Level 1

• SQL : Purpose and Real-time Usage
• DDL, DML, SELECT, DCL and TCL
• SSMS Tool : Connections and Usage
• SQL Versus T-SQL : Basic Difference
• Server Connections, Session Creations
• Creating Databases and DB Connections
• Creating Tables. Int, Char Data Types

• Single Row Inserts, Multi Row Inserts
• INSERT and INSERT INTO Statements
• SELECT Statement for Table Retrieval
• WHERE Conditions with =, OR, IN
• AND, OR, NOT, IN, NOT IN Conditions
• LIVE QUERY STATISTICS in SSMS
• Table Scan Properties in SQL Server

DAY 9: JOINS, T-SQL QUERIES - Level 3

• Cast, Convert, DateAdd, DateDiff Functions
• Date & Time Styles, Data Formatting
• Using Date and Time Formats in Queries
• String Functions: SUBSTRING,REPLICATE
• CHARINDEX, PATINDEX, LEFT, RIGHT
• LEN, STUFF, LTRIM, RTRIM, REVERSE
• DIFFERENCE, SOUNDEX, STRING_SPLIT

• WHEN MATCHED and NOT MATCHED
• Incremental Loads with MERGE Statement
• IIF(), CASE with WHEN and ELSE, END
• FETCH - OFFSET, NEXT ROWS, Order By
• Using PIVOT Function and FOR Values
• ROW_NUMBER() and RANK() Queries
• Dense Rank and Partition By Queries

DAY 16: FUNCTIONS - Level 2

• Functions : Types, Real-world Usage
• Inline Functions, Multi Line Functions
• Looping Concepts in SQL Server
• WHILE Loop Queries and UNPIVOT
• GROUPING SETS and OUTPUT Function
• EXISTS and RAISEERROR Functions
• TRY_CONVERT, TRY_PARSE Functions

• Using BULK INSERT & BULKCOLUMN
• OPENROWSET For Data Import, CAST
• OPENJSON For JSON Data Formats
• JSON Files - Data Import into SQL DB
• Json $Tag Notations, SELECT .. INTO
• XML Options in T-SQL Queries, Joins
• XML AUTO, XML RAW and XML PATH

DAY 4: DATABASE & SQL BASICS - Level 2

• Creating Databases : Files [MDF, LDF]
• Single Row Inserts, Multi Row Inserts
• SELECT. WHERE Conditions, Operators
• AND, OR, NOT, Mathematical Operators
• IN, NOT IN, BETWEEN, NOT BETWEEN
• IS NULL, LIKE, NOT LIKE. % and _
• CHAR Versus VARCHAR Data Types

• GO Statement, SQL BATCH Concept
• DISTINCT, TOP, FETCH, ORDER BY
• Basic Sub Queries with SELECT
• UPDATE and DELETE Statements
• TRUNCATE, ALTER, ADD and DROP
• Table Scans, Measuring Query Time
• CLIENT STATISTICS and Query Trails

DAY 10: Views, Functions, Procedure Basics

• Views : Types, Usage in Real-time
• System Predefined Views and Audits
• Listing Databases, Tables, Indexes
• Functions : Types, Usage in Real-time
• Scalar, Inline and Multi-Line Functions
• System Predefined Functions, Audits
• DBId, DBName, ObjectID, ObjectName
• Variables & Parameters in SQL Server

• Procedures : Types, Usage in Real-time
• User & System Predefined Procedures
• Parameters and Dynamic SQL Queries
• Sp_help, Sp_helpdb and sp_helptext
• Sp_recompile, sp_pkeys, sp_rename
• Compare Views, SPs and Functions

Day 17: Database, Index Architecture

• Database Architecture - Detailed
• Primary File, Secondary Files [mdf, ndf]
• Database Log Files (T-LOG) For Audits [ldf]
• Data Files, Log Files, LSN & VLF
• Transaction Log File [LDF] & LSN
• Filegroups : ReadWrite & Read Only
• Indexes: Architecture and Types
• Clustered and Non Clustered Indexes

• Included and ColumnStore Indexes
• FILTERED and COVERING Indexes
• UNIQUE Indexes, Online Indexes
• B Tree Structure, IAM Page [Root]
• Indexed Views / Materialized Views
• Pages, Extents, and Checkpoints

DAY 5: SQL Basics 3, Server Architecture

• SQL Server Architecture Components
• TDS Packets (N/W) in Client - Server
• Protocols, SQL Native Client (SNAC)
• Parser, Compiler, SQL Query Validations
• Query Optimizer (QO) and SQL Manager
• Storage Engine, File and DB Manager
• Transaction Manager and Lock Manager

• Buffer Manager, SQL OS and IO Buffer
• Synchronization and Thread Scheduler
• MDAC and CLR Components in SQL OS
• Temporary Tables : Real-time Use
• Local and Global Temporary Tables
• Schemas : Real-time Usage, Creation
• Schema - Table Transfer. 2P, 3P Naming

Day 11: Triggers, Transactions, DTC

• Triggers - Purpose, Real-world Usage
• FOR/AFTER Triggers - Real time Use
• INSTEAD OF Triggers - Real time Use
• INSERTED, DELETED Memory Tables
• Enable Triggers and Disable Triggers
• Database Level, Server Level Triggers
• Auditting Triggers and Real World Use

• Transactions : Types, ACID Properties
• EXPLICIT & IMPLICIT Transactions
• COMMIT and ROLLBACK Statements
• Query Blocking Scenarios @ Real-time
• Open Transctions in Real-world, Impact
• NOLOCK and READPAST Lock Hints
• Lock Hints, Joins @ T-SQL Queries

• Understanding Project Requirements
• End to End Project Work Flow
• Naming Conventions in Real-time
• Table Schemas : Creation and Use
• Implementing Normal Forms (OLTP)
• Computed Columns and Data Types
• SQL_Variant, Bit, sysname Data Types
• Email and Phone Number Validations
• Data Types Conversions, Validations

• Joining Tables for Reports
• Views with JOIN Options
• Implementing Indexed Views
• Using PIVOT Tables in Queries
• Using Functions for Queries
• Dynamic Conditions in Queries
• Parameterized Queries in T-SQL

• Event Handling , Error Handling
• Stored Procedures with Transactions
• Error Handling, Event Handling Options
• Transaction Nesting, Save Points
• Stored Procedures with Tables
• Stored Procedures with Views
• Stored Procedures with Functions
• Automating DML with Triggers
• Project Deployments, Project FAQ

DAY 6 : CONSTRAINTS, INDEXES - BASICS

• Constraints and Keys - Data Integrity
• NULL, NOT NULL Property on Tables
• UNIQUE KEY Constraints: Importance
• PRIMARY KEY Constraint: Importance
• FOREIGN KEY Constraint: Importance
• REFERENCES, CHECK and DEFAULT
• Candidate Keys and Identity Property

• Database Diagrams and ER Models
• Relationships Verification and Links
• Indexes : Basic Types and Creation
• Index Sort Options, Search Advantages
• Clustered and NonClustered Indexes
• Primary Key and Unique Key Indexes
• Need for Indexes with working with Keys

DAY 12 : ER MODELS, NORMAL FORMS

• Normal Forms for Entity Relationships
• First, Second, Third Normal Forms Usage
• Boycee-Codd Normal Form : BNCF : Usage
• 4 NF, EKNF, ETNF. Functional Dependency
• Multi-Valued, Transitive Dependencies
• Composite Keys and Composite Indexes
• 1:1, 1:M, M:1, M:M Relationship Types

• Self Referencing Keys and Self Joins
• Adding NOT NULL Property to Columns
• Adding Primary Key to Existing Tables
• Adding Foreign Key to Existing Tables
• Synonyms : Creation and Real-time Use
• Using Synonyms in Self Join Queries
• Cascading Keys. UPDATE/DELETE Types

DAY 21: QUERY TUNING 1 - QUERY AUDITS, PARTITIONS

• Audit Long Running Queries using DMVs and DMFs
• Activity Monitor Tool and Query Statistics Reports
• Logical I/O, Physical I/O and Database I/O, Wait Time
• Recent Expensive Queries & Active Expensive Queries
• Plan Handle and Execution Time - Query Usage Audits
• Factors Impacting the Query Executions, Performance
• Resumable Indexes, Usage in SQL Server 2017 & 2019
• ONLINE, RESUME, PAUSE, MAX_DURATION Options
• Query Store - Settings and Advantages. Options

• PARTITIONS Mechanism : Advantages, Performance
• Database Filegroups Usage with Partition Ranges
• Partition Functions and Partition Schemes - Usage
• Partitioning Un-partitioned Tables using Indexes
• Aligned / Indexed Partitions - Query Importance
• Partition SPLIT and Partition MERGE, NextUsed
• Partition Compression Techniques : ROW, PAGE
• Data Archival & SWITCH Partitions. Partitioned Views

Day 27: AZURE CLOUD & AZURE SQL DATABASE

• Introduction to Cloud. Need for Cloud, Advantages
• Cloud Architecture Basics - IaaS, PasS and SaaS
• Advantages of Microsoft Cloud - Azure Platform
• Azure Products and Azure Services - MarketPlace
• Comparing Azure with Google Cloud for SQL Server
• Comparing Azure with AWS Cloud for SQL Server
• Azure Sources - Types, Microsoft Market Place
• SQL Database Implementations in Azure Platform
• Logical Servers, Virtual Machines, Managed Instance

• Azure SQL Database Architecture Components
• Creating Azure SQL Server (Logical Server)
• Creating Azure SQL Databases and Pricing Tiers
• Price Tiers: Basic, Standard, Premium, PremiumRS
• DTUs Allocation for Database Size, Cost Models
• Firewall Settings for Azure SQL Server (Logical Server)
• Adding Firewall Rules - IP for Remote Access
• Password Resets and Azure SQL Server Name Format

Day 22: QUERY TUNING 2 - FULL TEXT SEARCH, TABLE TYPES

• Full Text Search (FTS) Mechanism - Architecture, Tuning
• Stop Words, Stemmer and Thesaurus For FullText Queries
• Indexer Program, Query Processor & FT Query Compilation
• Database Catalogs (FTC) and FDHost.exe. Daemon Threads
• Full Text (FT) Indexes for Query Tuning with Tokenization
• Crawler Threads and User Tables. Filter Daemon Host Exe
• CONTAINS() Queries and FREETEXT() Queries with SELECT

• In-Memory Tables : Creation and Practical Usage for Tuning
• Memory Snapshots and Database Level and Table Level
• FileStream Files and Memory Snapshot Filegroups for MOT
• MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Settings
• Manual Table Stats Updates with FullScan, NoRecompute
• Temporal Tables : Real-time Usage and History Tracking
• Statistics : Purpose, Creation Scenarios and Usage
• Index Statistics and Column Statistics for Tables, ReUse
• Comparing Triggers with Temporal Tables for Audits

DAY 28: DTU ARCHITECTURE, ELASTIC QUERIES

• DTU : Data Transaction Units : Architecture, Pools
• DTU - Memory and IO Resources for Reads & Writes
• Bounding Box Model for Optimal Performance
• Static Pools (DTU) and Elastic Pools (eDTU)
• eDTUs and Elastic Pool, per Database Settings
• EDTU Cost, eDTU max/min Limits and Performance
• Configuring Elastic Pools for Azure SQL Databases

• Elastic Pools & Tier Selection - Recommendations
• Elastic Scale for Azure SQL Database - Strategies
• Vertical Partitioning and Horizontal Partitioning
• Elastic Database Tool Libraries for Elastic Queries
• Sharding - Topology for Elastic Query Processing
• Split-Merge Service for SaaS Software Applications
• Elastic Database Features - ShardMap, ShardKey
• MultiShard Queries and Elastic Transactions
• LOOKUP, HASH and RANGE Strategies for Sharding

Day 23: QUERY TUNING 3 - INDEX MANAGEMENT, LOCKS

• Index Management Concepts for Performance Tuning
• Internal Fragmentation and External Fragmentation
• Fragmentation Audits : DMFs and Threshold Values
• Index Reorganization and Index Rebuild Options
• Database Maintenance Plans (DMP) For Index Reorg
• Compress Large Objects, Fragmentation Condition
• Index Page Count Condition, Index Usage Condition
• Fast, Sampled and Detailed Scan Types for Indexes
• Statistics Updates : Full Scan Options, DMP Jobs

• LOCKS : Mechanism, Types, Concurrency Control
• Lock Types: X, S, IS, IX, U, MD, Sch-M and Sch-S
• Lock Audits : SP_WHO2, SP_LOCK, sysprocesses
• Deadlock Simulation, Deadlock Prevention Scenarios
• Deadlock Audits and Lock Events in Profiler Tool
• Isolation Levels - ReadCommitted, Read Uncommitted
• Serializable, Snapshot, Repeatable Read Isolations
• Read Committed Snapshot Isolation Level in Real-time

DAY 29 : AZURE SQL DB MIGRATIONS, AZURE DATA STUDIO

• Data Migration Assistant (DMA) Tool
• On-premise to Azure SQL Database Migration
• Logical Server, Virtual Machine, Managed Instance
• Schema Generation and Compatibility For Migration
• Generating Data Scripts and Assessment
• Generate and Validate Schemas. Migrations
• Migration Scopes : Schema, Data, Schema & Data
• Compatibility Checks and Assessment Checks
• Resolving Database Migration Compatibility Issues

• Azure Data Studio Tool for Database Connections
• Azure Data Studio Features - Insights, Metrics
• Comparing Azure Data Studio with SQL OPS Tool
• SSMS Tool for Azure SQL Database Connections
• SQLOPS Tool for Azure SQL Database Connections
• SSDT (Visual Studio) for SQL Database Connections
• Query Editor for Azure Database Access - in Portal
• Azure Database Connections with Other Tools

Day 24: QUERY TUNING 4 - TUNING TOOLS, PERFMON

• Tuning Tools : Creating Workload Files and Trace Files
• SQL Profiler Tool - Tuning Template and TSQL / SP Events
• DTA Tool with Profiler Trace Files: Tuning Recommendations
• DTA with Query Cache (Procedure Cache) & .SQL File Inputs
• Execution Plans - Internals. Actual, LIVE Execution Plans
• Plan Types : Index Scan, Index Seek, Tables Scan, Spooling
• Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost
• NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity

• Perfmon Counters and Real-time Tracking Of Resources
• Processor, Disk, Memory, Transactions, Database Counters
• Using Perfmon for Big Query Audits. Free & Total Memory
• Longest Running Transactions & Transactions Per Second
• Database Log Space - Issues, Solutions. Log Rebuilds
• TempDB Issues - Causes, Precautions and Solutions
• Memory Issues - Causes, Precautions and Solutions
• Performance Tuning - Final Checklist and Precautions

DAY 30: Azure SQL DATABASE TUNING, AZURE SEARCH

• Azure SQL Server Level Tuning Options
• Azure SQL Database Level Tuning Options
• Automated Tuning Options and Peak-Loads
• Force Plan, Create Index and Drop Index
• Query Performance Insight, Intelligence
• Index Recommendations with CPU and IO
• IO Metrics, CPU Metrics & Query Statistics
• Data File IO, Log File IO, Custom Reports

• Identify Long Running Queries, Intensive Queries
• Query Level Recommendations and Query Costs
• Azure Search Service - Configuration, Pricing Tiers
• Azure Search for Data Import and Indexer Options
• Suggester and Analyzer Index Modes for Tuning
• Retrievable, Facetable, Filterable Indexes
• Facetable and Searchable Indexes for Tuning
• Change Tracking Options, Watermark Columns

DAY 25: EXECUTION PLAN ANALYSIS and RESOURCES

• Parameter Sniffing : Real-time Scenarios, Issues
• Execution Plan Issues with Parameter Sniffing
• DBCC SHOW_STATISTICS - Statistics Accuracy
• Histograms & Density Vectors for Query Optimizer
• RANGE_HI_KEY, EQ_ROWS and RANGE_ROWS
• DISTINCT_RANGE_ROWS and Index Overlapping
• sp_createstats and sp_autostats System Procedures
• Detection and Fix of (Overlapping) Duplicate Indexes

• Resource Governor : Real-time Usage
• Resource Pools and Workload Groups
• Query Priority and DOP, Grants, Classifier Function
• Policy Based Management (PBM) : Facets and Conditions
• Resource Governor : Real-time Usage & Conditions
• Resource Pools : Creation and Usage Options
• Workload Groups : Creation, Settings and Limits
• Query Priority : LOW, MEDIUM and HIGH
• DOP : Degree Of Parallelism and Allocation Grants

DAY 31: AZURE SQL DB & ON-PREMISE, XEL FILES

• Azure SQL Server Architecture Differences
• Network Protocols and DB Engine Differences
• File Structure and Filegroup Allocations
• Secondary Files and FileStream Differences
• Table Architecture for Partitions, FT Queries
• Query Processing Differences with TDS Packets
• Query Monitoring and Resources - Dashboards
• Unsupported Commands with T-SQL Queries

• SQL Traces : Creation and Audits. Limitations
• SQL Traces : Events, Class, Category, Filter, Conditions
• Extended Events and Traces for T-SQL Query Analysis
• Extended Events Packages, Targets, Actions, Sessions
• Extended Events Architecture and Engine Sessions
• TSQL and SP Debug Events with XEL Files for EventInfo
• Global Fields and Event Filters with XEL Files @ Traces
• XE Objects : Catalog Views, Dynamic Management Views
• XE Profiler - Default Templates for Event Profiling