SQL Server

BASIC CONCEPTS

  • Database Concepts
  • RDBMS Concepts
  • Data Models
  • Constraints
  • Normalization- 1NF,2NF,3NF,BCNF etc
  • ER Diagrams

SQL SERVER CONCEPTS

  • Introduction to Sql Server 2008 R2
  • Sql Server 2008 Features
  • Server Types
  • Editions
  • Components
  • Management Studio
  • T-SQL Features
  • New Operators
  • DML Statements

T-SQL

  • DDL
  • Data Types
  • System Defined Data Types
  • User defined Data
  • Create/modify/delete tables
  • Operators
  • DML
  • DCL
  • Using Select Statement

MAINTAINING DATABASE INTEGRITY

  • Constraints
  • Rules
  • Entity Integrity
  • Domain Integrity
  • Referential Integrity

SUBQUERIES

  • Built-in function
  • Nested Sub Queries
  • Correlated Sub Queries
  • Derived tables
  • Recursive queries
  • Common Table Expression

JOINS

  • Cross Join, Inner Join, Self Join, Outer Join

INDEXES

  • Types of Indexes
  • • Creating indexes
  • Modifying indexes
  • Dropping indexes

VIEWS

  • About View
  • Creating Views
  • Altering Views
  • Materialized Views
  • Distributed View

STORED PROCEDURES

  • Advantages
  • System Stored Procedures
  • Extended Stored Procedures
  • Parameter Handling

USER DEFINED FUNCTIONS

  • Returning Scalar Value
  • Returning Table of Data
  • Deterministic Functions
  • Non Deterministic Functions

TRIGGERS

  • Constraints Vs Triggers
  • After Triggers
  • Instead of Trigger
  • DDL Triggers
  • Altering and Dropping Triggers

CURSORS

  • Select Vs Cursors
  • Types of Cursors
  • Creating Cursors
  • Open/Fetch/Close Cursors
  • Using Cursors in Stored Procedures and Triggers

MICROSOFT .NET FRAME WORK INTEGRATION

  • CLR Integration
  • UDA
  • UDF
  • Stored Procedures
  • Triggers

TRANSACTIONS AND LOCKS

  • ACID Properties
  • Implicit Transactions
  • Explicit Transactions
  • Transaction Process
  • Concurrency Problems
  • Lock Types
  • Distributed Transactions
  • Dead Locks

DATA TRANSFORMATION SERVICES

  • BCP
  • DTS

ADMINISTRATIVE TASKS

  • Creating Database
  • Copy, Maintaining Database
  • Security Concepts
  • Schemas, Permissions
  • Backup and Restore Database
  • Sql Server Agent
  • Sql Server Profiler
  • Database Engine Tuning Adviser

SQL SERVER MANAGEMENT OBJECTS

  • Programming with SMO
  • Connecting to Sql Server
  • Scripting

XML IN SQL SERVER

  • XML in Sql Server

WORKING WITH IMAGES

  • Working with Images

INTRODUCTION TO MS- BI

  • SSRS
  • Standard, Ad- hoc Embedded Reports
  • SSIS
  • SSAS

SQL SERVER 2012 FEATURES

  • Sequence
  • OFFSET and FETCH
  • WITH RESULT SETS in stored procedure THROW in triggers