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 Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Resume Support Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Mock Interviews Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Performance Tuning Classes Croos-symbol-for-No Check-Symbol-for-Yes Check-Symbol-for-Yes
Azure SQL Database Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
MCSA Certification Classes Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
Total Course Fee INR 6000/-
USD 100
INR 9000/-
USD 150
INR 12000/-
USD 200

T-SQL Video Training How it works?


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 (11+ Yrs EXP). Profile
 

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

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

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)
*Above course curriculum applicable for registrations from Sep 16th, 2017.
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today
All Classes are Instructor-Led & LIVE. Completely Practical and Real-time with Study Material, Session Notes, Tasks and 24x7 LIVE Server.
 

SQL Server T-SQL Video Training - Highlights :

  • Completely Practical and Real-time
  • Suitable for Starters + Working Professionals
  • Session wise Handouts and Tasks + Solutions
  • TWO Real-time Case Studies, One Project
  • Certification Guidance to MCSA Exams
  • Interview Preperation & MOCK Interviews
 
 
  • End-End Database Design & Implementation
  • Detailed SQL Server Architecture, DB Design
  • Query Tuning, Stored Procedures, Linked Servers
  • In-Memory, New Features of SQL Server 2016
  • Multi Server Triggers, Views, CTEs & BLOB Data
  • In-Memory Tables and HASH Joins, Query Tuning
Register Today Other Popular Courses: SQL DBA Training, MSBI Training, SSIS Training, SSAS Training, SSRS Training [+] More Courses

Job-Oriented Real-time Training @ SQL School Training Institute - Trainer: Mr. Sai Phanindra T

 
 
 
24x7 LIVE Online Server (Lab) with Real-time Databases. Course includes ONE Real-time Project. Register Today