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

This impeccable Microsoft SQL Server Training Course is exclusively designed for aspiring Data Analysts, Business Analysts, Data Scientists, MSBI / Power BI Engineers and SQL Database Developers. This SQL Server and T-SQL Training Course is designed for both starters as well as for experienced professionals. This course includes SQL Basics, T-SQL Queries, Query Writing and Query Understanding required for Data Analysts and Business Analysts. This course also includes Programming, Tuning and Azure SQL (Cloud) concepts required for BI Developers and SQL Database Developers. One Real-time project is included in this SQL Server and T-SQL Training Course addressing Basic to Advanced SQL Queries, Detailed Database Design, Complex Joins, Common Table Expressions (CTEs), Complex Stored Procedures, Triggers, Remote Joins, Transactions and Query Tuning with XML, BLOB and JSON Data Sources with complete guidance to MCSA 70-761 and MCSA 70-762 Microsoft Certification Exams.

Practice Material, Certification, Resume & Interview Guidance are included in this SQL Server T-SQL Course.

SQL Server & T-SQL Training with Azure SQL (Development)

	PLAN A	PLAN B	PLAN C
Applicable For	SQL Dev (0-2 yrs)	SQL Dev (2+ yrs)	SQL Dev (2.5+ yrs)
Course Duration	3.5 Weeks	4.5 Weeks	6 Weeks
Completely Real-time, Practical
Mock Interviews, Case Studies
SQL Basics and Query Writing
SQL DB Design, Table Design
Normal Forms, Joins, Queries
Indexes, Stored Procedures
Excel Integration, Pivot Charts
Advanced Stored Procedures
CTE, PIVOT, XML and BLOB Data
Real-time Project [Banking]
In-depth Query Tuning
Performance Tools, Locks
Complex SPs, MCSA - 70 761
Azure SQL Database (Cloud)
Azure SQL Database Migrations
Stretch DBs, MCSA 70-762
Total Course Fee	INR 7000/- USD 100	INR 10000/- USD 150	INR 14000/- USD 200

Register Today

Trainer : Sai Phanindra

	Schedule (IST)	Start Date
1	6:30 AM - 7:30 AM	Dec 5th	Register
2	10:15 AM - 11:15 AM	Dec 18th	Register
3	7:30 PM - 8:30 PM	Nov 20th	Register

If above schedules does not work for you then opt for On-demand Video Training

SQL Server T-SQL Training Highlights

✔ In-depth Tuning	✔ HA - DR Issues
✔ Queries, Joins	✔ Dynamic SQL
✔ Query Tuning	✔ CTEs, TVPs
✔ Excel Imports	✔ In-Memory Tables
✔ Excel Pivot Charts	✔ Azure SQL Migration
✔ Complex joins	✔ Azure Elastic Queries
✔ Linked Servers	✔ Azure DB Tuning

All Session Are Completely Practical & Real Time

SQL Server T-SQL (DEV) Training Course Contents:

Curriculum

Register Today

**PART 1 Of 2: SQL Server Basics, Queries, Stored Procedures and Database Development**
Module I: SQL Queries & T-SQL Programming Applicable for Plans A, B, C
Day 1: INTRODUCTION, INSTALLATION Data, Databases and RDBMS Software Microsoft SQL Server Advantages, Use Database Engine Component and OLTP BI Components & 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 Versions and Editions of SQL Server SQL Server and SSMS Installation Plan SQL Server Pre-requisites : S/W, H/W SQL Server 2016 / 2017 Installation SQL Server 2019 Installation Instance Name and Server Features	Day 6: Group By, T-SQL Queries : Level 2 GROUP BY Queries and Aggregations DISTINCT Vs GROUP BY Comparisons GROUP BY Queries with Having Clause GROUP BY Queries with Where Clause Using WHERE and HAVING in T-SQL Rollup : Usage and T-SQL Queries UNION and UNION ALL Operator Date and Time Functions in T-SQL GetDate(), Convert(), Date Styles DateAdd() and DateDiff() Functions Year(), Month(), Day() Functions String Functions in T-SQL: Replace() SubString(), Reverse() and Charat() Left(), Right(), LTrim() and RTrim()	Day 10: STORED PROCEDURES - Level 2 Table Valued Parameters (TVP) SQL Injection Attacks - Precautions READONLY Parameters - Stored Procs OUTPUT Parameters - Stored Procedures User Data Types & Real-time Use Dynamic Data Insertions with SPs Table Cloning, 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: SSMS Tool, SQL BASICS - 1 SQL Server Management Studio Local and Remote Connections System Databases: Master and Model MSDB, TempDB, Resource Databases Creating Databases : Files [MDF, LDF] Creating Tables in User Interface Data Insertion & Storage. Limitations SQL : Purpose and Real-time Usage SQL Versus T-SQL : Basic Differences DDL, DML, SELECT, DCL and TCL Creating Tables using SQL Scripts Data Storage, Inserts - Basic Level Table Data Verifications with Select SELECT Statement for Table Retrieval	Day 7: JOINS, GROUP BY, T-SQL Queries 3 Joining 2, 3 and 4 Tables in T-SQL GROUP BY Queries in JOINS, WHERE Using Sub Queries with Joins Sub Queries; Views for Query Store Excel Pivot Tables, Pivot Charts MERGE Statement, IIF ( ) & CASE WHEN MATCHED & NOT MATCHED Normal Forms, Entity Relation Diagram First, Second, Third Normal Forms Boycee-Codd Normal Form : BNCF Functional Dependency, Candidate Keys Multi-Valued & Transitive Dependencies 4 NF and ETNF Differences, Usage 1:1, 1:M, M:1, M:M Relationships	Day 11: STORED PROCEDURES - Level 3 DML Triggers and DDL Triggers FOR and INSTEAD OF Triggers Magic Tables : Inserted, Deleted Views on Tables - SCHEMABINDING ENCRYPTION and CHECK OPTION Cascaded Views, Encrypted Views Updatable Views, Joins with Triggers 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: SQL Basics - 2 Single Row Inserts, Multi Row Inserts Rules for Data Insertion Statements SELECT Statement For Data Retrieval SELECT with WHERE Conditions AND and OR Operators Usage IN Operator and NOT IN Operator BETWEEN, NOT BETWEEN Operators LIKE and NOT LIKE Operators Using Wild Card Characters DISTINCT, TOP & OFFSET UPDATE Statement & Conditions DELETE & TRUNCATE Statements Table Structure Modifications ALTER, ADD and DROP Statements	Day 8: Transactions, Database Objects Transactions : Types, ACID Properties Transaction Types and AutoCommit EXPLICIT & IMPLICIT Transactions COMMIT and ROLLBACK Statements Open Transaction Scenarios & Cause Query Blocking Scenarios @ Real-time NOLOCK and READPAST Lock Hints Views : Types, Usage in Real-time Creating, Executing Views in Database Functions : Types, Usage in Real-time Using Parameters in Functions (UDF) Create, Execute Functions in Database Using Parameters in Stored Procedures Compare Views, SPs and Functions	Day 12: FUNCTIONS, CONSTRAINTS - 2 Functions : Types, Real-world Usage Scalar Value Returning Functions Inline Table Value Functions Multi-Line Table Value Functions WHILE Loops and Iteractions in T-SQL Table Variables Usage in T-SQL PIVOT and UNPIVOT Operations Self Referencing Keys and Self Joins Cascades : ON UPDATE, ON DELETE Composite Keys and Real-time Use Computed Columns and Realtime Use XML AUTO, XML RAW and XML PATH Temporary Tables : Real-time Use Local & Global Temporary Tables
Day 4 : CONSTRAINTS, INDEXES BASICS Constraints and Keys - Data Integrity NULL, NOT NULL Property on Tables UNIQUE KEY, PRIMARY KEY Constraint FOREIGN KEY Constraint, REFERENCES CHECK Constraint and DEFAULT Identity Property : Seed & Increment Database Diagrams (E R), Relations Composite Keys Creation from GUI Candidate Keys and Foreign Keys Adding Keys to Existing Tables Indexes : Basics and Purpose Clustered and NonClustered Indexes Creating Simple Indexes on Tables Primary Key and Unique Key Indexes	Real-time Case Study Writing Queries with Joins Writing Sub Queries with Joins Queries with Date/Time Formatting Queries with String Formatting Rollup and Cube with Aggregates Writing Views For Query Store Accessing / Testing Views Excel Integration with SQL DB SQL Database Access in Excel Generate Excel Pivot Tables Excel Pivot Charts & Labels Data Formatting with Excel ODC Connections with T-SQL SQL Server Architecture	Day 13: Server, DB Architecture Server Architecture and Protocols Database Engine and Query Processor Parser, Optimizer, SQL & DB Manager Storage Engine Components, SQL OS Transaction Services, Buffer Manager Lock Manager, IO Manager, MDAC CLR, WAL, Lazy Writer, Checkpoint Database Architecture - Data Files Database Architecture - Log Files Primary (mdf), Secondary Files (ndf) Filegroup Creation, Table Association Pages & Extents. Uniform, Mixed Extents Transaction Log File [LDF], LSN, VLF Adding Files. Initial & Max File Size
Day 5: JOINS, T-SQL Queries : Level 1 JOINS - Table Comparisons Queries Joins Syntax, LEFT / RIGHT Tables INNER JOINS For Matching Data OUTER JOINS For (non) Match Data OUTER JOIN - Examples, WHERE, ON Left Outer Joins with Example Queries Right Outer Joins with Example Queries FULL Outer Joins - Realtime Scenarios Join Queries with "ON" Conditions Joins with Unrelated Tables Null Comparisons and IS NULL CROSS JOIN and CROSS APPLY CROSS JOIN Versus CROSS APPLY Understand When to Use Which Join	Day 9: T-SQL Queries - Level 4 Joining 4 and More Tables in T-SQL Using Joins with Nested Sub Queries IIF ( ) and CASE Statement in T-SQL Joins in Group By, Rollup, Cube GROUPING() and Cube, Rollup Replacing Nulls: Isnull, Coalesce Sequence Number Generation in T-SQL ROW_NUMBER() and RANK() Queries DENSE_RANK, Sequence Identification PARTITION BY and RowNumber ( ) Self Joins with Row Number ( ) Sub Queries and Self Joins in T-SQL DML Operations with Sub Queries	DAY 14: INDEX ARCHITECTURE Indexes: Architecture and Index Types B Tree Structure, IAM Page [Root] Clustered Indexes: Architecture NonClustered Indexes: Architecture Included Indexes & Index selectivity COLUMNSTORE & ONLINE Indexes FILTERED Indexes, UNIQUE Indexes COVERING Indexes, Index Seek Plans Composite Indexes, Composite Keys Indexed Views / Materialized Views Execution Plan Types: Table Scan Index Scan and Index Seek Plans Fill Factor, Pad Index & Sort Options Sort_In_Tempdb ON / OFF Options
Days 15 - 20: REALTIME PROJECT : BANKING [Includes 2500 Lines of Code , COMPLETELY SOLVED] Phase 1: DATABASE DESIGN : 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; Data Types Conversions, Validations Phase 2: QUERY DESIGN : Joining Tables for Reports; Views with JOIN Options; Implementing Indexed Views; Using PIVOT Tables in Queries; PIVOT versus UNPIVOT Queries; Parameterized Queries in T-SQL; Phase 3: PROGRAMMING : Event Handling , Error Handling; Stored Procedures with Transactions; Error Handling, Event Handling; Transaction Nesting, Save Points; Stored Procedures with Tables; Stored Procs with Views, Functions; Automating DML with Triggers;

**PART 2 Of 2: Performance Tuning and Azure SQL Database (Cloud)**
Module II: Performance Tuning & MCSA - 70 761 Applicable for Plans B, C	Module III: Azure SQL Database Development & MCSA - 70 762 Applicable for Plan C
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 Server Dashboards and Memory Consumption Reports Factors Impacting the Query Executions, Performance Query Store - Settings and Advantages. Options PARTITIONS Mechanism : Advantages, Performance Partition Functions and Partition Schemes - Usage Partitioning Un-partitioned Tables using GUI Partition Compression Techniques : ROW, PAGE Auditing / Verifying Partitioned Structures	Day 26: AZURE CLOUD INTRO, CONFIGURATION 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, AWS Cloud SQL Database Implementations in Azure Platform Logical Servers, Virtual Machines, Managed Instance Creating Azure Account and Subscription Types Installing SSMS Tool, Azure Component Verification Installing Azure Data Studio (ADS) Tool Linking ADS Tool with Microsoft Azure Cloud Differences between SSMS Tool and ADS Tool Understanding Azure Market Place & Storage
Day 22: QUERY TUNING 2 - INDEX MANAGEMENT Statistics : Purpose, Auto Creation and Updates Index Management : Internal, External Fragmentation Fragmentation Audits : DMFs and Threshold Values Index Reorganization and Index Rebuild Options Database Maintenance Plans (DMP) For Index Reorg Page Count, Last Used. Fast, Sampled / Detailed Scan Statistics Management : Column Level, Index Level Index Management - DOP : Degree Of Parallelism Index Rebuilding Process and Fragmentation Audits Index Page Count and Index Condition Checks Resumable Indexes: ONLINE and RESUME Options PAUSE and RESUME Options in Index Rebuilds Fast, Detailed Scans and Stats NoRecompute Statistics : Index and Column Statistics	DAY 27 : AZURE SQL SERVER, DATABASE CONFIGURATION Azure SQL Database Architecture Components Creating Azure SQL Server (Logical Server) Firewall Settings for Azure SQL Server (Logical Server) Firewall Settings for Azure SQL Database with T-SQL Adding Firewall Rules - IP for Remote Access Server Properties and Status Check. Server Name Format Password Resets and Azure SQL Server Name Format Creating Azure SQL Databases and Pricing Tiers Basic, Standard and Premium Plans For SQL DB vCore Based Purchasing Options and Data Size General Purpose and Business Critical Plans Computer Tier : Provisioned and Serverless Compute Generation : Gen 4 and Gen 5 DTUs Allocation for Database Size, Cost Models
Day 23: QUERY TUNING 3 - TUNING TOOLS 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 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 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 Parameter Sniffing : Real-time Scenarios, Issues Execution Plan Issues with Spooling Mechanism	DAY 28 : DTU ARCHITECTURE, DATA MIGRATIONS 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 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 Migration Scopes : Schema, Data, Schema & Data Compatibility Checks and Assessment Checks
DAY 24: QUERY TUNING 4 - LOCKS & ISOLATIONS LOCKS : Mechanism, Types, Concurrency Control Lock Types: Shared Lock (S), Intent Shared (IS) Exclusive Lock (X), Intent Exclusive (IX) Update Lock (U) and Metadata Lock (MD) Schema Stability Lock and Schema Modify Lock Query Blocking Scenarios and Lock Monitors Lock Audits : SP_WHO2, SP_LOCK, sysprocesses Isolation Levels - ReadCommitted, Read Uncommitted Serializable, Snapshot, Repeatable Read Isolations Read Committed Snapshot Isolation Level in Real-time Deadlock Simulation, Deadlock Prevention Scenarios Deadlock Audits and Lock Events in Profiler Tool Preventing Query Blocking and Deadlocks Avoidance Choosing the Correct Isolation Level in T-SQL	DAY 29: AZURE STORAGE, DATABASE MIGRATIONS Azure Storage : Purpose, Azure BLOB Data Azure Storage Account Types and Creation LRS, GRS and RA-GRS Azure Storage Accounts Azure Resource Manager (ARM) Storage Instances Classic Deployment Model Instances in Azure SQL Storage Management (SMB), Azure Storage BLOB Data Storage: LRS, GRS, and "RA - GRS" Database Migrations To Azure SQL Database Migrations From Azure SQL Database Exports From SSMS, Azure Portal Database Imports From SSMS, Azure Portal Database Migrations from On-Premise to Azure Database Exports and Limitations in SSMS Transactional Consistency with DB Exports
Day 25: QUERY TUNING 5 - FTS, MEMORY TABLES Full Text Search (FTS) Mechanism - Architecture, Tuning Stop Words, Stemmer and Thesaurus For FullText Indexes Indexer Program, Query Processor & FT Query Compilation Database Catalogs (FTC) and FDHost.exe. Daemon Threads Full Text (FT) Indexes : Query Tuning. Filter Daemon Host CONTAINS() Queries and FREETEXT() Queries with SELECT Resumable Indexes, Usage in SQL Server 2017 & 2019 ONLINE, RESUME, PAUSE, MAX_DURATION Options In-Memory Tables : Creation and Practical Usage for Tuning Memory Snapshots at Database Level and Table Level FileStream Files and Memory Snapshot Filegroups for MOT MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT Temporal Tables : for DML Audits, System Versioning Using Temporal Tables for Data Audits, Timestamps	DAY 30 : AZURE SQL DATABASE TUNING 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, Recommendations IO Metrics, CPU Metrics & Query Statistics Data File IO, Log File IO, Custom Reports 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
Case Study on Performance Tuning: This Real-time Case Study includes: Query Tuning : Basic to Advanced Level Parameter Sniffing and OPTIMIZE Options Implementing Partitions, Data Archives Implementing Compressions for Read Only Data Statistics Creation and Updates Histograms and Event Handling Options SQL Traces and XEL Profiler Charts Index Management Techniques Statics Management Techniques Using SQL Profiler Tool Using DTA : DB Engine Tuning Advisor Using Perfmon Tool and AM Tool	DAY 31 : XEL GRAPHS, STRETCH DATABASES SQL Traces : Creation and Audits. Limitations SQL Traces : Event Class, Category, Filter, Conditions Extended Events Package, Target, Action, Session TSQL and SP Debug Events with Global Fields XE Objects : Catalog & Dynamic Management Views XE Profiler - Templates for Event Profiling Comparing XE Profiler with SQL Profiler Tool Stretch Databases in Azure SQL Databases Stretch Databases - Tuning Benefits, Cautions Table Level Migrations with Azure SQL Databases Compute Performance Levels and DSU Pricing Data Storage and Azure Database Snapshots Geo Backup Of Tables and Hybrid Cloud Settings Remote Data Archive Configuration in Azure Database Master Key [DMK], Cold Data Migration Real-time Advantages of Stretch Databases
MCSA - 70 761 Exam : Pattern, Examples & Guidance	MCSA - 70 762 Exam : Pattern, Examples & Guidance