Microsoft SQL Server is a relational database management system developed by Microsoft. Browse through the contents of the program.
SQL Server Course Contents |
WMI Checks & Configuration Tool |
Server Components and Features |
Firewall Options and Instance Rules |
Service Accounts, Purpose and Usage |
Authentication Modes & Logins |
Instance Configuration Options |
Testing Instances and Services |
Management Studio (SSMS) Tool Usage |
Shared Features and Client Components |
SQL Database Architecture |
DB Catalog and Storage Options |
Data Files : Purpose and Storage |
Filegroups : Purpose and Usage |
Log files : Purpose and Audits |
Transaction Log Files - Considerations |
Sizing and Placement - Data Files |
Sizing and Placement - Log Files |
Locations and Sizing Recommendations |
Filegrowth and MAXSIZE Options |
Scripting Database Structures |
Database ALTERs and Considerations |
Designing Very Large Databases (VLDB) |
Placement of Files and Filegroups |
DB Design Parameters and Limitations |
Tables Design Concepts & Normal Forms |
Table Creation Process and Options |
File Groups and Table Design |
Role of Schemas and Table Design |
DML, DDL and SELECT Operations |
Single Row and Multi-Row Inserts |
Table Aliases, Column Aliases & Usage |
INSERT & SELECT Variants in T-SQL |
Data Types and Column Usage |
Row size Computationsand NULLs |
Basic INSERT, UPDATE, DELETE |
Multi-Row Inserts & SELECT |
SELECT Qeries with Schema References |
Basic Data Types and NULL Values |
Design Limitations with UPDATES |
Role of Log Files for DML Operations |
DELETE versus TRUNCATE Statements |
Constraints and Keys - Purpose, Usage |
Normal Forms and Relational DB Design |
OLTP Database and BCNF - Relations |
NULLABILITY Property and Importance |
UNIQUE KEY Constraints and Importance |
PRIMARY KEY Constraints and Limitations |
Use of FOREIGN KEY References |
FOREIGN KEY Constraints - Relation |
Schema Level and Table Level Relations |
CHECK Constraints - Usage, Limitations |
DEFAULT Column Constraints and Usage |
Identity Property with PRIMARY KEY |
Composite Primary Keys & Usage |
Constraints with Naming Conventions |
Benefit of Views in SQL Server Database |
Defining Views on Tables and Views |
Views as Stored SELECT Statements |
SCHEMABINDING & ENCRYPTION Options |
Issues with Views For Data Validations |
Cascaded Views and WITH CHECK OPTION |
Orphan Views - Realworld Scenarios |
Common System Views & Metadata |
Limitations & Real-world Considerations |
Database Diagrams and Entity Relations |
INNER JOIN and Table Intersections |
OUTER JOINS - Types, Table Comparisons |
CROSS JOIN, Alternatives and Usage |
Views on Multi Level Tables - Joins |
MERGE Operations - Benefits and Usage |
TARGET & NOT MATCH with MERGE |
Query Joins in Updates and Deletes |
Working with SET Options and Joins |
NOCHECK Options and Limitations |
Table Comparisons and Options |
Use of GO statement in Batch Scope |
Nested Queries - Benefits and Usage |
Sub Queries, Nested Queries and Usage |
Temporary Tables and Object Types |
Multi Value Insert Statements in T-SQL |
Synonyms - Creation, Purpose and Usage |
Synonyms - Joins, Usage & Limitations |
Indexes Architecture, Types and Purpose |
Clustered Indexes - Architecture, Usage |
Non Clustered Indexes - Usage |
Indexes on Table Columns With Options |
Execution Plans - Table Scan & Index Scan |
Index Seek and Multi Level Index Options |
SORT_IN_TEMPDB & FillFactor Options |
INCLUDED Indexes & Query Optimizer |
Materialized Views (Indexed Views) |
Constraints and Keys with Indexes Usage |
Indexes for Joins and UNIQUE Constraints |
Using Indexes in Views and Query Plans |
Stored Procedures - Purpose and Usage |
Use of Variables & Parameters (IN / OUT) |
Stored Procedures for Data Validations |
Stored Procedures for Dynamic Queries |
Stored Procedures for Data Reporting |
System Procedures For Metadata Access |
IF.. ELSE and IF .. ELSEIF Conditions |
RECOMPILE Options and Execution Plans |
Common Table Expressions (CTE) & Uses |
Using CTE for Data Retrieval and Updates |
CTE Usage and Query Performance |
Dynamic SQL Queries and Parameters |
OUTPUT Parameters in Stored Procedures |
Functions: Types, Purpose and Usage |
Scalar Value Returning Functions - Usage |
Inline Table Value Returning Functions |
Multi-line Table Value Returning Functions |
Data Generation with Table Variables |
Function Arguments - Usage and Options |
SCHEMABINDING and ENCRYPTION Options |
Using System Functions & Metadata |
Date Functions, CAST and CONVERT() |
CROSSAPPLY and GROUP BY Operations |
HAVING and WHERE Conditions in SELECT |
ROW_COUNT(), GROUPING(), ROLLUP() |
ELSE .. IF, WHILE and Iteration Loops |
Joins - Table Variables, Views & Functions |
Use of Triggers - Purpose and Scope |
DML Triggers and Performance Impact |
FOR / AFTER DML Triggers - Importance |
INSTEAD OF DML Triggers - Importance |
INSERTED and DELETED Magic Tables |
Triggers for DML Audit Operations |
Triggers for Data Sampling Operations |
Database Triggers - Design and Usage |
Server Triggers - Design and Usage |
Using Triggers for Bulk Operations (DML) |
Using Triggers for Updatable Views |
Using Triggers for Data Distribution |
Triggers with Join Queries and Cascades |
Memory Limitations & Performance |
Cursor Variables in T-SQL Queries |
Cursor Variables in Joins & Data Access |
Cursor Variables for Record Iterations |
Cursor For Dynamic SQL Programming |
Cursors With Stored Procedures Params |
Cursor Types - Benefits and Options |
FORWARD_ONLY & SCROLL Cursors |
STATIC and DYNAMIC Cursors - Memory |
LOCAL and GLOBAL Cursor Types - Scope |
KEYSET DRIVEN Cursors and Performance |
SPs with Joins, Temp Tables & Cursors |
Embedding Cursors in Stored Procedures |
SPs with Cursors for Dynamic Data Loads |
Deciding Table Variables and Temp Tables |
Understanding Transactions Concepts |
ACID Properties, Transaction and Types |
EXPLICIT Transaction Types & Usage |
IMPLICIT Transactions Types and Options |
AUTOCOMMIT Transactions - Advantages |
Transactions Nesting - Rules and Usage |
SAVEPOINT and Query Blocking Scenarios |
Real-world Blocking Problems - Solutions |
Lock Hints and CTE Queries - Tuning |
Using CTE and Temp Tables in SPs |