Introduction

Keen Technologies TFS Training provides an overview of Microsoft’s Application Lifecycle Management (ALM) stack, then drills on how to use Team Foundation Server (TFS) to support your team’s use of ALM best practices.

Course Content

1.SQL Server Introduction & Installation

• SQL Server 2012 Installation
• Service Accounts & Use, Authentication Modes & Usage, Instance Configurations
• SQL Server Features & Purpose
• Using Management Studio (SSMS)
• Configuration Tools & SQLCMD
• Conventions & Collation

2.SQL Server 2012 Database Design

• SQL Database Architecture
• Database Creation using GUI
• Database Creation using T-SQL scripts
• DB Design using Files and File Groups
• File locations and Size parameters
• Log files and placement
• Database Structure modifications

3.SQL Server Tables & Data

• 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
• Use of WHERE, IN and BETWEEN
• Variants of SELECT statement
• ORDER BY
• GROUPING
• HAVING
• ROWCOUNT and CUBE Functions

4.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

5.Views And Row Data Security

• Benefits of Views in SQL Database
• Views on Tables and Views
• SCHEMA BINDING and ENCRYPTION
• Issues with Views and ALTER TABLE
• Common System Views and Metadata
• Common Dynamic Management views
• Working with JOINS inside views

6.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 & Non Clustered Indexes

7.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 level Stored Procedures
• Dynamic SQL and parameterization

8.User Defined Functions And Usage

• Scalar Valued Functions
• Types of Table Valued Functions
• SCHEMABINDING and ENCRYPTION
• System Functions and usage
• Date Functions
• Time Functions
• String and Operational Functions
• ROW_COUNT
• GROUPING Functions

9.Triggers And Cursors – Memory Limitations

• Why to use Triggers?
• DML Triggers and Performance impact
• INSERTED and DELETED memory tables
• Data Audit operations & Sampling
• Database Triggers and Server Triggers
• Bulk Operations with Triggers

10.Cursors – Memory Limitations

• Cursor declaration and Life cycle
• STATIC
• DYNAMIC
• SCROLL Cursors
• FORWARD_ONLY and LOCAL Cursors
• KEYSET Cursors with Complex SPs

11.Transaction Types And Real-World Scenario

• ACID Properties and Scope
• EXPLICIT Transaction types
• IMPLICIT Transactions and options
• AUTOCOMMIT Transaction and usage
• SAVEPOINT and Query Blocking
• Complex SPs with Transactions
• Linked Server and DTC operations