Tag Archives: sql dba

MS SQL Server Online Training

MS SQL Server Online Training Course Content

What is an MS SQL Server? MS SQL Server Online Training

SQL Server is Relational Database Management System (RDBMS). It is a full-featured Database primarily designed to against competitors Oracle DB Database and MySQL. Like SQL Server supports ANSI SQL, RBDMS, the standard SQL language.

Module I : SQL & T-SQL Server

SQL Server Introduction & Installation
  • SQL Server 2014 & 2016 Installation
  • Services, Service Accounts and Usage
  • Authentication Modes, Security, Logins
  • Instance Configuration Options & Rules
  • SQL Server Components and Features
  •  Configuration Tools, Services and Use
  • Naming Conventions & Collation Settings
  • Using Management Studio (SSMS) Tool
  • Post Installation Procedures and WMI
  • Firewall Settings, Remote Configuration
SQL Server Database Design
  • arrow-right-blue   SQL Server Database Architecture
  • arrow-right-blue   SQL Database Creation using GUI (SSMS)
  • arrow-right-blue   Database Creation using T-SQL Scripts
  • arrow-right-blue   DB Design using Files and File Groups
  • arrow-right-blue   DB File locations and Size Parameters
  • arrow-right-blue   Capacity Planning and Database Design
  • arrow-right-blue   Transaction Log Files – Size, Placement
  • arrow-right-blue   DB Structure Modifications & Ownership
  • arrow-right-blue   UI Limitations, ALTER Command Options
SQL Server Table Design
  • arrow-right-blue   Table creation using T-SQL Scripts
  • arrow-right-blue   SQL Server Tables & Filegroup Routing
  • arrow-right-blue   INSERT, UPDATE, DELETE Operations
  • arrow-right-blue   Single Row and Multi-Row Table Inserts
  • arrow-right-blue   Table Aliases, Column Aliases, Usage
  • arrow-right-blue   Table creation with Schemas, Filegroups
  • arrow-right-blue   DELETE Versus TRUNCATE, Usage
  • arrow-right-blue   SELECT Queries, Variants & Sub Queries
  • arrow-right-blue  Schemas and Nested Queries, Aliases
  • arrow-right-blue  Temporary Tables & Usage, Performance
Normal Forms, Keys & Constraints
  • arrow-right-blue  Data Integrity and Normal Forms (BCNF)
  • arrow-right-blue  Tables with Keys, Constraints (BCNF)
  • arrow-right-blue  NULL and IDENTITY Properties – Usage
  • arrow-right-blue  UNIQUE KEY Constraint and NOT NULL
  • arrow-right-blue  PRIMARY KEY Constraint & Importance
  • arrow-right-blue  FOREIGN KEY and REFERENCE Attributes
  • arrow-right-blue  CHECK and DEFAULT Constraints, Usage
  • arrow-right-blue  Duplicating Identity Property Values
  • arrow-right-blue  Disabling Constraints & Composite Keys
Views, Joins and Sub Queries
  • arrow-right-blue  SELECT Queries – IIF, SWITCH, CHOOSE
  • arrow-right-blue  GROUP BY, GROUPING, HAVING, OVER
  • arrow-right-blue  Sub Queries, Nested Queries and EXISTS
  • arrow-right-blue  DELETE FROM SELECT, Query Variants
  • arrow-right-blue  UPDATE FROM SELECT, Query Variants
  • arrow-right-blue  INSERT INTO SELECT & Data Copy
  • arrow-right-blue  Inner Joins – Purpose and Performance
  • arrow-right-blue  Outer Joins – Advantages, Usage
  • arrow-right-blue  Cross Joins – Advantages, Limitations
  • arrow-right-blue  Self Joins, Merge Joins, Sub Queries
  • arrow-right-blue  Working with Table Variables, Types
  • arrow-right-blue  WHILE Loops and Iteration Controls
  • arrow-right-blue  TARGET & NOT MATCH with MERGE
  • arrow-right-blue  NOCHECK and SET Options in Joins
  • arrow-right-blue  Sub Queries, Nested Queries and Usage
  • arrow-right-blue  Using Sub Queries for Data INSERTS
  • arrow-right-blue  Query Joins with UPDATE/DELETE

Module II : T-SQL Queries & Programming

Indexes and Query Tuning
  • arrow-right-blue Indexes Architecture, Types and Purpose
  • arrow-right-blue Clustered Indexes – Architecture, Usage
  • arrow-right-blue Non Clustered Indexes & Page Usage
  • arrow-right-blue Indexes on Table Columns With Options
  • arrow-right-blue Index Scan, Index Seek and Key Lookup
  • arrow-right-blue SORT_IN_TEMPDB & FillFactor Options
  • arrow-right-blue INCLUDED Indexes & Query Optimizer
  • arrow-right-blue Tuning Materialized (Indexed) Views
  • arrow-right-blue Indexes for Joins & UNIQUE Constraints
  • arrow-right-blue Using Indexes in Views and Query Plans
Stored Procedures (SPs) & Tuning
  • arrow-right-blue Stored Procedures – Purpose and Usage
  • arrow-right-blue Variables & Parameters (IN / OUT)
  • arrow-right-blue Stored Procedures for Data Validations
  • arrow-right-blue Stored Procedures for Dynamic Queries
  • arrow-right-blue Stored Procedures for Data Reporting
  • arrow-right-blue System Procedures & Metadata Access
  • arrow-right-blue IF.. ELSE and IF .. ELSEIF Conditions
  • arrow-right-blue Common Table Expressions (CTE), Uses
  • arrow-right-blue CTE for Data Retrieval and Updates
  • arrow-right-blue Recursive CTE and Query Performance
  • arrow-right-blue Dynamic SQL Queries and Parameters
  • arrow-right-blue OUTPUT Parameters in Procedures
  • arrow-right-blue SPs for Table Variables and Temp Tables
Cursors and Joins with Procedures (SPs)
  • arrow-right-blue Cursor Variables in T-SQL Queries
  • arrow-right-blue Cursor Variables in Joins & Data Access
  • arrow-right-blue Cursor Variables for Record Iterations
  • arrow-right-blue Cursor For Dynamic SQL Programming
  • arrow-right-blue Cursor Types – Benefits and Options
  • arrow-right-blue FORWARD_ONLY & SCROLL Cursors
  • arrow-right-blue STATIC and DYNAMIC Cursors – Memory
  • arrow-right-blue LOCAL & GLOBAL Cursor Types – Scope
  • arrow-right-blue Stored Procedures (SP), KeySet Cursors
  • arrow-right-blue SPs with Table Variables Joins & Cursors
  • arrow-right-blue SPs with Cursors, Dynamic Data Loads
Functions (UDF) and Sub Queries
  • arrow-right-blue Functions: Types, Purpose and Usage
  • arrow-right-blue Scalar Value Returning Functions, Usage
  • arrow-right-blue Inline Table Functions and Purpose
  • arrow-right-blue Multi-line Table Functions & Purpose
  • arrow-right-blue Table Variables – Purpose and Usage
  • arrow-right-blue SCHEMABINDING, ENCRYPTION Options
  • arrow-right-blue Using System Functions & Metadata
  • arrow-right-blue Date Functions, CAST and CONVERT()
  • arrow-right-blue CROSSAPPLY and GROUP BY Operations
  • arrow-right-blue ROW_COUNT(), GROUPING(), ROLLUP()
  • arrow-right-blue Joins with Functions and Views
Triggers and Data Audits, DTC
  • arrow-right-blue Use of Triggers – Purpose and Scope
  • arrow-right-blue DML Triggers and Performance Impact
  • arrow-right-blue FOR / AFTER DML Triggers – Importance
  • arrow-right-blue INSTEAD OF DML Triggers – Importance
  • arrow-right-blue INSERTED and DELETED Memory Tables
  • arrow-right-blue Triggers for DML Audit Operations
  • arrow-right-blue Triggers for Data Sampling Operations
  • arrow-right-blue Database and Server Triggers – Usage
  • arrow-right-blue Using Triggers for Updatable Views
  • arrow-right-blue Triggers for Data Audits and MSDTC
Module III : Real Time Project
Understanding Real-time Databases
  • arrow-right-blue Real-time Project Implementation
  • arrow-right-blue Requirement Analysis & OLTP Modeling
  • arrow-right-blue Identifying Entities and DB Objects
  • arrow-right-blue ER (Entity Relation) Models for OLTP
  • arrow-right-blue Database Diagrams and Scripting
  • arrow-right-blue Normal Forms and Types in DB Design
  • arrow-right-blue Advantages of BCNF Normal Form
  • arrow-right-blue Data Type Checks and Column Integrity
  • arrow-right-blue Synonyms and Object Level Access
  • arrow-right-blue MERGE, PIVOT, Clone Operations
  • arrow-right-blue New Features of SQL Server 2016
  • arrow-right-blue SQL Server 2014 Vs SQL 2016
  • arrow-right-blue Table Space & Memory Tables
  • arrow-right-blue Query Store in SQL 2016
  • arrow-right-blue Managed DB Objects in SQL 2016
Multi Server Data Access, Query Tuning

Linked Servers and Data Distribution
Configuring Linked Servers (Remote)
sp_addlinkedserver & sp_addsrvlogin
SQL Server & Non-SQL Server Products
Using MSDTC for Remote Queries
Using OPENROWSET for Remote Queries
Multi User and Multi Database Access
Real-world Query Blockings & Solutions
Self Joins and CTEs – Query Tuning
Using CTE and Temp Tables in SProcs
Transactions in Procedures (SPs), Functions

Understanding Transactions Concepts
ACID Properties, Transaction Types
EXPLICIT Transactions and Usage
IMPLICIT Transaction Types, Options
AUTOCOMMIT Transaction Advantages
Transactions Nesting – Rules & Usage
SAVEPOINT, Query Blocking Scenarios
Lock Hints and Remote CTE Queries
XML Data and Full Text Search (FTS)

XML Formats and Query Options
Sub Queries in T-SQL with XML
Releated Sub Queries with XML
XML PATH and Custom ROOT Options
Indexing XML Data for Performance
XML Operations on Views, Synonyms
Nested Queries & XML Limitations
KEYSET Cursors with XML Options
Full Text Search Service Options
Full Text Catalog and FT Indexes
Storing BLOB / CLOB Data (Docs, Pdf)
BULK..INSERT Commands – Options
OPENROWSET and Bulk Data Loads
SQL Server Architecture (Detailed)

Gearing for 70-461 Certification
Client Server Architecture – Detailed
TCP/IP, Named Pipes, Shared Memory
TDS Packets and Network Options
Storage Architecture, Transaction, IO
Query Parse, Optimizer, Mini LSN
Write Ahead Log, Lazy Writer Threads
SQL Engine and SQL Manager @ Query
SQLOS and Thread, Log Schedulers

 

Top ↑