SQL Server 2005

Course AD100: Five days; Instructor-Led

Introduction

In this five-day instructor-led 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.

Audience

This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing a database.

Objectives

After completing this workshop, 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.

•         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

Before attending this workshop, students must:

•         Have a solid understanding of relational databases.

•         No particular programming experience is required, but the course is taught from a developer's perspective.

Course Outline

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