Microsoft SQL Server 2005 for Developers Training

How To Take This Class:

Live Instructor-Led Online Class > View Schedule or Register
Onsite Group Training > Request Pricing

Instructor-Led Online Course Tuition:

$2,250.00

Course Duration:

5 Days

Course Description:

In this Microsoft SQL Server 2005 for Developers Training course, students will learn about available features for the administration of SQL Server, and how to design, create, modify, analyze and tune databases. Students will learn how to build basic queries using Transact-SQL, how to take advantage of the new, user-friendly management console, and how to use SQL Server’s tools for configuring, tuning and monitoring database activity. This course also covers critical topics like integration services, implementing security, disaster prevention and recovery, failover clustering, database mirroring and more.

Course Objectives:

Upon successful completion of this course, students will be able to:

Install and configure Microsoft SQL Server 2005.
Understand basic database design principles and how to implement them in SQL Server.
Understand 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.
Examine new RANKing functions, Common Table Expressions, and learn techniques for dealing with complex queries.
Explore advanced techniques for working with complex data structures.
Use source control with SQL Server Management Studio.
Use new XML capabilities in SQL Server 2005.

Course Audience:

This course is designed for students who will be responsible for developing databases using SQL Server 2005.

Course Prerequisites:

This course assumes that students have working experience with SQL Server 2000 or 2005. No particular programming experience is required, but the course is taught from a developer perspective.

Course Syllabus:

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

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

Implementing SQL Server Partitions

Overview of Table-based Partitioning
Creating Partitioned Tables
Querying Partitions
Managing Partitions

Complex Querying

Working with NULL Values
Ranking Grouped Data
Writing Correlated Subqueries
Using Common Table Expressions

Advanced Techniques

Complex Data and Structures
Writing More Efficient Queries
Working with Complex Queries
Maintaining Query Files

Working with XML

The XML Data Type
XML Schema Collections
Querying XML
Best Practices