TOPICS

Chapter 1: SQL Server Introduction & Installation

SQL Server 2012 & 2014 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

Chapter 2: SQL Server Database Design

SQL Server Database Architecture

SQL Database Creation using GUI (SSMS)

Database Creation using T-SQL Scripts

DB Design using Files and File Groups

DB File locations and Size Parameters

Capacity Planning and Database Design

Transaction Log Files – Size, Placement

DB Structure Modifications & Ownership

UI Limitations, ALTER Command Options

Chapter 3: SQL Server Table Design

Introduction to SQL

Data Definition Language (DDL)

Data Manipulation Language (DML)

Table creation using T-SQL Scripts

Create Tables using the Designer Tools

Alter Table

SELECT Queries

Defining a Null Value

INSERT Operations

The WHERE Clause

UPDATE Operations

DELETE Operations

Single Row Insert

Multi-Row Table Inserts

Alias

Schemas

Table creation with Schemas, Filegroups

DELETE Versus TRUNCATE, Usage

Sub Queries

Temporary Tables & Usage, Performance

Chapter 4: Normal Forms, Keys & Constraints

Data Integrity and Normal Forms (BCNF)

Data Integrity and Normal Forms (BCNF)

IDENTITY (AUTO INCREMENT) Usage

UNIQUE KEY Constraint

NOT NULL Constraint / Required Columns

PRIMARY KEY Constraint & Importance

FOREIGN KEY and REFERENCE Attributes

CHECK Constraint

DEFAULT Constraints, Usage

Duplicating Identity Property Values

Disabling Constraints & Composite Keys

Chapter 5: Views, Joins and Sub Queries

ORDER BY

DISTINCT

SELECT Queries – IIF, CASE, CHOOSE

GROUP BY, HAVING, OVER

EXISTS, NOT EXISTS

DELETE FROM SELECT, Query Variants

UPDATE FROM SELECT, Query Variants

INSERT INTO SELECT & Data Copy

Inner Joins – Purpose and Performance

Outer Joins – Advantages, Usage

Cross Joins – Advantages, Limitations

Self Joins, Merge Joins, Sub Queries

Working with Table Variables, Types

WHILE Loops and Iteration Controls

TARGET & NOT MATCH with MERGE

NOCHECK and SET Options in Joins

Sub Queries, Nested Queries and Usage

Using Sub Queries for Data INSERTS

Query Joins with UPDATE/DELETE

Chapter 6: Indexes and Query Tuning

Indexes Architecture, Types and Purpose

Clustered Indexes – Architecture, Usage

Non Clustered Indexes & Page Usage

Indexes on Table Columns With Options

Index Scan, Index Seek and Key Lookup

SORT_IN_TEMPDB & FillFactor Options

INCLUDED Indexes & Query Optimizer

Tuning Materialized (Indexed) Views

Indexes for Joins & UNIQUE Constraints

Using Indexes in Views and Query Plans

Chapter 7: Stored Procedures (SPs) & Benefits

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 & Metadata Access

IF.. ELSE and IF .. ELSEIF Conditions

Common Table Expressions (CTE), Uses

CTE for Data Retrieval and Updates

Recursive CTE and Query Performance

Dynamic SQL Queries and Parameters

OUTPUT Parameters in Procedures

SPs for Table Variables and Temp Tables

Chapter 8: Cursors and Joins with Procedures (SPs)

Cursor Variables in T-SQL Queries

Cursor Variables in Joins & Data Access

Cursor Variables for Record Iterations

Cursor For Dynamic SQL Programming

Cursor Types – Benefits and Options

FORWARD_ONLY & SCROLL Cursors

STATIC and DYNAMIC Cursors – Memory

LOCAL & GLOBAL Cursor Types – Scope

Stored Procedures (SP), KeySet Cursors

SPs with Table Variables Joins & Cursors

SPs with Cursors, Dynamic Data Loads

Chapter 9: Functions (UDF) and Queries

Functions: Types, Purpose and Usage

Scalar Value Returning Functions, Usage

Inline Table Functions and Purpose

Multi-line Table Functions & Purpose

Table Variables – Purpose and Usage

SCHEMABINDING, ENCRYPTION Options

Using System Functions & Metadata

Date Functions, CAST and CONVERT()

CROSSAPPLY and GROUP BY Operations

ROW_COUNT(), GROUPING(), ROLLUP()

Joins with Functions and Views

Chapter 10: Triggers and Memory Limitations

Use of Triggers – Purpose and Scope

DML Triggers and Performance Impact

FOR / AFTER DML Triggers – Importance

INSTEAD OF DML Triggers – Importance

INSERTED and DELETED Memory Tables

Triggers for DML Audit Operations

Triggers for Data Sampling Operations

Database and Server Triggers – Usage

Using Triggers for Updatable Views

Triggers for Data Audits and MSDTC

Synonyms and Object Level Access

Chapter 11: Data Access in a Multi Server Environment

Linked Servers and Data Distribution

Configuring Linked Servers (Remote)

sp_addlinkedserver & sp_addsrvlogin

Using OPENROWSET for Remote Queries

Real-world Query Blockings & Solutions

Self Joins and CTEs – Query Tuning

Using CTE and Temp Tables in SProcs

Chapter 12: 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

Chapter 13: XML Data and Full Text Search

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