SQL Server (T-SQL) Classroom Training

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

Timings (IST) Start Date Register
1 6:30 AM to 8 AM Sep 22nd Register
2 10:00 AM to 11:30 AM Oct 10th Register
3 6:30 PM to 8 PM Sep 14th Register
4 8 PM to 9:30 PM Oct 5th Register
 

If none of the above Training schedules work for you? please option for On-demand, Self Paced 24x7 SQL Server Video Training Course

Highlights : Daily Tasks Weekly Interviews Real-time Project Resume Guidance MCSA Certification Placement Services
ALL SESSIONS ARE COMPLETELY PRACTICAL, REAL-TIME WITH MATERIAL & LAB WORK. Course Fee: 4,000/-   DUR - 4 Weeks (Mon-Fri)

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

Chapter 1: DATABASE BASICS, SQL BASICS and INSTALLATION

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)

Chapter 2: SSMS Tool, DATABASE BASICS and SQL QUERIES

Instance Configuration Steps and Connection Tests. SQL Server Management Studio (SSMS) Tool Installation and Verification. Browsing for Local / Network Servers. Default, Named Instances. Service, Collation, Authentication, FILSTREAM. System Databases: master, model, msdb, tempdb, resource. Database Creation, Table Creation, Data Insertion and Modification using SSMS GUI. Writing Basic SQL Queries: Creating Databases, Creating Tables, Inserting Data using Scripts. DDL and DML Statements. INSERT, UPDATE, DELETE. CREATE, ALTER, DROP. SET. SELECT

Chapter 3: SYSTEM DATABASES & CLIENT - SERVER ARCHITECTURE

Session IDs. Internal Execution of Queries: Client-Server Architecture & TDS Packet Sizing, Network Providers and Driver Programs. SQL Data Encryption Process & Client Statistics. SQL Engine - Query Compilation. SELECT Queries, WHERE, Operators: OR, AND, NOT, =, IN, NOT, BETWEEN, UNION & UNION ALL DELETE Versus TRUNCATE. CHAR Versus VARCHAR. Local and Global Temp Tables & Usage

Chapter 4: DETAILED DATABASE ARCHITECTURE

Database Design Architecture: Logical and Physical Database Design, Data Files - File Groups, Sizing, Pages and Extents. Log Files - Sizing, Placements and Pages, Virtual Log Files (VLFs). LSN and MINI LSN. WAL - Write Ahead Log & Checkpoints. Adding Files and Filegroups to SQL Databases. Creating Databases using T-SQL Commands & GUI Tools. Filegrowth and MaxSize Properties. Schemas and Table Creations. Routing Tables to Filegroups Advantages. ALTER & MODIFY. VLDB Scenarios Design in Real-World Projects. Columns Alias, Sub Queries with Schemas

Chapter 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

Chapter 6: NORMAL FORMS, DATABASE DIAGRAMS

Normal Forms - Relational Database (RDB) OLTP Modeling with Normal Forms. FIRST Normal Form, SECOND Normal Form - Functional Dependency (FD), THIRD Normal Form and BCNF. Multi Valued Dependency (MVD) Rules and Performance Factors. Data Redundancy and BCNF. RAID Levels: Data Stripping, Mirroring and Parity Bits. Variables: Purpose, Types and Scope: BATCH. Local & Global Temporary Variables.

Chapter 7: VIEWS - TYPES & METADATA QUERIES

VIEWS - Benefits For Data Access, Defining Views on Tables. Views as Stored SELECT Statements, Data Access, SCHEMABINDING, ENCRYPTION - Advantages, Issues with Views For Data Validations - Solutions, Cascaded Views and WITH CHECK OPTION, Orphan Views - Real world Solutions for Row Level Data Security.

Chapter 8: JOIN - TYPES &SUB QUERIES

JOINS - Purpose and Types, JOIN - Types, Queries, 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. JOINS with more than two tables. Nested Sub Queries, Group By, Grouping, Rollup, Cube.

Chapter 9: FUNCTIONS & QUERIES, NESTED QUERIES

Functions - Types, Scalar Value, Inline / Multiline Table Value Returning Functions, WHILE Loops, Variables, Parameters. Row Number, Dense Rank & OVER, ORDER BY, ROLLUP, CUBE. Important System Functions – OBJECT_ID, OBJECT_NAME, DB_ID, DB_NAME. Date, Time Functions, GETDATE(), DATEDIFF, DATEADD, CASE, ISNULL, COALESCE, CAST, CONVERT, IIF, REPLACE, Sub String, CHARINDEX, SOUNDEX

Chapter 10: STORED PROCEDURES - LEVEL 1

Stored Procedures - Purpose, Syntax, Types. Compilation and Recompilation with Query Optimization (QO), Variables - Usage and Data Types in Stored Procedures, Parameters - Data Types in Stored Procedures, Stored Procedures for Validations. Stored Procedures for Data Reporting. System Stored Procedures For Metadata. IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT Versus SELECT Statements.

Chapter 11: STORED PROCEDURES - LEVEL 2

Dynamic SQL Queries, Using NVARCHAR Data Type and Error Handling in T-SQL: TRY, CATCH, THROW. Dynamic Parameters and Variables. Default Parameter Values, Batch Executions with Stored Procedures. OUTPUT Parameters & sysname data type in Real-time. MERGE Statement with Stored Procedures. Storing BLOB Data with Stored Procedures. OPENROWSET and BULKCOLUMN with Sub Queries.

Chapter 12: TRIGGERS

Triggers - Purpose and Types. DML Triggers - Events, Types. FOR / AFTER Triggers Usage and Importance, INSTEAD OF Triggers. INSERTED and DELETED Memory Tables with DML Triggers, Triggers for Disabling Table and View DML Operations. DML Audits, Database Level & Server Level Triggers, Triggers for Bulk Operations.

Chapter 13: TRANSACTIONS & STORED PROCEDURES – LEVEL 3

Transaction Types and ACID Properties. Atomic Property, EXPLICIT, IMPLICIT Transactions. @@TRANCOUNT & Query Blocking Scenarios. IMPLICIT Transactions, AUTOCOMMIT Transactions - Advantages, Nested Transactions and COMMIT / ROLLBACK, Save Points and Explicit Transactions. Lock Hints: NOLOCK, READPAST Transaction Isolation Levels: READ UNCOMMITTED and READ COMMITTED, SERIALIZABLE, REPEATABLE READ, SNAPSHOT, READ COMMITTED SNAPSHOTS

Chapter 14: INDEXES (QUERY TUNING)

Clustered Indexes & Non-Clustered Indexes - Architecture. Column Store Indexes, Included Column Indexes, Indexed Views, Online / Filtered Indexes with TEMPDB - Query Tuning. Table Scan, Index Scan, Index Seek. Indexes @ PK, UQ Constraints. Indexed Views (Materialized Views) for Query Tuning and Real-time Considerations.

Chapter 15: SQL SERVER ARCHITECTURE

SQL Server Architecture - Protocols : TCP / IP, Named Pipes, Shared Memory, SQL Native Client (SNAC) and OLE DB Drivers / Providers, Query Processing and Query Optimizer (QO) Components, Database Engine, Storage Engine, MDAC, SQL Server Engine, SQL & Database Manager, Buffer Manager. Write Ahead Log (WAL), Lazy Writer, SQLOS & CLR Components. MDAC & XMLA Components for Client - Server Communication. CHECKPOINT, LAZY WRITER, WAL Threads. SQL CMD Tool Usage.

Chapter 16: Performance Tuning - I

Identifying Long Running Queries & Activity Monitor, Important Dynamic Management Objects (DMV, DMF). Query Statistics and Cache Plans / Execution Plans, CROSSAPPLY, Avoiding Self Joins, Avoiding Sub Queries and Conditions: Real-world Scenarios. Common Table Expressions (CTE), In-Memory. Stored Procedures for Parameterized CTE Sub Queries. Performance Baselines, Query Tuning. RECURSIVE CTEs & Performance. ANCHOR & RECURSIVE Members. Termination Checks. Join Options For Query Tuning: HASH JOIN, LOOP JOIN, MERGE JOIN.

Chapter 17: Performance Tuning - 2

Big Data – Query Tuning Considerations, Table Partitions For Performance Tuning 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. STATISTICS. Column Statistics and Index Statistics - Auto Creations, Manual Update of Column Statistics - GUI & Scripting. LIVE Query Statistics. SAMPLE and FULLSCAN.

Chapter 18: Performance Tuning - 3

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 in FTS, Performance Tuning with FT Indexes. Performance Impact.

Chapter 19: Performance Tuning - 4

Index Internals and Execution Plans, Understanding Execution Plans, Statistics, Cost Index Fragmentation – Performance, Scans. FillFactor & Pad Index Options, Index Rebuilds (Online/Offline), Tuning Options, Index Reorganization Advantages. Page, Row Compressions with Indexes – Cautions, Filtered Indexes, Online Indexes, Indexes Views, GAM, SGAM Pages, Heaps, Clustered & Nonclustered Indexes, Database Tuning Advisor (DTA) – Usage with SQL Profiler. Creating Trace Tables & Workload Files. Profiler Filters and Templates. Tuning & Stored Procedure Templates.

Chapter 20: Performance Tuning – 5

Memory Optimized Tables – Configuration and Usage. Memory Optimized Filegroups Creation & Database Settings, MEMORY_OPTIMIZED_ELEVATE_SNAPSHOT Options, Nonclustered Primary Key Columns with Optimization Settings with Indexes and Real-world considerations. Degree Of Parallelism (DOP), Processor Settings and Counters. Thresholds, Recommended Thread Counts & Fibers. Temporal Tables and SYSTEM_VERSIONING, Temporal Tables For DML Audits, Performance Impact. Creating System Versioned Temporal Tables, Bulk Inserts, OPENROWSET with Temporal Tables. PERFMON Counters. Query Tuning Checklist For SQL Developers.

APPLICABLE FOR PLAN B:

Chapter 21: REAL-TIME PROJECT IMPLEMENTATION

Phase 1: Understanding Project Requirement - Banking Database Design with FileGroups, Schemas Table Design with FileGroups, Schemas Defining Constraints, Relations, Synonyms Design Data Structures for Optimal Performance

Phase 2: Views for Data Inserts, Joined Queries Common Reporting Functions, User Access RANK, ROW_NUMBER, DENSE_RANK, PIVOT INSERTS with PIVOT, Calculations, Sub Queries Implement Indexes and Column Store Options

Phase 3: End-to-End Implementation - Data Validations Stored Procedures for Dynamic Data Inserts Updatable Views and Triggers for DML, Indexes DML Operations with PIVOT and Pagination ADVANCED, COMPLEX Stored Procedures in T-SQL DB Documentation Tools, Deployment Options

Reading Log Files and Data Audits & 3rd Party Tools Transaction Audits and Offline Query Logs for SQL DEV Import & Export Operations in SQL Server (SSIS) Bulk Operations and Bulk Loads. Database Scripting Options Creating and Using Data Sheets - Project Documentation

1. RESUME PREPERATION

2. INTERVIEW GUIDANCE, LATEST INTERVIEW QUESTIONS

3. MOCK INTERVIEW

*Above course curriculum applicable for registrations from JUNE 13TH, 2017

Chapter 1: DATABASE BASICS, SQL BASICS and INSTALLATION

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)

Chapter 2: SSMS Tool, DATABASE BASICS and SQL QUERIES

Instance Configuration Steps and Connection Tests. SQL Server Management Studio (SSMS) Tool Installation and Verification. Browsing for Local / Network Servers. Default, Named Instances. Service, Collation, Authentication, FILSTREAM. System Databases: master, model, msdb, tempdb, resource. Database Creation, Table Creation, Data Insertion and Modification using SSMS GUI. Writing Basic SQL Queries: Creating Databases, Creating Tables, Inserting Data using Scripts. DDL and DML Statements. INSERT, UPDATE, DELETE. CREATE, ALTER, DROP. SET. SELECT

Chapter 3: SYSTEM DATABASES & CLIENT - SERVER ARCHITECTURE

Session IDs. Internal Execution of Queries: Client-Server Architecture & TDS Packet Sizing, Network Providers and Driver Programs. SQL Data Encryption Process & Client Statistics. SQL Engine - Query Compilation. SELECT Queries, WHERE, Operators: OR, AND, NOT, =, IN, NOT, BETWEEN, UNION & UNION ALL DELETE Versus TRUNCATE. CHAR Versus VARCHAR. Local and Global Temp Tables & Usage

Chapter 4: DETAILED DATABASE ARCHITECTURE

Database Design Architecture: Logical and Physical Database Design, Data Files - File Groups, Sizing, Pages and Extents. Log Files - Sizing, Placements and Pages, Virtual Log Files (VLFs). LSN and MINI LSN. WAL - Write Ahead Log & Checkpoints. Adding Files and Filegroups to SQL Databases. Creating Databases using T-SQL Commands & GUI Tools. Filegrowth and MaxSize Properties. Schemas and Table Creations. Routing Tables to Filegroups Advantages. ALTER & MODIFY. VLDB Scenarios Design in Real-World Projects. Columns Alias, Sub Queries with Schemas

Chapter 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

Chapter 6: NORMAL FORMS, DATABASE DIAGRAMS

Normal Forms - Relational Database (RDB) OLTP Modeling with Normal Forms. FIRST Normal Form, SECOND Normal Form - Functional Dependency (FD), THIRD Normal Form and BCNF. Multi Valued Dependency (MVD) Rules and Performance Factors. Data Redundancy and BCNF. RAID Levels: Data Stripping, Mirroring and Parity Bits. Variables: Purpose, Types and Scope: BATCH. Local & Global Temporary Variables.

Chapter 7: VIEWS - TYPES & METADATA QUERIES

VIEWS - Benefits For Data Access, Defining Views on Tables. Views as Stored SELECT Statements, Data Access, SCHEMABINDING, ENCRYPTION - Advantages, Issues with Views For Data Validations - Solutions, Cascaded Views and WITH CHECK OPTION, Orphan Views - Real world Solutions for Row Level Data Security.

Chapter 8: JOIN - TYPES &SUB QUERIES

JOINS - Purpose and Types, JOIN - Types, Queries, 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. JOINS with more than two tables. Nested Sub Queries, Group By, Grouping, Rollup, Cube.

Chapter 9: FUNCTIONS & QUERIES, NESTED QUERIES

Functions - Types, Scalar Value, Inline / Multiline Table Value Returning Functions, WHILE Loops, Variables, Parameters. Row Number, Dense Rank & OVER, ORDER BY, ROLLUP, CUBE. Important System Functions – OBJECT_ID, OBJECT_NAME, DB_ID, DB_NAME. Date, Time Functions, GETDATE(), DATEDIFF, DATEADD, CASE, ISNULL, COALESCE, CAST, CONVERT, IIF, REPLACE, Sub String, CHARINDEX, SOUNDEX

Chapter 10: STORED PROCEDURES - LEVEL 1

Stored Procedures - Purpose, Syntax, Types. Compilation and Recompilation with Query Optimization (QO), Variables - Usage and Data Types in Stored Procedures, Parameters - Data Types in Stored Procedures, Stored Procedures for Validations. Stored Procedures for Data Reporting. System Stored Procedures For Metadata. IF.. ELSE, IF .. ELSE IF, IIF Conditions. PRINT Versus SELECT Statements.

Chapter 11: STORED PROCEDURES - LEVEL 2

Dynamic SQL Queries, Using NVARCHAR Data Type and Error Handling in T-SQL: TRY, CATCH, THROW. Dynamic Parameters and Variables. Default Parameter Values, Batch Executions with Stored Procedures. OUTPUT Parameters & sysname data type in Real-time. MERGE Statement with Stored Procedures. Storing BLOB Data with Stored Procedures. OPENROWSET and BULKCOLUMN with Sub Queries.

Chapter 12: TRIGGERS

Triggers - Purpose and Types. DML Triggers - Events, Types. FOR / AFTER Triggers Usage and Importance, INSTEAD OF Triggers. INSERTED and DELETED Memory Tables with DML Triggers, Triggers for Disabling Table and View DML Operations. DML Audits, Database Level & Server Level Triggers, Triggers for Bulk Operations.

Chapter 13: TRANSACTIONS & STORED PROCEDURES – LEVEL 3

Transaction Types and ACID Properties. Atomic Property, EXPLICIT, IMPLICIT Transactions. @@TRANCOUNT & Query Blocking Scenarios. IMPLICIT Transactions, AUTOCOMMIT Transactions - Advantages, Nested Transactions and COMMIT / ROLLBACK, Save Points and Explicit Transactions. Lock Hints: NOLOCK, READPAST Transaction Isolation Levels: READ UNCOMMITTED and READ COMMITTED, SERIALIZABLE, REPEATABLE READ, SNAPSHOT, READ COMMITTED SNAPSHOTS

Chapter 14: INDEXES (QUERY TUNING)

Clustered Indexes & Non-Clustered Indexes - Architecture. Column Store Indexes, Included Column Indexes, Indexed Views, Online / Filtered Indexes with TEMPDB - Query Tuning. Table Scan, Index Scan, Index Seek. Indexes @ PK, UQ Constraints. Indexed Views (Materialized Views) for Query Tuning and Real-time Considerations.

Chapter 15: SQL SERVER ARCHITECTURE

SQL Server Architecture - Protocols : TCP / IP, Named Pipes, Shared Memory, SQL Native Client (SNAC) and OLE DB Drivers / Providers, Query Processing and Query Optimizer (QO) Components, Database Engine, Storage Engine, MDAC, SQL Server Engine, SQL & Database Manager, Buffer Manager. Write Ahead Log (WAL), Lazy Writer, SQLOS & CLR Components. MDAC & XMLA Components for Client - Server Communication. CHECKPOINT, LAZY WRITER, WAL Threads. SQL CMD Tool Usage.

Chapter 16: Performance Tuning - I

Identifying Long Running Queries & Activity Monitor, Important Dynamic Management Objects (DMV, DMF). Query Statistics and Cache Plans / Execution Plans, CROSSAPPLY, Avoiding Self Joins, Avoiding Sub Queries and Conditions: Real-world Scenarios. Common Table Expressions (CTE), In-Memory. Stored Procedures for Parameterized CTE Sub Queries. Performance Baselines, Query Tuning. RECURSIVE CTEs & Performance. ANCHOR & RECURSIVE Members. Termination Checks. Join Options For Query Tuning: HASH JOIN, LOOP JOIN, MERGE JOIN.

Chapter 17: Performance Tuning - 2

Big Data – Query Tuning Considerations, Table Partitions For Performance Tuning 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. STATISTICS. Column Statistics and Index Statistics - Auto Creations, Manual Update of Column Statistics - GUI & Scripting. LIVE Query Statistics. SAMPLE and FULLSCAN.

Chapter 18: Performance Tuning - 3

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 in FTS, Performance Tuning with FT Indexes. Performance Impact.

Chapter 19: Performance Tuning - 4

Index Internals and Execution Plans, Understanding Execution Plans, Statistics, Cost Index Fragmentation – Performance, Scans. FillFactor & Pad Index Options, Index Rebuilds (Online/Offline), Tuning Options, Index Reorganization Advantages. Page, Row Compressions with Indexes – Cautions, Filtered Indexes, Online Indexes, Indexes Views, GAM, SGAM Pages, Heaps, Clustered & Nonclustered Indexes, Database Tuning Advisor (DTA) – Usage with SQL Profiler. Creating Trace Tables & Workload Files. Profiler Filters and Templates. Tuning & Stored Procedure Templates.

Chapter 20: Performance Tuning – 5

Memory Optimized Tables – Configuration and Usage. Memory Optimized Filegroups Creation & Database Settings, MEMORY_OPTIMIZED_ELEVATE_SNAPSHOT Options, Nonclustered Primary Key Columns with Optimization Settings with Indexes and Real-world considerations. Degree Of Parallelism (DOP), Processor Settings and Counters. Thresholds, Recommended Thread Counts & Fibers. Temporal Tables and SYSTEM_VERSIONING, Temporal Tables For DML Audits, Performance Impact. Creating System Versioned Temporal Tables, Bulk Inserts, OPENROWSET with Temporal Tables. PERFMON Counters. Query Tuning Checklist For SQL Developers.

APPLICABLE FOR PLAN B:

Chapter 21: REAL-TIME PROJECT IMPLEMENTATION

Phase 1: Understanding Project Requirement - Banking Database Design with FileGroups, Schemas Table Design with FileGroups, Schemas Defining Constraints, Relations, Synonyms Design Data Structures for Optimal Performance

Phase 2: Views for Data Inserts, Joined Queries Common Reporting Functions, User Access RANK, ROW_NUMBER, DENSE_RANK, PIVOT INSERTS with PIVOT, Calculations, Sub Queries Implement Indexes and Column Store Options

Phase 3: End-to-End Implementation - Data Validations Stored Procedures for Dynamic Data Inserts Updatable Views and Triggers for DML, Indexes DML Operations with PIVOT and Pagination ADVANCED, COMPLEX Stored Procedures in T-SQL DB Documentation Tools, Deployment Options

Reading Log Files and Data Audits & 3rd Party Tools Transaction Audits and Offline Query Logs for SQL DEV Import & Export Operations in SQL Server (SSIS) Bulk Operations and Bulk Loads. Database Scripting Options Creating and Using Data Sheets - Project Documentation

1. RESUME PREPERATION

2. INTERVIEW GUIDANCE, LATEST INTERVIEW QUESTIONS

3. MOCK INTERVIEW

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

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

DAY 25: 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 26: 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 27: 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

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

SQL Server T-SQL Classroom 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

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