MS-SQL online training

Course Duration : 20 Hrs
SQL Server & T-SQL Course Content

 

SQL Server Introduction & Installation

  • SQL Server 2008 R2
  • Services, Service Accounts and Usage
  • Authentication Modes, Security, Logins
  • SQL Server Components and Features
  • Configuration Tools, Services and Use
  • Using Management Studio (SSMS) Tool

SQL Server 2008 R2 Database Design  

  • SQL Server Database Architecture
  • SQL Server Database Creation using GUI
  • Database Creation using T-SQL Scripts
  • DB Design using Files and File Groups
  • File Locations and File Size Parameters
  • Database Log Files, Growth and Placement
  • DB Structure Modifications and Ownership

SQL Server Tables and T-SQL Queries

  • Table creation using T-SQL Scripts
  • SQL Server Tables and Filegroup Routing
  • DML Operations: INSERT, UPDATE, DELETE
  • Single Row and Multi-Row Insert Statements
  • Table Aliases, Column Aliases & Usage
  • Table creation with Schemas & Filegroups
  • DELETE Versus TRUNCATE Statements
  • SELECT Queries, Variants and Sub Queries
  • Schemas and Nested Queries with Aliases
  • Temporary Tables & Usage, Performance

Constraints, Keys and Data Validation

  • Data Integrity and Normal Forms (BCNF)
  • Tables with Keys & Constraints (BCNF)
  • NULL and IDENTITY Properties – Usage
  • UNIQUE KEY Constraint and NOT NULL
  • PRIMARY KEY Constraint & Importance
  • FOREIGN KEY and REFERENCE Attributes
  • CHECK and DEFAULT Constraints, Usage
  • Duplicating Identity, Disabling Constraints

Joins, Sub Queries and Nested Queries

  • SELECT Statements
  • ORDER BY, GROUP BY, GROUPING, HAVING
  • Sub Queries, Nested Queries and EXISTS
  • DELETE from SELECT, UPDATE from SELECT
  • INSERT INTO … SELECT & Data Copy
  • Inner Joins – Purpose and Performance
  • Outer Joins – Types, Advantages and Usage
  • Cross Joins – Advantages and Limitations
  • Self Joins and Sub Queries

Joins, Views and Nested Queries

  • Benefits of Views – End User Access
  • Defining Views on Tables and Views
  • SCHEMABINDING and ENCRYPTION Options
  • ALTER TABLE Issues and Solutions with Views
  • System Views & Management Views

User Defined Functions and Joins

  • Functions – Syntax, Usage and Importance
  • Scalar Valued Functions and Examples
  • Table Valued Functions and Examples
  • SCHEMABINDING and ENCRYPTION Options
  • System Functions and Joins – CROSSAPPLY
  • Date, Time and Conversion Functions
  • String Functions and Meta-data Functions
  • ROWCOUNT, GROUPING, ROLLUP Functions
Indexes and Query Tuning

  • Need for Indexes, Index Types & Usage
  • Indexing Table and View Columns
  • Index SCAN, SEEK and LOOP Operations
  • Composite Indexed Columns & Keys
  • Materializing Views (Indexed Views)
  • RIDs, KEYs and Column Constraints
  • PRIMARY KEYS and Non Clustered Indexes
  • INCLUDED Column Indexes & Tuning

Stored Procedures and Benefits

  • Need for Stored Procedures & Syntax
  • Use of Variables and Query Parameters
  • Stored Procedures with Loops & IF… ELSE
  • SCHEMABINDING and ENCRYPTION Options
  • INPUT, OUTPUT & RESULTSET Parameters
  • System Stored Procedures and Nesting
  • Dynamic SQL Queries and Parameters
  • Stored Procedures , Data Validation Options

Triggers and Memory Limitations

  • Triggers – Architecture, Types & Usage
  • DML Triggers and Performance Impact
  • INSERTED and DELETED Memory Tables

Cursors, Stored Procedures, CTE

  • Cursor Declaration and Fetch Options
  • STATIC and DYNAMIC Cursor Types
  • SCROLL and FORWARD_ONLY Cursors
  • KEYSET Cursors and Temp Objects
  • Embedding Cursors in SPs and Functions
  • Temp Tables and Procedures with Cursors
  • Common Table Expressions (CTE) and Queries
  • Stored Procedures with CTE : Tuning Options

Data Access in Multi Server Environment

  • Linked Servers and Data Distribution
  • Configuring Linked Servers (Remote)
  • sp_addlinkedserver & sp_addsrvlogin
  • Multi User and Multi Database Access
  • Real-world Query Blockings & Solutions
  • Using CTE and Temp Tables in SProcs

Transactions in Procedures , 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

General DBA Concepts

  • Client Server Architecture – Detailed
  • TCP/IP, Named Pipes, Shared Memory
  • Storage Architecture, Transaction, IO
  • Query Parse, Optimizer, Mini LSN
  • Write Ahead Log, Lazy Writer Threads

Course Highlights:

  • Normal Forms, Joins, Views, Stored Procedures, CTEs, Indexed Views, Execution Plans, Triggers, Linked Servers, Query Blocking, Remote Queries and Sub Queries, Nested Queries and lot more..!

Online Training Enquiry