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

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 the SELECT statement

ORDER BY

GROUPING

HAVING

ROWCOUNT and CUBE Functions

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

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

Need for Indexes & Usage?

Indexing Table & View Columns

Index SCAN and SEEK

INCLUDED Indexes & Usage

Materialising Views (storage level)

Composite Indexed Columns & Keys

Indexes and Table Constraints

Primary Keys & Non-Clustered Indexes

Why 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 parameterisation

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

Why 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

Cursor declaration and Life cycle

STATIC

DYNAMIC

SCROLL Cursors

FORWARD_ONLY and LOCAL Cursors

KEYSET Cursors with Complex SPs

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

TFS licensing Overview

Team Explorer

Microsoft Project

Team Web Access

Microsoft Excel

SharePoint project portal

3rd party tools and Command-line

Integration with Visual Studio

TF and TFPT command-line tools

Managing and  Creating workspaces

Locking, unlocking, and conflicts

Basic and advanced workflows

TFS Proxy and MSSCCI Provider

Get, check-out, check-in

Check-in policies

Shelving, unshelving, and labelling

Merging, Branching, and patterns

Configuring a team project for Git

Controlling access to version control

TFS Sidekicks

Creating a backup schedule

TFS Backup and Restore strategies

Refreshing the client data cache

Understanding the TFS databases

The restore process