Microsoft SQL Server 2005 Professional Training Series

This course is for developers coming from Visual Studio or other development backgrounds who want to learn to build successful databases solutions with SQL Server 2005. Even developers who've already used Oracle, MySQL, DB2 or other relational database management systems will find this course useful for getting up to speed on SQL Server 2005.

Course includes 90+ hours of total training time...

20 modules of training

Over 30 hours of media run time

Over 1100 pages of printable courseware

Step-by-step hands-on-labs

Sample code

In this course, You'll learn about the features that are available in SQL Server; how to design and create a database; and how to build basic queries using Transact-SQL, the language of SQL Server. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and programmers can now use .NET languages like C# and Visual Basic to build database objects.

SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. You'll learn how to take advantage of the new, user-friendly management console that integrates both authoring and administrative tasks. You'll learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration services, implementing security, and Microsoft's new Business Intelligence (BI) suite.

In this course, you will learn:

Install and configure Microsoft SQL Server 2005.

Understand basic database design principles and how to implement them in SQL Server.

Learn the fundamentals of writing Transact-SQL queries that retrieve or modify data.

Understand the differences between view, stored procedures, triggers, and user-defined functions.

Perform calculations with Visual Basic and Visual C# code rather than T-SQL.

Use the new Common Table Expressions for creating hierarchical queries.

Understand the SQL Server security model.

Configure and tune SQL Server, and monitor database activity.

Support distributed users by implementing SQL Server Replication.

Publish data to the Web, and take advantage of SQL Server’s rich support for XML.

Understand Business Intelligence to add reporting and analysis capabilities.

Prerequisites:

To get the most out of the Microsoft SQL Server 2005 course, you should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer's perspective.

A Tour of SQL Server 2005

SQL Server 2005 Editions, Components, and Tools

Using SQL Server Management Studio (SSMS)

Working with Tables and Queries

New Transact-SQL Features

Business Intelligence Services

Installing SQL Server 2005

Preparing for Installation

Upgrading an Earlier Version

Installation Steps

Configuring the Server

Designing and Creating a Database

Relational Database Design Principles

Implementing the Design

Data Selection Queries

Understanding Transact-SQL

The SELECT Statement

The WHERE Clause

Using ORDER BY to Sort Data

The GROUP BY Clause

Joining Tables

Modifying Data

Modifying Data

Inserting Data

Updating Data

Deleting Data

Understanding Transaction Isolation

Working with SQL Server Management Studio

Getting Started with SSMS

Exploring the Object Explorer

Working with the Query Editor

Using SQL Server Books Online

Transact-SQL Programming

Overview of Transact-SQL

Using Built-In Functions

Controlling Flow

Ranking Results

Transactions and Error Handling

Transaction Concepts

Applications and Transactions

Creating Explicit Transactions

Using TRY/CATCH Error Handling

Creating Views

What Is a View?

Creating Views

Updating Data Using a View

Using Computed Columns

Indexed Views

Partitioned Views

Creating Stored Procedures and Triggers

Creating Stored Procedures

Creating Triggers

Creating User-Defined Functions

User-Defined Function Overview

Scalar Functions

Inline Table-Valued Functions

Multi-Statement Table-Valued Functions

Using Functions, Views, and Stored Procedures

Using .NET Code in SQL Server 2005

Writing SQLCLR Code

SQLCLR Code Modules

Managing Code Modules

SQLCLR Security

T-SQL vs. .NET Code

Advanced Query Techniques

Full-Text Search

Generating XML with FOR XML

Using APPLY

Creating Recursive Queries

Creating Pivot Queries

Executing Dynamic SQL

Understanding and Implementing Security

Security Overview

Authentication

Authorization

Permissions

Data Encryption

Security Epilog

Analyzing and Tuning Performance

Evaluating Performance

Monitoring with SQL Server Profiler

Tuning Queries

Indexes and Partitions

Using the Database Engine Tuning Advisor

Understanding SQL Server Performance Problems

Automating Administrative Tasks

SQL Server Agent

Maintenance Plans

SQL Management Objects (SMO)

Programming Replication

Overview of SQL Server Replication

Replication Programming Interfaces

Configuring Replication

Synchronizing Data

Using Integration Services

Importing and Exporting Data

Integration Services Tools

Building a Package

Troubleshooting a Package

Analysis Services

Understanding Analysis Services

Creating a Unified Dimensional Model

Data Mining

Introduction to Reporting Services

Understanding Reporting Services

Configuring Reporting Services

Building a Simple Report

Creating, Publishing, and Viewing Reports

Using and Managing Published Reports