SQL Server & T-SQL (DEV) Video Training

SQL Server T-SQL (DEVELOPER) course includes Database Fundamentals, Installation, In-depth DB and Table Design, Detailed Constriants, Joins, Views, Functions including Basic to Advanced Stored Procedures, Transactions, Triggers, JSON & Query Tuning with one REAL-TIME PROJECT. Each video includes Study Material, Lab Works and Tasks & Solutions with 24x7 LIVE Server Access. Register for T-SQL Video Training

SQL Server & T-SQL Developer Course - Video Training

	PLAN A	PLAN B	PLAN C
No. Of. Video Classes	15	21	27
Real-Time Project
Resume Support
Mock Interviews
Performance Tuning Classes
Azure SQL Database
MCSA Certification Classes
Total Course Fee	INR 6000/- USD 100	INR 9000/- USD 150	INR 12000/- USD 200

T-SQL Video Training Highlights

Real-time, Practical Training Sessions

Acessible 24x7 - Mobile/PC/Tab

Study Material and Lab Work

ONE Real-time Project Included

Trainers for doubts clarifications

Service payable in TWO Installments

Trainer : Mr. Sai Phanindra T (12+ Yrs EXP). Profile

Register Today

SQL Server T-SQL (DEVELOPER) Video Training Contents:

Brouchure

Register Today

PART 1 : Applicable for Plan A, B, C
Module I: SQL Server & Design, Queries, Joins	Module II: T-SQL Queries, Tuning & Programming
VIDEO 0: SQL SERVER (2016 / 2014) INSTALLATION -- Free Demo What is Data? What is Database? File Store Limitations? Why Microsoft SQL Server? Advantages (Technical/Usage) SQL Server - Career Options, Certifications, Projects What is SQL? What is T-SQL? Differences. Why T-SQL? Versions and Editions of SQL Server - Overview Session Wise Plan, Material and Real-time Project Details LAB PLAN - 24x7 LIVE Server (Online Lab) For the Course How to install SQL Server - Step by Step Guidelines SQL Server 2016 Software - Server Installation Steps SQL Server 2016 - Tools Installation and Verification SQL Server 2014 / 2012 Software Installation Guidance H/W & S/W Requirements. Server Configuration Options Instance Types : Default and Named Instances. Instance IDs Service, Authentication and Instance Collation Properties SQL Server Tools - SQL Server Management Studio (SSMS) Client Connectivity Tests, Browsing Servers (Local/Remote)	VIDEO 8: STORED PROCEDURES - LEVEL 1 Stored Procedures - Purpose, Syntax, Properties and Types Compilation, Precompilation and Query Optimization (QO) Variables - Usage and Data Types in Stored Procedures Parameters - Usage and Data Types in Stored Procedures Stored Procedure Executions - Syntax, Alternate Options Stored Procedures for Data Validations & Missing Identity Stored Procedures for Dynamic SQL Queries. Views & SPs Stored Procedures for Data Reporting. Advantanges, Tuning Important System Procedures For Metadata Access. Usage Important Extended Procedures For Application Operations IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT statements Error Handling Techniques in T-SQL: TRY, CATCH, THROW Dynamic Parameters and Variables. Examples with Views Default Parameter Values, Data Types and NULL Values Batch Executions with Stored Procedures. Variants Unicode Data and Dynamic SQL Queries. sysname Data
VIDEO 1: SQL BASICS - DDL, DML, SELECT -- Free Demo Testing Installation, Understanding Server Connection Defining New Sessions for Writing Queries. Session IDs Basic SQL for Beginners. Introducing Databases, Tables What is SQL? Why T-SQL? Basic SQL Queries in SSMS DDL and DML Statements - Creating & Using Databases Table Creation (Basic Level) - Columns and Data Types Issues with Digital Data into Characters. Missing Values INSERT / Store Data into SQL Server Tables - Options Single Row and Multiple Row Inserts with NULL Values SELECT Queries and Basic Operators : IN, BETWEEN IS, UNION, UNION ALL, Other Basic SQL Operators UPDATE Statements with / without Conditions. SET DELETE Statements with Conditions. Logging Options TRUNCATE Statement - DELETE Comparisons, Logging SYSTEM DATABASES - Purpose and Importance. Resource CLIENT - SERVER Architecture (TDS) & Client Statistics SQL Native Client (SNAC) and OLE-DB Providers	VIDEO 9: STORED PROCEDURES - LEVEL 2 Stored Procedures for Sub Queries, Dynamic Sub Queries Stored Procedures for Recursive and Nested Queries OUTPUT Parameters in Stored Procedures. Usage Options Common Table Expressions (CTE) and In-Memory - Syntax Row Number and Rank Generation, Sub Queries, Self Joins Stored Procedures for Parameterized CTE (Sub) Queries Using CTE for Table Data Operations - DML & Retrieval CTE for DML and DDL Operations in Stored Procedures Using Recursive CTEs and Self Joins with Stored Procedures Precautions for Recursive CTEs - Performance Impact Query Tuning Operations with CTEs. Query Store Options CTE Advantages and Limitations - Precompilations Dynamic SQL Queries with Parameters and Variables Cached Plans and Memory Store for Stored Procedures RECOMPILE Options and ENCRYPTION Options - Scenarios Identity Inserts - Manual Sequence. Dynamic Inserts ANCHOR Members and RECURSIVE Members. Termination
VIDEO 2: SQL SERVER DATABASE DESIGN SQL Server Databases - Purpose and Design Options SQL Database Architecture - Logical and Physical View Database Properties - Files - Types - Storage Options Data Files : Purpose and Sizing. Detailed Architecture Filegroups : Purpose and Grouping Options. Properties Log files : Sizing, Placement & Detailed Architecture Pages, Extents (Uniform, Mixed). Data Allocation Process Write Ahead Log (WAL) and Log Sequence Number (LSN) Virtual Log File (VLF) and MINI LSN. Operation Audits Database Creation using GUI - Adding Files, Filegroups Database File and Filegroup Options. GUI Limitations Database Creation using T-SQL Scripts. SYNTAX Rules Database with Filegrowth, Autogrowth, MAXSIZE Options mdf, ndf, ldf and Custom Extensions. Dynamic Extensions Planning and Designing Very Large Databases (VLDB) Adding Filegroups and Files. Size, Property Modifications CHAR versus VARCHAR Differences - Type, Size Allocations	VIDEO 10: STORED PROCEDURES - LEVEL 3 SQL Injection Attacks & Vulnerables: Parameter Sniffing Stored Procedure for ReadWrite Parameters - Usage READONLY Parameters, Table Data Type (User Defined) Error Handling with Table Valued Parameters in SProcs Startup Stored Procedures: Configuration, Server Property Server Startup, Auto Log Options with Stored Procedures Extended Stored Procedures - Purpose, Options & Usage Using Extended Stored Procedures with User Procedures Stored Procedures for Dynamic Values, Calendar Data Cursors - Benefits, Syntax. Using SProcs with Cursors FORWARD_ONLY and SCROLL Cursors Types. Limitations STATIC and DYNAMIC Cursors Types. ABSOLUTE Fetch LOCAL and GLOBAL Cursor Types & Scope, Reusability KEYSET DRIVEN Cursor Types & Performance Options Embedding Cursors in Procedures and User Functions SPs with Cursors @ Dynamic Data Loads, Data Formatting Memory Limitations with Cursors with SP Recompilations
VIDEO 3: TABLE DESIGN & QUERIES Table Design - Creation. Columns - Data Types, Length Routing Tables to Database File Groups, Advantages Schemas - Purpose, Creation and Usage with Tables Table Design using T-SQL Scripts - Syntax, Examples Table Design using User Interface - Usage Options Data Types, Length, NULLs and Naming Conventions BATCH and TRANSACTION Concepts - Insert Examples UNION, UNION ALL Operators. Differences, Row Order CREATE, ALTER, DROP -- INSERT, UPDATE, DELETE SELECT Queries with Schema on Tables, Column Aliases T-SQL Data Types and NULL Values. Computed Columns Database Log Files for DML - Logged, NonLogged Options Comparing DELETE and TRUNCATE Statements - TLog Files T-SQL Operators: IN, BETWEEN, IS, AND, OR, EXISTS Default Schema and Default Filegroup for Table Design Basic Sub Queries - SELECT, MIN/ MAX. Column Aliases Temporary Tables : Purpose and Types. Local and Global Synonyms : Purpose. Alternate Object Reference, Queries	VIDEO 11: TRIGGERS - DML/DDL AUTOMATIONS Triggers - Purpose and Types. Scope Of Usage DML Triggers - Events, Types and Practical Usage FOR / AFTER Triggers - Syntax, Usage and Importance INSTEAD OF Triggers - Syntax, Usage and Importance INSERTED & DELETED Memory Tables with DML Triggers Memory Usage with INSERTED/DELETED Tables. Usage Triggers for Disabling DML Operations. Trigger Priority Triggers for DML Operation Audits and Data Sampling Triggers for Data Distribution to Multiple Tables / Views Database Level Triggers and DDL Operations - FOR Type Server Level Triggers and DDL Operations - FOR Type Triggers for Bulk Operations, Updatable Views (Indexed) Triggers for Data Distribution and JOINS. Value Mapping Recursive Triggers with Examples. Performance Impact Declarative Referential Integrity with Triggers Real-time Considerations with Triggers - Precautions Stored Procedures with Triggers and Advantages Limitations with Triggers for DDL & DML Operations
VIDEO 4: CONSTRAINTS and KEYS Constraints and Keys - Ensuring Table Data Integrity Normal Forms - Types, Relational Database (RDB) Design OLTP Database Model & BCNF - Relations with PK / UQ NULL, NOT NULL and Default Nullability for Columns UNIQUE KEY Constraints: Importance, Uniqueness, Nulls PRIMARY KEY Constraint: Properties, Priority, Limitations FOREIGN KEY Constraint: References, Relations & Usage FOREIGN KEY Constraints : Relating Two or more tables CASCADED Foreign Keys and Relations - UPDATE, DELETE CHECK Constraints: Properties, Conditions and Usage CHECK Constraints: Multi Column Checks & Operators Use DEFAULT Constraints: Properties, Usage and Limitations Relations with Tables across Multiple Schemas, Usage Identity Property with / without PRIMARY KEY, Usage Composite Primary Keys & Practical Use. Recommendations Self Referencing Keys & Usage. Using Unicode References Adding / Modifying Constraints, Keys and Data Types Naming Conventions For Constraints, Columns and Tables Normal Forms - Types, Purpose and Usage. With Examples BCNF: Boycee-Codd Normal Form and Practical Usage	VIDEO 12: TRANSACTIONS & ISOLATION LEVELS Introduction to Transactions - Types Need for Transactions, Transaction Scenarios ACID Properties and Transaction Types. Atomic Property EXPLICIT, IMPLICIT Transactions - Query Blocking IMPLICIT Transactions - Usage, Database Settings AUTOCOMMIT Transactions - Advantages, Usage Examples OPEN Transactions and Audits. OPENTRAN commands Nested Transactions and COMMIT / ROLLBACK Rules SavePoint Options with Explicit Transactions, Rollbacks LOCK HINTS : READPAST, NOLOCK, HOLDLOCK - Usage Isolation Levels : Types of Isolation Levels ReadCommitted & Read UnCommitted Isolation Levels Snapshot Isolation, Serializable Isolation Levels ReadCommitted Snapshot Isolation with Tempdb Usage Impact of Isolation Levels with Concurrent Database Users Choosing the Best Isolation Level in OLTP Environment TRY..CATCH..THROW & Error Handling with Transactions Stored Procedures with with Triggers and Transactions Choosing Transaction Type and Lock Hints Real-world Considerations For Transactions
VIDEO 5: JOINS, SUB QUERIES & NESTED QUERIES JOINS - Purpose and Types, Use Case Scenarios JOIN - Types, Queries and Importance of Reports CROSS JOIN in detail. Examples and Conditions @ WHERE INNER JOIN in detail. Examples with WHERE and ON Comparing INNER JOIN with CROSS JOIN for Conditions OUTER JOINS in detail. LEFT, RIGHT and FULL Joins SELF JOINS with INNER / OUTER Joins. Usage Scenarios Working with Self Joins on non key columns, advantages JOINS with more than 2 tables. Syntax, Precedence Order Query Optimization Considerations with Schema References Deciding the best Join Type, Order and Query Options JOIN Queries with Options and UNION, UNION ALL Operators Basic Sub Queries and Joins. Alternate Syntax & Queries Using ON and WHERE for Join Conditions. Working with NULLs Using SubQueries for Self Joins and Outer Joins Working with Nested Queries and Nested Sub Queries Using Sub Queries and Nested Sub Queries with Outer Joins End User Access to SQL Databases - Reporting Tools, Options A Real-world Case Study understanding Joins & Queries	VIDEO 13: INDEXES and QUERY TUNING OPTIONS Indexes: Architecture (Page Level), Purpose and Types Clustered Indexes - Architecture, Fragmentation Issues Non Clustered Indexes - Architecture, Column References SORT_IN_TEMPDB, FILLFACTOR and PAD_INDEX Options Execution Plans and Query Optimization (QO) Techniques Execution Plan - Table Scan, Index Scan and Index Seek INCLUDED INDEXES - Purpose, Index Seeks, Query Tuning COLUMNSTORE Indexes - Advantages, Usage Examples COLUMNSTORE Indexes - Limitation @ Filtered Index COLUMNSTORE Indexes and Online Indexes - Memory Options FILTERED Indexes - Sizing Advantages and Limitations ONLINE Indexes and OFFLINE Indexes - UNIQUE Indexes Materialized Views / Indexed Views - Tuning Options Working with UNIQUE Indexes on Tables, Views Query Optimizer (QO) Options for Index Pages, Data Pages Limitations of Indexes - Impact on DML and SELECT Primary Key Index, Composite Indexes and Precautions RID and Index Key Concepts. Index Page - Data Page Arch" Real-world Considerations For Indexes (Tables, Views)
VIDEO 6: VIEWS - FUNCTIONS (LEVEL 1) VIEWS - Benefits For Data Access, Table Operations Defining Views on Tables - Syntax, Options, Uses Views as Stored SELECT Statements, Data Access SCHEMABINDING and ENCRYPTION Options - Advantages Issues with Views For Data Validations - Solutions Cascaded Views and WITH CHECK OPTION, Advantages Orphan Views - Scenarios and Realworld Solutions Common System Views For Metadata Access, Object IDs Views on Multi Level Tables. Joins. Partitioned Views Data Synchronization and Metadata Refresh with Views Functions: Types, Purpose and Usage. Return Values Scalar Value Returning Functions - Examples, Usage Inline Table Value Returning Functions - Dynamic Joins Multi-Line Table Value Returning Functions - Usage Table Variables and Usage with Functions. Table Data Type Variables and Parameters in SQL Server. Usage Differences Dynamic Query Conditions with Functions. Return, Returns SCHEMABINDING and ENCRYPTION Options with Functions	VIDEO 14: SQL SERVER ARCHITECTURE Client - Server Architecture of SQL Server SQL Server Tools - Connection Options, TDS Packets Protocols : TCP / IP, Named Pipes, Shared Memory SQL Native Client (SNAC) and OLE DB Drivers / Providers ISO - OSI Model of Data Connections, Encrypted Data Query Processing and Query Optimizer (QO) Components SQL Server Architecture For Database Engine, LCM Options Architecture - Query Processor and Storage Engine Architecture - Query Parser, Optimizer, Mini LSN, MDAC Architecture - SQL Engine, SQL Manager and Query Buffers Architecture - Write Ahead Log (WAL), Lazy Writer Threads Architecture - SQLOS Threads and Task Schedulers, CLR SQL Database Architecture - RAID Levels (S/W, H/W) Log Sequence Numbers (LSN) and Time Mapping. Audits Log File Architecture - Virtual Log Files and Usage Log File Architecture - Mini LSN & Degree Of Parallelism DB Catalogs, CLR Integration and MDAC Components LSN Timestamps and MINILSN. Background Threads @ SQL
VIDEO 7: FUNCTIONS - QUERIES - VIEWS (LEVEL 2) Queries with GROUP BY, HAVING, ON & WHERE ROLLUP and CUBE - Sub Totals, Grand Totals, Aggregates ROLLUP of Table Data. Column Aggregations. ORDER BY CUBE on Table Data - Purpose & Usage. Permutations Queries with GROUPING() Option in SELECT, Using HAVING HAVING versus WHERE Conditions - Usage Differences Query Execution Order with Joins, ORDER BY and ROLLUP Important System Functions and Metadata. Object Name, IDs Date and Time Functions, Date Format, Styles and DATEDIFF SOUNDEX, DIFFERENCE, CASE, ISNULL, COALESCE Functions CAST, CONVERT, TRY_PARSE, ROW_NUMBER, RANK Functions PATINDEX, CHARINDEX,RTRIM/LTRIM, REVERSE Functions CASE Statement (with/without Expressions), PIVOT Usage MERGE Statement - MATCHED and NONMATCHED Operations Miscellaneous System Functions and Dynamic Conditions Using Views for Queries and Sub Queries with Functions Real-time Case Study on Online Medicare Project - Joins, Functions, Sub Queries	VIDEO 15: REAL-TIME PROJECT (BANKING) End - to - to End Project Implemetation Phase 1: Understanding Project Requirement - Banking Phase 1: Database Design with FileGroups, Schemas Phase 1: Table Design with FileGroups, Schemas Phase 1: Defining Constraints, Relations, Synonyms Phase 2: Views for Data Inserts, Joined Queries Phase 2: Common Reporting Functions, User Access Phase 2: Queries for PIVOT, DENSE_RANK, PARTITION BY Phase 2: INSERTS with PIVOT, Calculations, Sub Queries Phase 3: End-to-End Implementation - Data Validations Phase 3: Stored Procedures for Dynamic Data Inserts Phase 3: Updatable Views and Triggers for DML, Indexes Phase 3: DML Operations with PIVOT and Pagination Phase 3: ADVANCED, COMPLEX Stored Procedures in T-SQL Phase 3: DB Documentation Tools, Deployment Options 3rd Party Tools - Dell Litespeed for SQL Server 2014/2016 Reading Log Files and Data Audits & 3rd Party Tools Transaction Audits and Offline Query Logs for SQL DEVs

PART 2 : Applicable for Plan B, C : SQL Server Query Performance Tuning

Level 1 Tuning Options	Level 2 Tuning Options
VIDEO 16: QUERY TUNING - CTE, JOIN OPTIONS, STATS Identifying Long Running Queries & Activity Monitor Using Important Dynamic Management Objects (DMV, DMF) Avoiding Self Joins - Real-world Scenarios Avoding Sub Queries and Conditions - Real-world Scenarios Comparing Sub Queries & Joins - Performance Baselines Using CTEs for Memory Based Query Pre-Fetch Query Tuning and Resource Optimization Options STATISTICS - Purpose and Types. Query Tuning Options Column Statistics - Creation and Usage. Advantages Index Statistics - Auto Creation with Indexes, Usage Manual Update of Column Statistics - GUI & Scripting Role of Statistics in Query Tuning Process - Options STATISTICS with Indexes and Query Conditions. Updates LIVE Query Statistics (SQL Server 2016), Table Statistics HASH JOIN - Examples and Precautions. Usage MERGE JOIN - Examples and Precautions. Usage LOOP JOIN - Examples and Precautions. Usage OUTER APPLY, Hybrid and Multi - Level Joins Indexes on Join Options - MERGE and LOOP Joins. Usage Real-world Scenarios @ ERP (LIVE) Database	VIDEO 19: PERF. MONITORING, LIVE EXECUTION PLANS Memory Optimized Tables, Optimized Filegroups Memory Snapshot Settings and Real-world Usage Temporal Tables and SYSTEM_VERSIONING Temporal Tables For DML Audits, Performance Impact In-Memory Tables Creation and Index Options Working with Extended Events & Performance Impact LIVE Query Statistics - Monitoring Options, Metrics LIVE Query Statistics - Tracing, and Baselining Collecting and Analyzing Data Using Extended Events Implementing Performance Baseline Methodologies Optimize the file configuration of your databases Use DMVs and gather DB Performance Metrics Memory Tables Versus Temp Tables/Table Variables LIVE Execution Statistics, Hash Plans, Performance Natively Compiled Stored Procedures, Performance Creating System Versioned Temporal Tables Querying and Modifications to Temporal Tables Bulk Inserts, OPENROWSET with Temporal Tables Tuning Bulk Inserts - Recovery Models & Logging Real-world Scenarios @ ERP (LIVE) Database
VIDEO 17: PARTITIONS and FULL TEXT SEARCH Big Data - Performance Considerations Table Partitions and Query Tuning Options Partition Functions and Partition Schemes Partition Ranges, Values and Sort Orders Partitioning Un-partitioned Tables using Indexes Aligned / Indexed Partitioning and Performance Data Compression Types - ROW Level, PAGE Level Partition Numbers and Filtered Compression Concepts Managing Partitions and Query Tuning Options LIKE Operator - Limitations. Using Wild-cards Full Text Search (FTS) Configuration Options Full Text Search Service Activation - DB Level Filter Daemon Launcher Service - Purpose, Settings Database Catalogs (FTC) and Storage Locations Full Text (FT) Indexes for Query Tuning Full Text Columns and Primary Key Index Full Text Index For Searching Queries. Issues Full Population and Incremental Population CONTAINS() and FREETEXT() Functions Token Search, Inflectional Forms, Opertors Data Populations and FILESTREAM with FTS Performance Tuning with Full Text Indexes	VIDEO 20: DATABASE TUNING ADVISOR (DTA) TOOL, DOP DTA: Usage, Sequential / Parallel Query Tuning DTA Tool with Profiler, Trace Tables, Cache Understanding Workload Files & Tables in Profiler SQL Profiler Tuning and Tuning Templates Database Tuning Advisor (DTA) - Usage DTA Tool for Procedure Cache, Reent Queries DTA Tool for Multi-Database Connections Understanding PDS Options with Indexes Choosing Correct Option (PDS) for Tuning Resource Governor - Resource Pools - Tuning Resource Workload Groups - Creation, Settings LOW, HIGH, MEDIUM Priority Quries - Resources Classifier Functions, Cost Based Optimization Query Priority, CPU / Memory / IO Limits Windows Fibres, Priority Boost, DOP Options Processor Settings and Counters. Thresholds Recommended Thread Counts and Fibres. Settings CHANGE_TRACKING Options, Limitations BLOB and BULK Operations with DTA Tool DTA Tool - Limitations with Heaps, Transactions OPENROWSET Queries and Correlated Queries Real-world Scenarios @ ERP (LIVE) Database
VIDEO 18: INDEXED QUERIES, MISSING INDEXES Index Internals and Execution Plans Understanding Execution Plans, Statistics, Cost Index Fragmentation - Issues, Performance SAMPLED and DETAILED Query Scans. FillFactor Index Rebuilds (Online/Offline), Tuning Options Index Reorganization Process and Advantages Page, Row Compressions with Indexes - Cautions Filtered Indexes, Online Indexes, Indexes Views GAM, SGAM Pages, Metadata Header Info Filtered Indexes and Index Size Limitations Table Statistics & Query Tuning Options Handling Heaps, Clustered, and Nonclustered Indexes Fill Factor, Pad Index and Query Tuning Memory Pages & IO Resources : Query Performance MEMORY LEAKS & PAGE WAITS: Query Performance LATCH WAITS and Query Performance Impact	VIDEO 21 : PERFMON COUNTERS, DDM PERFMON Counters and PSSDIAG Tools Dynamic Data Masking (DDM) - Performance Secured Column Access - DDM Functions Impersonation Options with Data Masking Index Management Options - SQL 2016 Distributed Replay Controller Tool, SCOM Data Migration Assistant (DMA) Tool PSSDIAG Tool - Performance Monitoring SQL Browser Server - TCP IP, Trace Flags CPU, Thread Management and Windows Fibres Priority Boost Settings and Windows Kernel Working with Machine Code @ SQL Server 2016 Non-TSQL queries in SSMS - Performance Benefits!! Index Management Options - SQL Server 2016 New Tuning Options - SQL Server 2016 Performance Tuning - Checklist Activities

Level 1 Tuning Options

Level 2 Tuning Options

VIDEO 16: QUERY TUNING - CTE, JOIN OPTIONS, STATS

Identifying Long Running Queries & Activity Monitor
Using Important Dynamic Management Objects (DMV, DMF)
Avoiding Self Joins - Real-world Scenarios
Avoding Sub Queries and Conditions - Real-world Scenarios
Comparing Sub Queries & Joins - Performance Baselines
Using CTEs for Memory Based Query Pre-Fetch
Query Tuning and Resource Optimization Options
STATISTICS - Purpose and Types. Query Tuning Options
Column Statistics - Creation and Usage. Advantages
Index Statistics - Auto Creation with Indexes, Usage
Manual Update of Column Statistics - GUI & Scripting
Role of Statistics in Query Tuning Process - Options
STATISTICS with Indexes and Query Conditions. Updates
LIVE Query Statistics (SQL Server 2016), Table Statistics
HASH JOIN - Examples and Precautions. Usage
MERGE JOIN - Examples and Precautions. Usage
LOOP JOIN - Examples and Precautions. Usage
OUTER APPLY, Hybrid and Multi - Level Joins
Indexes on Join Options - MERGE and LOOP Joins. Usage
Real-world Scenarios @ ERP (LIVE) Database

VIDEO 19: PERF. MONITORING, LIVE EXECUTION PLANS

Memory Optimized Tables, Optimized Filegroups
Memory Snapshot Settings and Real-world Usage
Temporal Tables and SYSTEM_VERSIONING
Temporal Tables For DML Audits, Performance Impact
In-Memory Tables Creation and Index Options
Working with Extended Events & Performance Impact
LIVE Query Statistics - Monitoring Options, Metrics
LIVE Query Statistics - Tracing, and Baselining
Collecting and Analyzing Data Using Extended Events
Implementing Performance Baseline Methodologies
Optimize the file configuration of your databases
Use DMVs and gather DB Performance Metrics
Memory Tables Versus Temp Tables/Table Variables
LIVE Execution Statistics, Hash Plans, Performance
Natively Compiled Stored Procedures, Performance
Creating System Versioned Temporal Tables
Querying and Modifications to Temporal Tables
Bulk Inserts, OPENROWSET with Temporal Tables
Tuning Bulk Inserts - Recovery Models & Logging
Real-world Scenarios @ ERP (LIVE) Database

VIDEO 17: PARTITIONS and FULL TEXT SEARCH

Big Data - Performance Considerations
Table Partitions and Query Tuning Options
Partition Functions and Partition Schemes
Partition Ranges, Values and Sort Orders
Partitioning Un-partitioned Tables using Indexes
Aligned / Indexed Partitioning and Performance
Data Compression Types - ROW Level, PAGE Level
Partition Numbers and Filtered Compression Concepts
Managing Partitions and Query Tuning Options
LIKE Operator - Limitations. Using Wild-cards
Full Text Search (FTS) Configuration Options
Full Text Search Service Activation - DB Level
Filter Daemon Launcher Service - Purpose, Settings
Database Catalogs (FTC) and Storage Locations
Full Text (FT) Indexes for Query Tuning
Full Text Columns and Primary Key Index
Full Text Index For Searching Queries. Issues
Full Population and Incremental Population
CONTAINS() and FREETEXT() Functions
Token Search, Inflectional Forms, Opertors
Data Populations and FILESTREAM with FTS
Performance Tuning with Full Text Indexes

VIDEO 20: DATABASE TUNING ADVISOR (DTA) TOOL, DOP

DTA: Usage, Sequential / Parallel Query Tuning
DTA Tool with Profiler, Trace Tables, Cache
Understanding Workload Files & Tables in Profiler
SQL Profiler Tuning and Tuning Templates
Database Tuning Advisor (DTA) - Usage
DTA Tool for Procedure Cache, Reent Queries
DTA Tool for Multi-Database Connections
Understanding PDS Options with Indexes
Choosing Correct Option (PDS) for Tuning
Resource Governor - Resource Pools - Tuning
Resource Workload Groups - Creation, Settings
LOW, HIGH, MEDIUM Priority Quries - Resources
Classifier Functions, Cost Based Optimization
Query Priority, CPU / Memory / IO Limits
Windows Fibres, Priority Boost, DOP Options
Processor Settings and Counters. Thresholds
Recommended Thread Counts and Fibres. Settings
CHANGE_TRACKING Options, Limitations
BLOB and BULK Operations with DTA Tool
DTA Tool - Limitations with Heaps, Transactions
OPENROWSET Queries and Correlated Queries
Real-world Scenarios @ ERP (LIVE) Database

VIDEO 18: INDEXED QUERIES, MISSING INDEXES

Index Internals and Execution Plans
Understanding Execution Plans, Statistics, Cost
Index Fragmentation - Issues, Performance
SAMPLED and DETAILED Query Scans. FillFactor
Index Rebuilds (Online/Offline), Tuning Options
Index Reorganization Process and Advantages
Page, Row Compressions with Indexes - Cautions
Filtered Indexes, Online Indexes, Indexes Views
GAM, SGAM Pages, Metadata Header Info
Filtered Indexes and Index Size Limitations
Table Statistics & Query Tuning Options
Handling Heaps, Clustered, and Nonclustered Indexes
Fill Factor, Pad Index and Query Tuning
Memory Pages & IO Resources : Query Performance
MEMORY LEAKS & PAGE WAITS: Query Performance
LATCH WAITS and Query Performance Impact

VIDEO 21 : PERFMON COUNTERS, DDM

PERFMON Counters and PSSDIAG Tools
Dynamic Data Masking (DDM) - Performance
Secured Column Access - DDM Functions
Impersonation Options with Data Masking
Index Management Options - SQL 2016
Distributed Replay Controller Tool, SCOM
Data Migration Assistant (DMA) Tool
PSSDIAG Tool - Performance Monitoring
SQL Browser Server - TCP IP, Trace Flags
CPU, Thread Management and Windows Fibres
Priority Boost Settings and Windows Kernel
Working with Machine Code @ SQL Server 2016
Non-TSQL queries in SSMS - Performance Benefits!!
Index Management Options - SQL Server 2016
New Tuning Options - SQL Server 2016
Performance Tuning - Checklist Activities

PART 3 : Applicable for Plan C :- MCSA 70-761 & 70-762

SQL Server Querying [70-761]	SQL Server Development [70-762]
VIDEO 22 : Manage Data with Transact-SQL Understanding the foundations of T-SQL Understanding Logical Query Processing Set Theory and Predicate Logic Relational Model with SQL Queries SELECT Statement Options and Filtering data Filtering data with TOP and OFFSET-FETCH Composite Joins and NULLs in Join Columns CASE expressions and related functions FETCH, OFFSET and OUTPUT Options LIVE Query Statistics and Performance Correct handling of Nulls, Sub Queries Join Options : Merge, Hash, Loop Joins SET Operators - UNION, INTERSECT, EXCEPT Query Stats @ UNION ALL, UNION VIDEO 23 : Advanced Querying Components Date and Time Functions. Time Zones Time Zone Offsets and Conversions Concatenation and ISNULL Replacements CharIndex Versus PatIndex Functions LEN, DATALENGTH, String Alteration Formatting Functions, STRING_SPLIT CONTEXT_INFO and SESSION_CONTEXT Function Determinism - RAND, ABS CHECKSUM and NEWID Functions Self Contained Sub Queries, ALL, ANY GROUPING SETS with GROUP BY - Usage PIVOT and UNPIVOT Data Samples Window Aggregate Functions with CTE LEAD and LAG for ROWOFFSET MOCK CERTIFICATION (MCSA : 70-761)	VIDEO 24 : Advanced T-SQL Components Working with Schemabounded Views Preventing Orphan Views in joins Using CTEs in Views and Joins PARTITION Options with CTes in Views SPs for Dynamic Search Conditions Creating Logins and Users EXECUTE AS and REVERT Options with SPs Isolation Levels and Usage Concurrent Queries on Isolation Levels Resource and Performance Impact of Isolation Levels Optimize concurrency and locking behavior Troubleshoot locking issues Identify Lock Escalation & Deadlocks Identify ways to remediate deadlocks Natively Compiled Stored Procedures Statistics for Natively Compiled Stored Procedures Consolidate Overlapping Indexes VIDEO 25 : Manage database concurrency Analyze and Troubleshoot Query Plans Estimated and Actual Query Plans, Metadata Monitor Azure SQLDatabase query plans Performance Monitoring and Logging Tools Monitor Azure SQLDatabase performance Determine best practice use cases for extended events Compare the impact of Extended Events and SQLTrace Extended Events Packages, Targets, Actions, and Sessions Configure Azure SQLDatabase Performance Insight Design and Implement Elastic Scale for Azure SQLDatabase Azure Deployments of SQL Server Databases MOCK CERTIFICATION - 2 (MCSA : 70-762)