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. Practice Material, Certification, Resume & Interview Guidance are included in this SQL Server T-SQL Course.

SQL Server & T-SQL Developer Training

  PLAN A PLAN B PLAN C
Duration 3.5 Weeks 4.5 Weeks 5.5 Weeks
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
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

LIVE Online Training Schedules

Timings (IST) Demo Date Start Date
6:30 AM to 8 AM August 17th August 17th Register
10:00 AM to 11:30 AM August 30th August 31st Register
6:30 PM to 8 PM Sep 6th Sep 10th Register
8 PM to 9:30 PM August 17th August 18th Register

None of the above schedules work for you? opt for On-demand, Self Paced Video Training Course


Trainer : Mr. Sai phanindra T (11+ Yrs EXP). Profile
 
 

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

Module I: SQL Server & Design, Queries, Joins

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 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)

DAY 2,3: SQL BASICS - DDL, DML, SELECT

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 4: DATABASE & TABLE 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. Planning VLDBs Adding Filegroups and Files. Size, Property Modifications Routing Tables to Database File Groups, Advantages Schemas - Purpose, Creation and Usage with Tables CHAR versus VARCHAR Differences - Type, Size Allocations Database Log Files for DML - Logged, NonLogged Options Default Schema and Default Filegroup for Table Design Data Types, Length, NULLs and Naming Conventions SELECT Queries with Schema on Tables, Column Aliases

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 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 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 Looking for More Joins and Queries with Procedures? DAY 16

DAY 7, 8: VIEWS, FUNCTIONS, JOINS, QUERIES

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 Cascaded Views and WITH CHECK OPTION, Advantages Orphan Views - Scenarios and Realworld Solutions Common System Views For Metadata Access, Object IDs Functions: Types, Purpose and Usage. Return Values Scalar Value Returning Functions - Examples, Usage Inline Table Value Returning Functions - Dynamic Joins Multi-Line Table Value Functions - WHILE Loop Table Variables and Usage with Functions. Table Data Type Variables and Parameters in SQL Server. Usage Differences Dynamic Query Conditions with Functions. Return, Returns 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 CASE Statement (with/without Expressions), PIVOT Usage MERGE Statement - MATCHED and NONMATCHED Operations Using Views for Queries and Sub Queries with Functions

Module II: T-SQL Queries, Tuning & Programming

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 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 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 More examples for CTEs and Stored Procedures: DAY 14,15,21

Realtime Project Starts. BANKING / ECOMMERCE / ERP

DAY 11: TRIGGERS & TRANSACTIONS

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 Data Distribution and JOINS. Value Mapping Looking for more CTEs and Recursive CTEs? DAY 21 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

DAY 12: 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) Stored Procedures and Recompilations with Indexes

DAY 13: 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 & Degreeponents LSN Timestamps and MINILSN. Background Threads @ SQL

DAY 14-17: REAL-TIME PROJECT (BANKING)

End - to - to End Project Implementation
  • 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 1: Design Data Structures for Optimal Performance
  • Phase 2: Views for Data Inserts, Joined Queries
  • Phase 2: Common Reporting Functions, User Access
  • Phase 2: RANK, ROW_NUMBER, DENSE_RANK, PIVOT
  • Phase 2: INSERTS with PIVOT, Calculations, Sub Queries
  • Phase 2: Implement Indexes and Column Store Options
  • 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
  • Reading Log Files and Data Audits & 3rd Party Tools
  • Transaction Audits and Offline Query Logs for SQL DEVs

1. RESUME PREPERATION

2. INTERVIEW GUIDANCE, LATEST INTERVIEW QUESTIONS

3. MOCK INTERVIEW

Module I: SQL Server & Design, Queries, Joins

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 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)

DAY 2,3: 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 4: DATABASE & TABLE 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. Planning VLDBs Adding Filegroups and Files. Size, Property Modifications Routing Tables to Database File Groups, Advantages Schemas - Purpose, Creation and Usage with Tables CHAR versus VARCHAR Differences - Type, Size Allocations Database Log Files for DML - Logged, NonLogged Options Default Schema and Default Filegroup for Table Design Data Types, Length, NULLs and Naming Conventions SELECT Queries with Schema on Tables, Column Aliases

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 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 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 Looking for More Joins and Queries with Procedures? DAY 16

DAY 7, 8: VIEWS, FUNCTIONS, JOINS, QUERIES

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 Cascaded Views and WITH CHECK OPTION, Advantages Orphan Views - Scenarios and Realworld Solutions Common System Views For Metadata Access, Object IDs Functions: Types, Purpose and Usage. Return Values Scalar Value Returning Functions - Examples, Usage Inline Table Value Returning Functions - Dynamic Joins Multi-Line Table Value Functions - WHILE Loop Table Variables and Usage with Functions. Table Data Type Variables and Parameters in SQL Server. Usage Differences Dynamic Query Conditions with Functions. Return, Returns 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 CASE Statement (with/without Expressions), PIVOT Usage MERGE Statement - MATCHED and NONMATCHED Operations Using Views for Queries and Sub Queries with Functions

Module II: T-SQL Queries, Tuning & Programming

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 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 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 More examples for CTEs and Stored Procedures: DAY 14,15,21

Realtime Project Starts. BANKING / ECOMMERCE / ERP

DAY 11: TRIGGERS & TRANSACTIONS

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 Data Distribution and JOINS. Value Mapping Looking for more CTEs and Recursive CTEs? DAY 21 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

DAY 12: 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) Stored Procedures and Recompilations with Indexes

DAY 13: 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 & Degreeponents LSN Timestamps and MINILSN. Background Threads @ SQL

DAY 14-17: REAL-TIME PROJECT (BANKING)

End - to - to End Project Implementation
  • 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 1: Design Data Structures for Optimal Performance
  • Phase 2: Views for Data Inserts, Joined Queries
  • Phase 2: Common Reporting Functions, User Access
  • Phase 2: RANK, ROW_NUMBER, DENSE_RANK, PIVOT
  • Phase 2: INSERTS with PIVOT, Calculations, Sub Queries
  • Phase 2: Implement Indexes and Column Store Options
  • 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
  • Reading Log Files and Data Audits & 3rd Party Tools
  • Transaction Audits and Offline Query Logs for SQL DEVs

1. RESUME PREPERATION

2. INTERVIEW GUIDANCE, LATEST INTERVIEW QUESTIONS

3. MOCK INTERVIEW

SQL Server Performance Tuning (Query Tuning) [For Plan B & C]

 

Level 1 Tuning Options

DAY 18: QUERY AUDITS, QUERY TUNING - JOIN OPTIONS, CTES

Identifying Long Running Queries & Activity Monitor Using Important Dynamic Management Objects (DMV, DMF) Using Query Statistics and Cache Plans / Execution Plans Using CROSSAPPLY and Other Operators with Dynamic Objects Avoiding Self Joins - Real-world Scenarios Avoiding Sub Queries and Conditions - Real-world Scenarios Comparing Sub Queries & Joins - Performance Baselines 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 ANCHOR Members and RECURSIVE Members. Termination 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 @ Joins, Join Options

DAY 19: PARTITIONS and STATISTICS

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 Partitioned Views and Concolation Options Table Archival Process and Partition Split/Merge 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

DAY 20: FULL TEXT SEARCH (FTS) & FT Indexes

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, Operators Data Populations and FILESTREAM with FTS Performance Tuning with Full Text Indexes CONTAINSTABLE and FREETEXTTABLE with FTS Real-world Performance Considerations with FTS

Level 2 Tuning Options

DAY 21: INDEX INTERNALS AND DTA TOOL

    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 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, Recent Queries DTA Tool for Multi-Database Connections Understanding PDS Options with Indexes

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

DAY 23: PERFMON COUNTERS, MEMORY OPTIONS @ TUNING

PERFMON Counters and MSDTC Service Memory Pages & IO Resources : Query Performance MEMORY LEAKS & PAGE WAITS: Query Performance LATCH WAITS and Query Performance Impact CPU, Thread Management and Windows Fibres Working with Machine Code @ SQL Server 2016 Resource Governor - Resource Pools - Tuning Resource Workload Groups - Creation, Settings LOW, HIGH, MEDIUM Priority Queries - Resources Classifier Functions, Cost Based Optimization Query Priority, CPU / Memory / IO Limits Windows Fibres, Priority Boost, DOP Options Processor Settings and Counters. Thresholds Cached Plans and Memory Store for Stored Procedures Performance Tuning - Checklist Activities

Module I: SQL Server & Design, Queries, Joins

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 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)

DAY 2,3: 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 4: DATABASE & TABLE 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. Planning VLDBs Adding Filegroups and Files. Size, Property Modifications Routing Tables to Database File Groups, Advantages Schemas - Purpose, Creation and Usage with Tables CHAR versus VARCHAR Differences - Type, Size Allocations Database Log Files for DML - Logged, NonLogged Options Default Schema and Default Filegroup for Table Design Data Types, Length, NULLs and Naming Conventions SELECT Queries with Schema on Tables, Column Aliases

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 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 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 Looking for More Joins and Queries with Procedures? DAY 16

DAY 7, 8: VIEWS, FUNCTIONS, JOINS, QUERIES

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 Cascaded Views and WITH CHECK OPTION, Advantages Orphan Views - Scenarios and Realworld Solutions Common System Views For Metadata Access, Object IDs Functions: Types, Purpose and Usage. Return Values Scalar Value Returning Functions - Examples, Usage Inline Table Value Returning Functions - Dynamic Joins Multi-Line Table Value Functions - WHILE Loop Table Variables and Usage with Functions. Table Data Type Variables and Parameters in SQL Server. Usage Differences Dynamic Query Conditions with Functions. Return, Returns 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 CASE Statement (with/without Expressions), PIVOT Usage MERGE Statement - MATCHED and NONMATCHED Operations Using Views for Queries and Sub Queries with Functions

Module II: T-SQL Queries, Tuning & Programming

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 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 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 More examples for CTEs and Stored Procedures: DAY 14,15,21

Realtime Project Starts. BANKING / ECOMMERCE / ERP

DAY 11: TRIGGERS & TRANSACTIONS

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 Data Distribution and JOINS. Value Mapping Looking for more CTEs and Recursive CTEs? DAY 21 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

DAY 12: 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) Stored Procedures and Recompilations with Indexes

DAY 13: 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 & Degreeponents LSN Timestamps and MINILSN. Background Threads @ SQL

DAY 14-15: REAL-TIME PROJECT (BANKING)

End - to - to End Project Implementation
  • 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 1: Design Data Structures for Optimal Performance
  • Phase 2: Views for Data Inserts, Joined Queries
  • Phase 2: Common Reporting Functions, User Access
  • Phase 2: RANK, ROW_NUMBER, DENSE_RANK, PIVOT
  • Phase 2: INSERTS with PIVOT, Calculations, Sub Queries
  • Phase 2: Implement Indexes and Column Store Options
  • 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
  • Reading Log Files and Data Audits & 3rd Party Tools
  • Transaction Audits and Offline Query Logs for SQL DEVs

1. RESUME PREPERATION

2. INTERVIEW GUIDANCE, LATEST INTERVIEW QUESTIONS

3. MOCK INTERVIEW

SQL Server Performance Tuning (Query Tuning) [For Plan B & C]

 

Level 1 Tuning Options

DAY 16: QUERY AUDITS, QUERY TUNING - JOIN OPTIONS, CTES

Identifying Long Running Queries & Activity Monitor Using Important Dynamic Management Objects (DMV, DMF) Using Query Statistics and Cache Plans / Execution Plans Using CROSSAPPLY and Other Operators with Dynamic Objects Avoiding Self Joins - Real-world Scenarios Avoiding Sub Queries and Conditions - Real-world Scenarios Comparing Sub Queries & Joins - Performance Baselines 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 ANCHOR Members and RECURSIVE Members. Termination 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 @ Joins, Join Options

DAY 17: PARTITIONS and STATISTICS

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 Partitioned Views and Concolation Options Table Archival Process and Partition Split/Merge 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

DAY 18: FULL TEXT SEARCH (FTS) & FT Indexes

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, Operators Data Populations and FILESTREAM with FTS Performance Tuning with Full Text Indexes CONTAINSTABLE and FREETEXTTABLE with FTS Real-world Performance Considerations with FTS

Level 2 Tuning Options

DAY 19: INDEX INTERNALS AND DTA TOOL

    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 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, Recent Queries DTA Tool for Multi-Database Connections Understanding PDS Options with Indexes

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

DAY 21 : PERFMON COUNTERS, MEMORY OPTIONS @ TUNING

PERFMON Counters and MSDTC Service Memory Pages & IO Resources : Query Performance MEMORY LEAKS & PAGE WAITS: Query Performance LATCH WAITS and Query Performance Impact CPU, Thread Management and Windows Fibres Working with Machine Code @ SQL Server 2016 Resource Governor - Resource Pools - Tuning Resource Workload Groups - Creation, Settings LOW, HIGH, MEDIUM Priority Queries - Resources Classifier Functions, Cost Based Optimization Query Priority, CPU / Memory / IO Limits Windows Fibres, Priority Boost, DOP Options Processor Settings and Counters. Thresholds Cached Plans and Memory Store for Stored Procedures Performance Tuning - Checklist Activities

MCSA CERTIFICATION TRAINING - SQL SERVER DATABASE DEVELOPMENT [70-761] & QUERYING [70-762]

DAY 22: Understanding Sets; Understanding Predicate Logic; Executing Queries that Filter Data using Predicates; Executing Queries That Sort Data Using ORDER BY; Filtering Data with Predicates; Filtering Data with TOP and OFFSET-FETCH; Working with Unknown Values; Self-Contained Subqueries SET XACT_ABORT; SOUNDEX, DIFFERENCE, CASE, ISNULL, COALESCE Functions; TRY_PARSE, PATINDEX, CHARINDEX,RTRIM/LTRIM, REVERSE Functions; GROUPING SETS;

DAY 23: Storing and Querying XML Data in SQL Server; Storing XML Data and Schemas in SQL Server; Implementing XML Data Type Using the Transact-SQL; FOR XML Statement; Working with XQuery; Shredding XML; Determining when to use XML; Testing XML Data Storage in Variables Using XML Schemas; Using FOR XML Queries; Creating a Stored Procedure to Return XML; Table Value Parameters (TVP) - Purpose, Types and Syntax; User Defined Table Data Types and TVP Usage in SProcs;Natively Compiled Stored Procedures; SQL Injection Attacks & Vulnerables: Parameter Sniffing; Stored Procedure for ReadWrite Parameters - Usage; READONLY Parameters, Table Data Type (User Defined);

DAY 24: Implementing Managed Code in SQL Server; Introduction to CLR Integration in SQL Server; Implementing and Publishing CLR Assemblies; Implementing Managed Code in SQL Server; Assessing Proposed CLR Code; Creating a Scalar-Valued CLR Function; Creating a Table Valued CLR Function; Importance of CLR integration in SQL Server; Implement and publish CLR assemblies using SQL Server Data Tools (SSDT); SQL Server Concurrency;SQL Server Concurrency; Implement Snapshot Isolation; Implement Partition Level Locking;Alternatives to Functions

DAY 25: Introduction to Spatial Data; Storing and Querying Spatial Data in SQL Server; Working with SQL Server Spatial Data Types; Using Spatial Data in Applications; Geometry Data Type; Add Spatial Data to an Existing Table; Find Nearby Locations; Spatial data can be stored in SQL Server; Use basic methods of the GEOMETRY and GEOGRAPHY data types; Controlling Execution Context; JSON Files - Usage. Import Options;JSON Files - Importance, JSON File Export to Azure;

DAY 26: Certification Pattern Examples (70-761) : Querying Data with Transact-SQL - Mock Certification
Certification Pattern Examples (70-762) : Developing SQL Databases - UseCase Scenarios - Mock Certification

*Above course curriculum applicable for registrations from June 24th, 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 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