SQL Server & T-SQL 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, Data Engineers and SQL Database Developers. This Database Course is applicable for both starters as well as for experienced professionals. Includes required for Data Analysts and Business Analysts.

This course includes SQL Basics, T-SQL Queries, Query Writing, Remote Queries and Normal Forms needs for everyone. This course includes Basic to Advanced Stored Procedures, User Defined Functions (UDFs), Detailed Database Design, Complex Joins, Common Table Expressions (CTEs), Triggers and Transactions for SQL Developers. Also includes Query Tuning Operations with Tuning Tools, Execution Plans, Partitions, Indexes, Statistics, Locks & Isolation Levels, Azure SQL Database Development, Deployments, Cloud Migrations, Azure SQL DB Tuning, Working with XML, BLOB and JSON.

With Concept wise mock tests and Realtime Case Studies we ASSURE you 100% Hands-On Training with detailed explaination of one Realtime Project in a Banking Domain that contains 2000+ Lines of Code involving various phases of Design, Coding, Testing and Deployment which will be surely helpful for your Job Work and Interviews.

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

SQL Server & TSQL Training with Azure SQL

	PLAN A	PLAN B	PLAN C
Applicable For	1. SQL Server TSQL	1. SQL Server TSQL 2. Query Tuning	1. SQL Server TSQL 2. Query Tuning 3. Azure SQL Dev
Course Duration	4 Weeks	5 Weeks	7 Weeks
Course Content	Ch 1 to 20	Ch 21 to 25	Ch 1 to 30
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]	✔	✔	✔
Query Audits, Query Store	❌	✔	✔
Indexes & QUery Tuning	❌	✔	✔
Performance Tools, Locks	❌	✔	✔
Execution Plans, Query Analysis	❌	✔	✔
Azure SQL Database (Cloud)	❌	❌	✔
Azure SQL Database Deployments	❌	❌	✔
Azure SQL Database Migrations	❌	❌	✔
Azure SQL Database Tuning	❌	❌	✔
Total Course Fee ( Payable in Installments)*	INR 8000 USD 100	INR 13000 USD 165*	INR 18000 USD 225*

Trainer: Mr. Sai Phanindra (18+ Yrs of Experience)

Trainer: Mr Sai Phanindra (18+ Yrs Exp)

S No	Time (IST, Mon - Fri)	Start Date
SQL Server & TSQL Schedules
1	6 AM - 7 AM	Apr 2nd	Register
2	8 AM - 9 AM	Apr 24th	Register
3	9 AM - 10 AM	Mar 19th	Register
4	6 PM - 7 PM	Apr 15th	Register
5	8 PM - 9 PM	Apr 9th	Register
6	9 PM - 10 PM	Mar 12th	Register

SQL Server TSQL Training Highlights

✔ Queries, Joins	✔ Dynamic SQL
✔ Query Tuning	✔ CTEs, TVPs
✔ Excel Imports	✔ In-Memory Tables
✔ Excel Pivots	✔ Azure SQL Concepts
✔ Complex joins	✔ Azure DB Migrations
✔ Complex SPs	✔ Azure DB Tuning
✔ Tuning Tools	✔ Azure DB Deployments

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

Curriculum

Register Today

Module I: Intro, SQL Server Basics Installation, Architecture, DB Basics	Module II: SQL Server TSQL Queries Joins, Group By, Window, Basic SPs, UDF..	Module III: SQL Server TSQL Programming Complex Procedures, CTEs, Triggers...
Ch 1: SQL SERVER INTRODUCTION Data, Databases and RDBMS Software Database Types : OLTP, DWH, OLAP Microsoft SQL Server Advantages, Use Versions and Editions of SQL Server SQL : Purpose, Real-time Usage Options SQL Versus Microsoft T-SQL [MSSQL] Microsoft SQL Server - Career Options SQL Server Components and Usage Database Engine Component and OLTP BI Components, Data Science Components ETL, MSBI and Power BI Components Course Plan, Concepts, Resume, Project 24 x 7 Online Lab for Remote DB Access Software Installation Pre-Requisites	Ch 7: JOINS, T-SQL Queries : Level 1 JOINS - Table Comparisons Queries INNER JOINS For Matching Data OUTER JOINS For (non) Match Data Left Outer Joins with Example Queries Right Outer Joins with Example Queries FULL Outer Joins - Realtime Scenarios Join Queries with "ON" Conditions Join Unrelated Tables in SQL Server NULL, IS NULL Operators in Joins CROSS JOIN and CROSS APPLY CROSS JOIN Versus CROSS APPLY One-way & Two Way Data Comparisons Important Join Queries in T-SQL Join Options: Merge, Loop, Hash	Ch 13: STORED PROCEDURES Level 2 Table Valued Parameters (TVP) ReadOnly Parameters, Stored Procedures Output Parameters, Stored Procedures User Data Types and Real-time Use Dynamic Data Insertions with SPs Table Cloning, Inserts @ Table Variables SQL Injection Attacks - Precautions 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
Ch 2: SQL SERVER INSTALLATIONS System Configuration Checker Tool 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 Instances : Types and Properties Default Instance, Named Instances Port Numbers, Instance Differences Service and Service Account Use Authentication Modes and Logins Windows Logins and SQL Logins FileStream and Collation Properties DBEngine and Replication Components	Ch 8: Group By, T-SQL Queries Level 2 GROUP BY Queries and Aggregations 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 Cube : Usage and T-SQL Queries UNION and UNION ALL Operator EXISTS Operator, Query Conditions 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	Ch 14: 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 Stored Procedures @ Triggers, Views 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
Ch 3: 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	Ch 9: JOINS, T-SQL QUERIES Level 3 GetDate, Year, Month, Day Functions Date & Time Styles, Data Formatting DateAdd and DateDiff Functions Cast and, Convert Functions in Queries String Functions: SubString, Relicate Len, Upper, Lower, Left and Right LTrim, RTrim, CharIndex Functions MERGE Statement - Comparing Tables WHEN MATCHED and NOT MATCHED Incremental Load with MERGE Statement IIF() Function for Value Compares CASE Statement : WHEN, ELSE, END ROW_NUMBER() and RANK() Queries Dense Rank and Partition By Queries	Ch 15: XML,BLOB,FUNCTIONS Level 2 Functions : Types, Real-world Usage Scalar Value Returning Functions Inline Table Value Functions Multi-Line Table Value Functions WHILE Loops and Iterations in T-SQL Table Variables Usage in T-SQL Data Type Conversions with Functions Composite Keys , Computed Columns Self Referencing Keys, Self Joins Adding Keys to Existing Tables XML AUTO, XML RAW and XML PATH BULK INSERT, BULKCOLUMN, JSON OPENROWSET, PIVOT and UNPIVOT JSON Files - Data Import into SQL DB
Ch 4: SQL BASICS - 2 Creating Databases & Tables in SSMS Single Row Inserts, Multi Row Inserts Rules for Data Insertion Statements SELECT Statement @ Data Retrieval SELECT with WHERE Conditions Batch Concept and Go Statement AND and OR Operators Usage IN Operator and NOT IN Operator Between, Not Between Operators LIKE and NOT LIKE Operators UPDATE Statement & Conditions DELETE & TRUNCATE Statements Logged and Non-Logged Operations ADD, ALTER and DROP Columns ALTER & DROP Table Statements	Ch 10: View, SPs, Function Basics Views : Types, Usage in Real-time System Predefined Views and Audits Listing Databases, Tables, Schemas 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_pkeys, sp_rename and sp_help Important System Objects and Metadata When to use Which Database Objects	Ch 16: Server, DB Architecture Server Architecture and Protocols Database Engine and Query Processor Parser, Optimizer, SQL & DB Manager Storage Engine Components, SQL OS File Manager and Database Files 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) Filegroups Usage, ReadOnly Filegroups Database Files : Size and Location Pages, Extents. Uniform, Mixed Extents Transaction Log File [LDF], LSN, VLF
Ch 5: SQL Basics - 3, T-SQL INTRO Database Objects : Tables and Schemas Schemas : Group Tables in Database Schemas : Security Management Object Creating Schemas & Batch Concept Using Schemas for Table Creation Data Storage in Tables with Schemas Data Retreival and Usage with Schemas Table Migrations across Schemas Import and Export Wizard in SSMS Data Imports with Excel File Data Performing Bulk Operations in SSMS Temporary Tables : Real-time Use Local and Global Temporary Tables # and ## Prefix, Scope of Usage Session Level, Connection Level Use	Ch 11: Triggers & Transactions Triggers - Purpose, Real-world Usage FOR/AFTER Triggers - Real time Use INSTEAD OF Triggers - Real time Use INSERTED, DELETED Memory Tables Using Triggers for Data Replication Enable Triggers and Disable Triggers Database Level, Server Level Triggers Auditting Triggers and Real World Use 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	Ch 17 - 20: REAL-TIME PROJECT (BANKING) Includes 2500 Lines of Code (COMPLETELY SOLVED). Phase 1: DATABASE DESIGN Understanding Project Requirements End to End Project Work Flow Naming Conventions in Real-time Primary (mdf) and Secondary (ndf) Files 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 Phase 2: QUERY DESIGN Joining Tables for Reports Views with JOIN Options Implementing Indexed Views Using PIVOT Tables in Queries Dynamic Conditions in Queries Parameterized Queries in T-SQL Phase 3: PROGRAMMING 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 Project Solution Explanation Resume Points from the Project Interview FAQs from Project
Ch 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 - working with Keys	Ch 12 : ER MODELS, NORMAL FORMS Normal Forms for Entity Relationships First Normal Form and Atomocity Second Normal Form, Candidate Keys 3rd Normal Form Multi Value Dependancy Boycee-Codd Normal Form : BNCF Fourth Normal Form Realtime Advantages 1:1, 1:M, M:1, M:M Relationship Types Joins with Group By Queries Joins with Sub Queries, Formating Office Data Connections, Excel Reports Excel Pivot Reports and Reports SQL Queries (Auto Generated) in BI Tools FETCH OFFSET, NEXT ROWS, Order By Data Refresh (Manual and Automated)
Real-time Case Study - 1 (Sales & Retail) Objective : DB Design, Table Design, Relations Involves Purchases, Products, Customers and Time Data with Various Data Types.	Real-time Case Study - 2 (Sales & Retail) Objective : Query Writing, Excel Integration Excel Pivot Tables, Pivot Charts, Data Formatting, ODC Connections & Labelling

Module I: Intro, SQL Server Basics

Installation, Architecture, DB Basics

Module II: SQL Server TSQL Queries

Joins, Group By, Window, Basic SPs, UDF..

Module III: SQL Server TSQL Programming

Complex Procedures, CTEs, Triggers...

Ch 1: SQL SERVER INTRODUCTION

Data, Databases and RDBMS Software
Database Types : OLTP, DWH, OLAP
Microsoft SQL Server Advantages, Use
Versions and Editions of SQL Server
SQL : Purpose, Real-time Usage Options
SQL Versus Microsoft T-SQL [MSSQL]
Microsoft SQL Server - Career Options

SQL Server Components and Usage
Database Engine Component and OLTP
BI Components, Data Science Components
ETL, MSBI and Power BI Components
Course Plan, Concepts, Resume, Project
24 x 7 Online Lab for Remote DB Access
Software Installation Pre-Requisites

Ch 7: JOINS, T-SQL Queries : Level 1

JOINS - Table Comparisons Queries
INNER JOINS For Matching Data
OUTER JOINS For (non) Match Data
Left Outer Joins with Example Queries
Right Outer Joins with Example Queries
FULL Outer Joins - Realtime Scenarios
Join Queries with "ON" Conditions

Join Unrelated Tables in SQL Server
NULL, IS NULL Operators in Joins
CROSS JOIN and CROSS APPLY
CROSS JOIN Versus CROSS APPLY
One-way & Two Way Data Comparisons
Important Join Queries in T-SQL
Join Options: Merge, Loop, Hash

Ch 13: STORED PROCEDURES Level 2

Table Valued Parameters (TVP)
ReadOnly Parameters, Stored Procedures
Output Parameters, Stored Procedures
User Data Types and Real-time Use
Dynamic Data Insertions with SPs
Table Cloning, Inserts @ Table Variables
SQL Injection Attacks - Precautions

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

Ch 2: SQL SERVER INSTALLATIONS

System Configuration Checker Tool
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
Instances : Types and Properties

Default Instance, Named Instances
Port Numbers, Instance Differences
Service and Service Account Use
Authentication Modes and Logins
Windows Logins and SQL Logins
FileStream and Collation Properties
DBEngine and Replication Components

Ch 8: Group By, T-SQL Queries Level 2

GROUP BY Queries and Aggregations
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
Cube : Usage and T-SQL Queries
UNION and UNION ALL Operator
EXISTS Operator, Query Conditions

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

Ch 14: 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
Stored Procedures @ Triggers, Views

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

Ch 3: 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

Ch 9: JOINS, T-SQL QUERIES Level 3

GetDate, Year, Month, Day Functions
Date & Time Styles, Data Formatting
DateAdd and DateDiff Functions
Cast and, Convert Functions in Queries
String Functions: SubString, Relicate
Len, Upper, Lower, Left and Right
LTrim, RTrim, CharIndex Functions

MERGE Statement - Comparing Tables
WHEN MATCHED and NOT MATCHED
Incremental Load with MERGE Statement
IIF() Function for Value Compares
CASE Statement : WHEN, ELSE, END
ROW_NUMBER() and RANK() Queries
Dense Rank and Partition By Queries

Ch 15: XML,BLOB,FUNCTIONS Level 2

Functions : Types, Real-world Usage
Scalar Value Returning Functions
Inline Table Value Functions
Multi-Line Table Value Functions
WHILE Loops and Iterations in T-SQL
Table Variables Usage in T-SQL
Data Type Conversions with Functions

Composite Keys , Computed Columns
Self Referencing Keys, Self Joins
Adding Keys to Existing Tables
XML AUTO, XML RAW and XML PATH
BULK INSERT, BULKCOLUMN, JSON
OPENROWSET, PIVOT and UNPIVOT
JSON Files - Data Import into SQL DB

Ch 4: SQL BASICS - 2

Creating Databases & Tables in SSMS
Single Row Inserts, Multi Row Inserts
Rules for Data Insertion Statements
SELECT Statement @ Data Retrieval
SELECT with WHERE Conditions
Batch Concept and Go Statement
AND and OR Operators Usage
IN Operator and NOT IN Operator

Between, Not Between Operators
LIKE and NOT LIKE Operators
UPDATE Statement & Conditions
DELETE & TRUNCATE Statements
Logged and Non-Logged Operations
ADD, ALTER and DROP Columns
ALTER & DROP Table Statements

Ch 10: View, SPs, Function Basics

Views : Types, Usage in Real-time
System Predefined Views and Audits
Listing Databases, Tables, Schemas
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_pkeys, sp_rename and sp_help
Important System Objects and Metadata
When to use Which Database Objects

Ch 16: Server, DB Architecture

Server Architecture and Protocols
Database Engine and Query Processor
Parser, Optimizer, SQL & DB Manager
Storage Engine Components, SQL OS
File Manager and Database Files
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)
Filegroups Usage, ReadOnly Filegroups
Database Files : Size and Location
Pages, Extents. Uniform, Mixed Extents
Transaction Log File [LDF], LSN, VLF

Ch 5: SQL Basics - 3, T-SQL INTRO

Database Objects : Tables and Schemas
Schemas : Group Tables in Database
Schemas : Security Management Object
Creating Schemas & Batch Concept
Using Schemas for Table Creation
Data Storage in Tables with Schemas
Data Retreival and Usage with Schemas
Table Migrations across Schemas

Import and Export Wizard in SSMS
Data Imports with Excel File Data
Performing Bulk Operations in SSMS
Temporary Tables : Real-time Use
Local and Global Temporary Tables
# and ## Prefix, Scope of Usage
Session Level, Connection Level Use

Ch 11: Triggers & Transactions

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

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

Ch 17 - 20: REAL-TIME PROJECT (BANKING)
Includes 2500 Lines of Code (COMPLETELY SOLVED).
Phase 1: DATABASE DESIGN

Understanding Project Requirements
End to End Project Work Flow
Naming Conventions in Real-time
Primary (mdf) and Secondary (ndf) Files
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

Phase 2: QUERY DESIGN

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

Phase 3: PROGRAMMING

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

   Project Solution Explanation
   Resume Points from the Project
   Interview FAQs from Project

Ch 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 - working with Keys

Ch 12 : ER MODELS, NORMAL FORMS

Normal Forms for Entity Relationships
First Normal Form and Atomocity
Second Normal Form, Candidate Keys
3rd Normal Form Multi Value Dependancy
Boycee-Codd Normal Form : BNCF
Fourth Normal Form Realtime Advantages
1:1, 1:M, M:1, M:M Relationship Types

Joins with Group By Queries
Joins with Sub Queries, Formating
Office Data Connections, Excel Reports
Excel Pivot Reports and Reports
SQL Queries (Auto Generated) in BI Tools
FETCH OFFSET, NEXT ROWS, Order By
Data Refresh (Manual and Automated)

Real-time Case Study - 1 (Sales & Retail)
Objective : DB Design, Table Design, Relations
Involves Purchases, Products, Customers
and Time Data with Various Data Types.

Real-time Case Study - 2 (Sales & Retail)
Objective : Query Writing, Excel Integration
Excel Pivot Tables, Pivot Charts,
Data Formatting, ODC Connections & Labelling

**PART 2 Of 2: Performance Tuning and Azure SQL Database (Cloud)**
Module IV: Performance Tuning Applicable for Plans B, C	Module V: Azure SQL Database Development Applicable for Plan C
Ch 21: QUERY TUNING 1 - AUDITS, INDEXES 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 Indexes: Architecture and Types : Clustered, Non Clustered B Tree Architecture and Advantages : IAM Page, Branches GAM, SGAM, PFS and Index Page Architecture Clustered & Non Clustered Indexes : Creation and Usage Included and ColumnStore Indexes : Creation and Usage Covering Indexes and Filtered Indexes : Creation and Usage Indexed View (Materialized View) : Performance Advantages SORT_IN_TEMPDB, ONLINE = ON. Real-time Considerations	Ch 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 SQL Database Implementations in Azure Platform Logical Servers, Virtual Machines, Managed Instance Installing SSMS and Azure Data Studio (ADS) Tools Creating Azure Account and Free Subscriptions Creating Azure SQL Server (Logical Server) Firewall Settings for Azure SQL Server (Logical Server) 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 Azure SQL Database Access from SSMS Tool
Ch 22: QUERY TUNING 2 - PARTITIONS, INDEX MANAGEMENT Query Store - Settings and Advantages. Options PARTITIONS Mechanism : Advantages, Performance Partition Functions and Partition Schemes - Usage Partitioning Un-partitioned Tables using GUI Table Data Compression Techniques : ROW, PAGE Compression of Speific Partitions, REBUILDS Partition and Compression Recommendations for OLTP Auditing and Verifying Partitions with sys.partitions 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 Management : Index and Column Stats	Ch 27 : DTU, ELASITC QUERIES, MIGRATIONS DTU : Data Transaction Unit, Resource Allocations Basic, Standard and Premium Plans For SQL DB vCore Based Purchasing : Gen 4 and Gen 5 Types General Purpose and Business Critical Plans Compute Tier : Provisioned and Serverless Bounding Box Model, Elastic Pools & Queries eDTUs and Elastic Pool, per Database Settings Azure Storage : Types, Creation & Containers LRS, GRS and RA-GRS Azure Storage Accounts Storage Containers, Storage Explorer Tool Usage Data Migration Assistant (DMA) Tool, Assessment Data / Database Migrations To Azure SQL Database Exports and Imports in Azure with bacpac Migration Scopes : Schema, Data, Schema & Data Schema Generation and Compatibility For Migration
Ch 23: QUERY TUNING 3 - TUNING TOOLS, LOCK MANAGEMENT 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 : Processor, Disk, Memory, Transactions, DB Execution Plans - Internals. Actual, LIVE Execution Plans Query Costs : IO, CPU Cost, SubTree Cost, Operator Cost NUMA Nodes, Boost SQL Priority, Thread Count, IO Affinity Execution Plan Issues with Spooling Mechanism Lock Types: Shared Lock, Intent Shared, Exclusive Lock Intent Exclusive, Update, Metadata, Schema Lock 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	Ch 28 : 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 Audits with Query IDs, Dashboards DTU Usage Reports and Elastic 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 Full Text Search : Performance Advantages
Ch 24: QUERY TUNING 4 - FULL TEXT SEARCH, MOT 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 Creating Full Text Catalogs (FTCs) and FT Indexes CONTAINS() Queries and FREETEXT() Queries with SELECT Query Performance Impact with SQL Server T-SQL Queries 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 Statistics : Purpose, Auto Creation and Updates	Ch 29 : 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 (Audits) Comparing XE Profiler with SQL Profiler Tool Comparing Azure SQL Database with On-Premise 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
Ch 25 : MCSA 70 761 Exam Pattern, Examples, Guidance	Ch 30 : MCSA - 70 762 Exam Pattern, Examples, Guidance