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

This impeccable SQL Server (with T-SQL Queries and Tuning) course is exclusively designed for starters as well as for experienced professionals addressing SQL Server Installation, DB Design, Queries, Joins, CTE, Stored Procedures, Triggers, Remote Queries, Transactions and Tuning options with BLOB Data and JSON. This training course also includes Azure SQL Database and complete guidance to MCSA Certification Exams : 70-761 and 70-762.

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

SQL Server & T-SQL Developer Training with Azure

  PLAN A PLAN B PLAN C
Duration 3.5 Weeks 4.5 Weeks 6 Weeks
Completely Real-time Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Resume, Job Support Check-Symbol-for-Yes Check-Symbol-for-Yes Check-Symbol-for-Yes
Real-time Project (Banking) 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
MCSA Certification: 70-761 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
MCSA Certification: 70-762 Croos-symbol-for-No Croos-symbol-for-No Check-Symbol-for-Yes
Azure SQL Database DEV 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

SQL Server T-SQL Schedules - Trainer: Mr Sai Phanindra T

Timings (IST)
Free Demo
Start Date
 
1 6:30 AM to 7:45 AM
Recently Started
Register
2 9 AM to 10:15 AM
Jan 3rd
Jan 4th
Register
3 10:15 AM to 11:30 AM Dec 27th Dec 28th
Register
4 11:30 AM to 12:30 PM
Recently Started
Register
5 6:30 PM to 7:45 PM Jan 2nd Jan 3rd
Register

If the above schedules does not work for you, please opt for
SQL Server Video Training (on demand, 24x7 Access)

ALL CLASSES ARE COMPLETELY PRACTICAL, REAL-TIME.

 
 
 

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

PART 1 : Applicable for Plan A, B, C

Module I: SQL Server & Design, Queries, Joins

Module II: T-SQL Queries, Tuning & Programming

DAY 1: 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/2017 Server Installation Steps
  • SQL Server Tools Installation and Verification
  • SQL Server 2014 / 2012 Versions and Differences
  • 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)

DAY 9: 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

DAY 2: 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

DAY 10: 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

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

DAY 11: 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

DAY 4: 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

DAY 12: TRIGGERS, LINKED SERVERS, XML

  • 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)
  • Defining Linked Servers and Remote Join Queries
  • Import / Export Options and Triggers with Linked Servers
  • Recursive Triggers, XML Options, XML Queries
  • Real-time Considerations with Triggers - Precautions
  • Stored Procedures with Triggers and Advantages
  • Limitations with Triggers for DDL & DML Operations

DAY 5: 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

DAY 13: 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

DAY 6: 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

DAY 14: 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)

DAY 7: 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

DAY 15: 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

DAY 8: 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

DAY 16, 17,18 : 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, newid
  • 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 - Query Tuning

Level 2 - Query Tuning

DAY 19: 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

DAY 22: 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

DAY 20: 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
  • FETCH, OFFSET and OUTPUT Options with FTS

DAY 23: 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

DAY 21: 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

DAY 24: 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 : Azure SQL Database Dev

Azure SQL Database Basics

Azure SQL Database Dev / Programming

DAY 1: AZURE CLOUD INTRO

  • Introduction to Cloud. Need for Cloud, Advantages
  • Cloud Architecture Basics - Iaas, PasS and SaaS
  • Operational Advantages of Cloud, Cloud Providers
  • Advantages of Microsoft Cloud - Azure Platform
  • Service Models, Private & Public Clouds
  • SQL Databases in Microsoft Azure and Advantages
  • Azure SQL & Databases - Need, Importance
  • Azure Sources - Types, Microsoft Market Place
  • Azure SQL Database, Azure SQL Data Warehouse
  • Azure Analysis Services, BLOB and TABLE Storage
  • Azure Cosmos DB, Data Lake, DH Insight, Spark
  • Virtual Machines and Apps, Programs in Azure
  • Advantages of Azure SQL Databases & Tools
  • Comparing Azure with AWS and Google Cloud
  • Microsoft Azure Price Tiers & Subscription

DAY 5: AZURE SQL DATABASE MIGRATIONS

  • Database Scripting Wizard in SSMS
  • Scripting On-Premise Databases in T-SQL
  • Data Migration Assistant (DMA) Tool
  • Schema Generation and Compatibility Issues
  • Generating Data Scripts, Assessment, Schema Options
  • Prepare and Deploy Fixes. Database Snapshots
  • Resolving Database Migration Compatibility Issues
  • Partially Supported and Unsupported Functions
  • non SQL Server Database Migrations : MS Access, Oracle
  • SQL Server Migration Assistant (SSMA) Tool
  • Import from a BACPAC file using Azure portal
  • Import from a BACPAC file using SQLPackage
  • Import from a BACPAC file using PowerShell
  • Migrate Stored Procedures, In-Memory Tables

DAY 2: AZURE CLOUD CONFIGURATIONS

  • Azure Cloud Subscription, Azure Portal Options
  • Azure Resources, Marketplace and Dashboards
  • Azure SQL Database Architecture Components - in detail
  • Price Tiers: Basic, Standard, Premium, PremiumRS
  • Isolated Price Trier - Advantages, Performance
  • Creating SQL Servers in Azure and in Virtual Machines
  • Elastic Pools and Configuration Options - Advantages
  • DTU : Data Transaction Units : Architecture, Pools
  • eDTUs and Elastic Pool, per Database Settings
  • EDTU Cost, eDTU max/min Limits and Performance
  • Resource Groups and Resource Pools in Azure SQL
  • Azure SQL Databases : Technical Features, Benefits
  • Built-In Intelligence and Scalability, Tools For Usage
  • Advanced Security Compliance, ARM and ASM Topologies
  • Need for OSM Workspace - Operations Management Suite

DAY 6: INTEGRATING with AZURE SQL DATABASE

  • Azure SQL Database Tables, Views in Excel
  • Excel Pivot Tables and Chart Reports with Azure SQL DB
  • Azure & Excel ODC Connections. Pivot Reports
  • ADO.NET, JDBC and ODBC Connections. Data Mashups
  • Connection Drivers in Azure Cloud - Options
  • Azure Portal Email Configurations, Triggers
  • Azure SQL Database Query Batching - Advantages
  • Azure Cloud Shell - Concepts, Architecture
  • Azure Power Shell - Install and Configure
  • Installing and Scripting with Power Shell
  • PowerShellGet and Version Paths
  • Cloud Shell to run the Azure Power Shell
  • Linux Virtual Machines with Power Shell
  • Windows Virtual Machines with Power Shell

DAY 3: AZURE SQL DATABASE CONFIGURATION

  • Creating Azure SQL Server Instances
  • Creating Azure SQL Databases, Price Tiers
  • SQL Database – Cloud Database as a Service
  • Subscription Options and Database Sources
  • Elastic Pools & Tier Selection - Recommendations
  • Database Name Identifiers, Naming rules & restrictions
  • Server Names - Locations, Admin Users, Passwords
  • S1/S2/S3 DTU bands and Performance, Storage
  • Add-On Storage Options. Database Provisioning
  • Firewall Rules, IP Configuration Ranges
  • Azure Dashboard - Metrics, Notification Options
  • Azure SQL Database Collation, Connection Options, Tools
  • SQL Server Management Studio (SSMS) & Visual Studio
  • SQL Server Data Explorer Tool in Azure Cloud

DAY 7 : Advanced TSQL Components in Azure SQL DB

  • Formatting Functions, STRING_SPLIT
  • CONTEXT_INFO and SESSION_CONTEXT
  • Function Determinism - RAND, ABS
  • EXECUTE AS and REVERT Options with SPs
  • Self Contained Sub Queries, ALL, ANY
  • GROUPING SETS with GROUP BY - Usage
  • LEAD and LAG for ROWOFFSET, CHECKSUM
  • Identify Lock Escalation & Deadlocks
  • Identify ways to remediate deadlocks
  • Natively Compiled Stored Procedures
  • Consolidate Overlapping Indexes
  • .NET, PHP, Node.js, Java, Ruby, Python
  • Creating Azure SQL Databases in SSMS Tool
  • T-SQL Scripts for Azure SQL Database

DAY 4: DEVELOP AZURE SQL DATABASE

  • Executing T-SQL Scripts in Azure
  • Creating Tables and Defining Constraints
  • Cascades, Constraint Rules and Index Rules
  • Clustered Indexes in Azure SQL Database Tables
  • Programming Objects: Stored Procedures in Cloud
  • Automated Recompilations, Complex Stored Procedures
  • Triggers and Memory Tables Architecture in Cloud
  • CTE : Common Table Expressions and Performance
  • User Defined Functions and Views for Data Reporting
  • Differences between On-Premise and Cloud SQL Databases
  • Executing T-SQL Scripts in Azure SQL Database
  • Linked Servers with On-Premise and Cloud
  • SSMS "Generate Script" Options, Advanced Options

DAY 8 : Azure SQL Database Design

  • Azure SQL Database Configuration Plans
  • Using SQL Data Explorer Tool, Visual Studio
  • Azure Database Programming Concepts
  • Monitor Azure SQL Database performance
  • Monitor Azure SQL Database query plans
  • Determine best practice use cases for extended events
  • Compare the impact of Extended Events and SQLTrace
  • Firewall Settings and Row Level Security
  • Configure Azure SQL Database Performance Insight
  • Design and Implement Elastic Pools for Azure SQL Database
  • Azure SQL Database JSON Features, Data Imports
  • Excel Reporting Options from Azure SQL Database
 
MOCK CERTIFICATION - 1 (MCSA : 70-761) + Material + Dump

 

 
MOCK CERTIFICATION - 2 (MCSA : 70-762) + Material + Dump

 

*Above course curriculum applicable for registrations from Dec 6th, 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 Online 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 Preparation & 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
  • Azure SQL Database Programming, Sharding
  • In-Memory Tables and Azure Performance Insights
 
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