Contents

Introduction to SQL Server and its Toolset

This module introduces you to the entire SQL Server platform and its major tools. This module also covers editions, versions, basics of network listeners, and concepts of services and service accounts.

Introduction to SQL Server Platform

Working with SQL Server Tools

Configuring SQL Server Services

Lab : Introduction to SQL Server and its Toolset

Verifying SQL Server Component Installation

Altering Service Accounts for New Instance

Enabling Named Pipes Protocol for Both Instances

Creating Aliases for AdventureWorks and Proseware

Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port

Working with Data Types

This module describes the data types supported by SQL Server and how to work with them.

Using Data Types

Working with Character Data

Converting Data Types

Working with Specialized Data Types

Lab : Working with Data Types

Choosing Appropriate Data Types

Writing Queries With Data Type Conversions

Designing and Creating Alias Data Types

Designing and Implementing Tables

This module describes the design and implementation of tables.

Designing Tables

Working with Schemas

Creating and Altering Tables

Lab : Designing and Implementing Tables

Improving the Design of Tables

Creating a Schema

Creating the Tables

Designing and Implementing Views

This module describes the design and implementation of views.

Introduction to Views

Creating and Managing Views

Performance Considerations for Views

Lab : Designing and Implementing Views

Designing, Implementing and Testing the WebStock Views

Designing and Implementing the Contacts View

Modifying the AvailableModels View

Planning for SQL Server Indexing

This module describes the concept of an index and discusses selectivity, density, and statistics. It covers appropriate data type choices and choices around composite index .structures.

Core Indexing Concepts

Data Types and Indexes

Single Column and Composite Indexes

Lab : Planning for SQL Server Indexing

Exploring Existing Index Statistics

Designing Column Orders for Indexes

Implementing Table Structures in SQL Server

This module covers clustered indexes and heaps.

SQL Server Table Structures

Working with Clustered Indexes

Designing Effective Clustered Indexes

Lab : Implementing Table Structures in SQL Server

Creating Tables as Heaps

Creating Tables with Clustered Indexes

Comparing the Performance of Clustered Indexes vs. Heaps

Reading SQL Server Execution Plans

This module introduces the concept of reading execution plans.

Execution Plan Core Concepts

Common Execution Plan Elements

Working with Execution Plans

Lab : Reading SQL Server Execution Plans

Actual vs. Estimated Plans

Identify Common Plan Elements

Query Cost Comparison

Improving Performance through Nonclustered Indexes

This module explains nonclustered indexes, covering indexes and included columns.

Designing Effective Nonclustered Indexes

Implementing Nonclustered Indexes

Using the Database Engine Tuning Advisor

Lab : Improving Performance through Nonclustered Indexes

Reviewing Nonclustered Index Usage

Improving Nonclustered Index Designs

Using SQL Server Profiler and Database Engine Tuning Advisor

Nonclustered Index Design

Designing and Implementing Stored Procedures

This module describes the design and implementation of stored procedures.

Introduction to Stored Procedures

Working With Stored Procedures

Implementing Parameterized Stored Procedures

Controlling Execution Context

Lab : Designing and Implementing Stored Procedures

Creating Stored Procedures

Creating a Parameterized Stored Procedure

Altering the Execution Context of Stored Procedures

Merging Data and Passing Tables

This module covers table types, table valued parameters and the MERGE statement as used in stored procedures.

Using the MERGE Statement

Implementing Table Types

Using Table Types as Parameters

Lab : Merging Data and Passing Tables

Creating a Table Type

Using a Table Type Parameter

Using a Table Type with MERGE

Creating Highly Concurrent SQL Server Applications

This module covers transactions, isolation levels, and designing for concurrency.

Introduction to Transactions

Introduction to Locks

Management of Locking

Transaction Isolation Levels

Lab : Creating Highly Concurrent SQL Server Applications

Detecting Deadlocks

Investigating Transaction Isolation Levels

Handling Errors in T-SQL Code

This module describes structured exception handling and gives solid examples of its use within the design of stored procedures.