SQL Server Training with Realtime Project

SQL Server Introduction & Installation

  • SQL Server 2012 & 2014 Installation
  • Service Accounts & Types, Usage
  • Authentication Modes & Usage
  • Instance Configurations Options
  • SQL Server Features & Purpose
  • Using Management Studio (SSMS)
  • Configuration Tools & Use
  • Naming Conventions & Collation
Indexes and Query Tuning

  • Need for Indexes & Usage?
  • Indexing Table & View Columns
  • Index SCAN and SEEK
  • INCLUDED Indexes & Usage
  • Materializing Views (storage level)
  • Composite Indexed Columns & Keys
  • Indexes and Table Constraints
  • PRIMARY KEYS and NonClustered Indexes
SQL Server 2012 Database Design

  • SQL Server Database Architecture
  • Database Creation using GUI
  • Database Creation using T-SQL scripts
  • DB Design using Files and File Groups
  • File locations and Size parameters
  • DB Log Files and Placement
  • Database Structure modifications
Stored Procedures and Benefits

  • Why to use Stored Procedures?
  • Types of Stored Procedures
  • Use of Variables and parameters
  • SCHEMABINDING and ENCRYPTION
  • INPUT and OUTPUT parameters
  • System defined Stored Procedures
  • Dynamic SQL Queries and parameterization
SQL Server Tables & Queries

  • SQL Server Database Tables
  • Table creation using T-SQL Scripts
  • Naming Conventions for Columns
  • Single Row and Multi-Row Inserts
  • Table Aliases, Column Aliases & Usage
  • Table creation using Schemas
  • Basic INSERT, UPDATE, DELETE
  • SELECT queries and Schemas
User Defined Functions and Joins

  • Where to use Functions?
  • Scalar Valued Functions
  • Types of Table Valued Functions
  • SCHEMABINDING and ENCRYPTION
  • System Functions and Joins
  • Date Functions, Time Functions
  • String and Operational Functions
  • ROW_COUNT, GROUPING Functions
Data Validations and Constraints

  • Need for Data Integrity
  • Table creation using Constraints
  • NULL and IDENTITY properties
  • UNIQUE KEY Constraint and NOT NULL
  • PRIMARY KEY Constraint & Usage
  • CHECK and DEFAULT Constraints
  • Naming Composite Primary Keys
  • Disabling Constraints & Other Options
Triggers and Memory Limitations

  • Why to use Triggers?
  • DML Triggers and Performance impact
  • INSERTED and DELETED memory tables
  • Triggers for Audit operations
  • Triggers for Data Sampling
  • Database Triggers and usage
  • Server Triggers and limitations
  • Triggers for Bulk Operations
JOINS and Sub Queries

  • Variants of SELECT statement
  • Use of WHERE, IN and BETWEEN
  • ORDER BY, GROUPING, HAVING
  • Use of Predicates and TOP
  • Sub Queries and Nested Queries
  • DELETE FROM SELECT query variants
  • INSERT INTO … SELECT statement
Cursor Variables and Programming

  • Benefits of Cursor variables
  • Cursor declaration and life cycle
  • STATIC and DYNAMIC cursors
  • SCROLL and FORWARD_ONLY cursors
  • LOCAL and GLOBAL cursors
  • KEYSET Cursors and usage
  • Embedding Cursors in SPs and UDFs
Views, Joins and Queries

  • Benefit of Views in SQL Database
  • Views on Tables and Views
  • SCHEMABINDING and ENCRYPTION
  • Issues with Views and ALTER TABLE
  • Common System Views and Metadata
  • Common Dynamic Management views
  • Working with JOINS inside views
Transactions & Realworld Scenario

  • Transactions and Use
  • ACID Properties and Scope
  • EXPLICIT Transaction types
  • IMPLICIT Transactions and options
  • AUTOCOMMIT Transaction and usage
  • SAVEPOINT and Query Blocking
  • Complex SPs with Transactions
SQL School contact@sqlschool.com USA: +1 (510) 400 – 4845 India: +91 (0) 9666440801    /    +91 (040) 64577244

About sqlschool

SQL School (Regd: SequelGate Innovative Technologies Pvt. Ltd.) is one of fastest growing organization delivering Realtime Training and Realtime Projects exclusively on Microsoft SQL Server. Our Training services include SQL DBA Online Training, MSBI Online Training, SQL DBA Classroom Training, MSBI Classroom Training and Realtime Projects. SQL School, established in February, 2008 is now, one of the best institute offering Trainings on SQL Server, SQL Database Administration (DBA) and Business Intelligence (BI) technologies. We have been working with numerous consultancies in India, US, UK and Australia. We undertake Online Trainings and Corporate Trainings on SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012 (DENALI) versions. We make sure that all our sessions are very much interactive and well structured. We encourage every participant to come up with his / her own queries during & after the training sessions. We prefer practical approach rather than theoretical information to master the technical depth of the technology. We provide excellent Lab Handouts for practice, Realtime Case Studies and Projects on SQL Server Administration (SQL DBA) and Microsoft Business Intelligence (MS BI) Training courses. Free LIVE DEMOs would be provided prior to training registration. Specific DAY to DAY Course Plan will also be shared prior to training registration to ensure transparency of our Training services.