Course Outline

NOTE: Our customized course now includes a module/lab on SQL 2005 T-SQL Enhancements

Module 1: Introduction to Transact-SQL

The following topics are covered in this module:

•	The Transact-SQL Programming Language
•	Types of Transact-SQL Statements
•	Transact-SQL Syntax Elements
•	Using SQL Server Books Online

After completing this module, you will be able to:

•	Differentiate between Transact-SQL and ANSI-SQL.
•	Describe the basic types of Transact-SQL.
•	Describe the syntax elements of Transact-SQL.

Module 2: Using Transact-SQL Querying Tools

The following topics are covered in this module:

•	SQL Query Analyzer
•	Using the Object Browser Tool in SQL Query Analyzer
•	Using the osql Utility
•	Executing Transact-SQL Statements
•	Creating and Executing Transact-SQL Scripts

After completing this module, you will be able to:

•	Describe the basic functions of SQL Query Analyzer.
•	Describe how to use the Object Browser tool in SQL Query Analyzer.
•	Describe how to use the templates in SQL Query Analyzer.
•	Describe how to use the osql command-line utility.
•	Execute Transact-SQL statements in various ways.

Module 3: Retrieving Data

The following topics are covered in this module:

•	Retrieving Data by Using the SELECT Statement
•	Filtering Data
•	Formatting Result Sets
•	How Queries Are Processed
•	Performance Considerations
•	Retrieving Data and Manipulating Result Sets

After completing this module, you will be able to:

•	Retrieve data from tables by using the SELECT statement.
•	Filter data by using different search conditions to use with the WHERE clause.
•	Format result sets.
•	Describe how queries are processed.
•	Describe performance considerations that affect retrieving data.

Module 4: Grouping and Summarizing Data

The following topics are covered in this module:

•	Listing the TOP n Values
•	Using Aggregate Functions
•	GROUP BY Fundamentals
•	Generating Aggregate Values Within Result Sets
•	Using the COMPUTE and COMPUTE BY Clauses
•	Grouping and Summarizing Data

After completing this module, you will be able to:

•	Use the TOP n keyword to retrieve a list of the specified top values in a table.
•	Generate a single summary value by using aggregate functions.
•	Organize summary data for a column by using aggregate functions with the GROUP BY and HAVING clauses.
•	Generate summary data for a table by using aggregate functions with the GROUP BY clause and the ROLLUP or CUBE operator.
•	Generate control-break reports by using the COMPUTE and COMPUTE BY clauses.

Module 5: Joining Multiple Tables

The following topics are covered in this module:

•	Using Aliases for Table Names
•	Combining Data from Multiple Tables
•	Combining Multiple Result Sets
•	Querying Multiple Tables

After completing this module, you will be able to:

•	Use aliases for table names.
•	Combine data from two or more tables by using joins.
•	Combine multiple result sets into one result set by using the UNION operator.

Module 6: Working with Subqueries

The following topics are covered in this module:

•	Introduction to Subqueries
•	Using a Subquery as a Derived Table
•	Using a Subquery as an Expression
•	Using a Subquery to Correlate Data
•	Using the EXISTS and NOT EXISTS Clauses
•	Working with Subqueries

After completing this module, you will be able to:

•	Describe when and how to use a subquery.
•	Use subqueries to break down and perform complex queries.

Module 7: Modifying Data

The following topics are covered in this module:

•	Using Transactions
•	Inserting Data
•	Deleting Data
•	Updating Data
•	Performance Considerations
•	Modifying Data

After completing this module, you will be able to:

•	Describe how transactions work.
•	Write INSERT, DELETE, and UPDATE statements to modify data in tables.
•	Describe performance considerations related to modifying data.

Module 8: Querying Full-Text Indexes

The following topics are covered in this module:

•	Introduction to Microsoft Search Service
•	Microsoft Search Service Components
•	Getting Information About Full-Text Indexes
•	Writing Full-Text Queries
•	Querying Full-Text Indexes

After completing this module, you will be able to:

•	Describe Microsoft Search service function and components.
•	Write full-text queries.
•	Get information about full-text indexes.

Module 9: Introduction to Programming Objects

The following topics are covered in this module:

•	Displaying the Text of a Programming Object
•	Introduction to Views
•	Advantages of Views
•	Creating Views
•	Introduction to Stored Procedures
•	Introduction to Triggers
•	Introduction to User-defined Functions
•	Working with Views

After completing this module, you will be able to:

•	Display the text of a programming object.
•	Describe the concepts of views.
•	List the advantages of views.
•	Describe stored procedures.
•	Describe triggers.
•	Describe user defined functions.

Close Move

Introduction

This five-day instructor-led course provides students with the knowledge and skills to implement a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to implementing a database.

At Course Completion

After completing this course, students will be able to:

•
Create databases and database files.

•
Create data types and tables.

•
Use XML-related features in Microsoft SQL Server 2005.

•
Plan, create, and optimize indexes.

•
Implement data integrity in Microsoft SQL Server 2005 databases by using constraints.

•
Implement data integrity in Microsoft SQL Server 2005 by using triggers.

•
Implement views.

•
Implement stored procedures.

•
Implement functions.

•
Implement managed code in the database.

•
Manage transactions and locks.

•
Use Service Broker to build a messaging-based solution.

•
Use Notification Services to generate and send notifications.

Prerequisites

Before attending this course, students must have:

•
Basic knowledge of the Microsoft Windows operating system and its core functionality.

•
Working knowledge of Transact-SQL.

•
Working knowledge of relational databases.

•
Some experience with database design.

In addition, it is recommended, but not required, that students have completed:

•
Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL.

•
Course 2780: Maintaining a Microsoft SQL Server 2005 Database.

Course Outline

Module 1: Creating Databases and Database Files

This module explains how to create databases, filegroups, schemas, and database snapshots.

Lessons

•
Creating Databases

•
Creating Filegroups

•
Creating Schemas

•
Creating Database Snapshots

Lab 1: Creating Databases and Database Files

•
Creating a Database

•
Creating Schemas

•
Creating a Database Snapshot

After completing this module, students will be able to:

•
Create databases.

•
Create filegroups.

•
Create schemas.

•
Create database snapshots.

Module 2: Creating Data Types and Tables

This module explains how to create data types and tables. It also describes how to create partitioned tables.

Lessons

•
Creating Data Types

•
Creating Tables

•
Creating Partitioned Tables

Lab 2: Creating Data Types and Tables

•
Creating Data Types

•
Creating Tables

•
Creating Partitioned Tables

After completing this module, students will be able to:

•
Create new data types.

•
Create new tables.

•
Create partitioned tables.

Module 3: Using XML

This module explains how to use the FOR XML clause and the OPENXML function. It also describes how to use the xml data type and its methods.

Lessons

•
Retrieving XML by Using FOR XML

•
Shredding XML by Using OPENXML

•
Introducing XQuery

•
Using the xml Data Type

Lab 3: Using XML

•
Mapping Relational Data and XML

•
Storing XML Natively in the Database

•
Using XQuery with xml Methods

After completing this module, students will be able to:

•
Retrieve XML by using the FOR XML clause.

•
Shred XML by using the OPENXML function.

•
Use XQuery expressions.

•
Use the xml data type.

Module 4: Creating and Tuning Indexes

This module explains how to plan, create, and optimize indexes. It also describes how to create XML indexes.

Lessons

•
Planning Indexes

•
Creating Indexes

•
Optimizing Indexes

•
Creating XML Indexes

Lab 4: Creating and Tuning Indexes

•
Creating Indexes

•
Tuning Indexes

•
Creating XML Indexes

After completing this module, students will be able to:

•
Plan indexes.

•
Create indexes.

•
Optimize indexes.

•
Create XML indexes.

Module 5: Implementing Data Integrity by Using Constraints

This module explains how to implement constraints and provides an overview of data integrity.

Lessons

•
Data Integrity Overview

•
Implementing Constraints

Lab 5: Implementing Data Integrity by Using Constraints

•
Creating Constraints

•
Disabling Constraints

After completing this module, students will be able to:

•
Describe the options for enforcing data integrity in SQL Server 2005.

•
Implement data integrity in SQL Server 2005 databases by using constraints.

Module 6: Implementing Data Integrity by Using Triggers and XML Schemas

This module explains how to implement triggers and XML schemas.

Lessons

•
Implementing Triggers

•
Implementing XML Schemas

Lab 6: Implementing Data Integrity by Using Triggers and XML Schemas

•
Creating Triggers

•
Implementing XML Schemas

After completing this module, students will be able to:

•
Implement data integrity in SQL Server 2005 databases by using triggers.

•
Implement data integrity in SQL Server 2005 databases by using XML schemas.

Module 7: Implementing Views

This module explains how to create views.

Lessons

•
Introduction to Views

•
Creating and Managing Views

•
Optimizing Performance by Using Views

Lab 7: Implementing Views

•
Creating Views

•
Creating Indexed Views

•
Creating Partitioned Views

After completing this module, students will be able to:

•
Describe the purpose of views.

•
Create and manage views.

•
Explain how to optimize query performance by using views.

Module 8: Implementing Stored Procedures

This module explains how to create stored procedures and functions. It also describes execution plans, plan caching, and query compilation.

Lessons

•
Implementing Stored Procedures

•
Creating Parameterized Stored Procedures

•
Working With Execution Plans

•
Handling Errors

Lab 8: Implementing Stored Procedures

•
Creating Stored Procedures

•
Working With Execution Plans

After completing this module, students will be able to:

•
Implement stored procedures.

•
Create parameterized stored procedures.

•
Work with execution plans.

•
Handle errors in stored procedures.

Module 9: Implementing Functions

This module explains how to create functions. It also describes how to control the execution context.

Lessons

•
Creating and Using Functions

•
Working with Functions

•
Controlling Execution Context

Lab 9: Implementing Functions

•
Creating Functions

•
Controlling Execution Context

After completing this module, students will be able to:

•
Create and use functions.

•
Work with functions.

•
Control execution context.

Module 10: Implementing Managed Code in the Database

This module explains how to implement managed database objects.

Lessons

•
Introduction to the SQL Server Common Language Runtime

•
Importing and Configuring Assemblies

•
Creating Managed Database Objects

Lab 10: Implementing Managed Code in the Database

•
Importing an Assembly

•
Creating Managed Database Objects

After completing this module, students will be able to:

•
Identify appropriate scenarios for managed code in the database.

•
Import and configure assemblies.

•
Create managed database objects.

Module 11: Managing Transactions and Locks

This module explains how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your applications.

Lessons

•
Overview of Transactions and Locks

•
Managing Transactions

•
Understanding SQL Server Locking Architecture

•
Managing Locks

Lab 11: Managing Transactions and Locks

•
Using Transactions

•
Managing Locks

After completing this module, students will be able to:

•
Describe how SQL Server 2005 transactions use locks.

•
Execute and cancel a transaction.

•
Describe concurrency issues and SQL Server 2005 locking mechanisms.

•
Manage locks.

Module 12: Using Service Broker

This module explains how to build a messaging-based solution with Service Broker.

Lessons

•
Service Broker Overview

•
Creating Service Broker Objects

•
Sending and Receiving Messages

Lab 12: Using Service Broker (Optional)

•
Creating Service Broker Objects

•
Implementing the Initiating Service

•
Implementing the Target Service

After completing this module, students will be able to:

•
Describe Service Broker functionality and architecture.

•
Create Service Broker objects.

•
Send and receive Service Broker messages.

Module 13: Using Notification Services (Optional)

This module explains how to develop applications that generate and send timely messages to subscribers.

Lessons

•
Introduction to Notification Services

•
Developing Notification Services Solutions

After completing this module, students will be able to:

•
Describe how Notification Services operates.

•
Develop a Notification Services application.

Close Move

Introduction

This five-day instructor-led course provides students with the knowledge and skills to maintain a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to maintaining a database.

At Course Completion

After completing this course, students will be able to:

•
Install and configure SQL Server 2005.

•
Manage database files.

•
Backup and restore databases.

•
Manage security.

•
Monitor SQL Server.

•
Transfer data into and out of SQL Server.

•
Automate administrative tasks.

•
Replicate data between SQL Server instances.

•
Maintain high availability.

Prerequisites

Before attending this course, students must have:

•
Basic knowledge of the Microsoft Windows operating system and its core functionality.

•
Working knowledge of Transact-SQL.

•
Working knowledge of relational databases.

•
Some experience with database design.

Course Outline

Module 1: Installing and Configuring SQL Server 2005

This module explains how to plan for and install SQL Server 2005, how to manage a SQL Server 2005 installation, and how to use the SQL Server 2005 administrative tools.

Lessons

•
Preparing to Install SQL Server

•
Installing SQL Server 2005

•
Managing a SQL Server 2005 Installation

Lab 1: Installing and Configuring SQL Server 2005

•
Performing an Installation

•
Managing SQL Server

After completing this module, students will be able to:

•
Explain how to prepare the hardware and other resources necessary to install SQL Server 2005.

•
Install SQL Server 2005.

•
Manage and configure a SQL Server 2005 installation.

Module 2: Managing Databases and Files

This module explains how to manage databases and files.

Lessons

•
Planning Databases

•
Creating Databases

•
Managing Databases

Lab 2: Managing Databases and Files

•
Creating a Database

•
Monitoring and Managing Filegroup Usage

•
Viewing Database Metadata

After completing this module, students will be able to:

•
Plan how to implement a database that meets an organization's requirements.

•
Create a SQL Server database.

•
Manage a SQL Server database.

Module 3: Disaster Recovery

This module explains how to plan and implement a backup and restore strategy.

Lessons

•
Planning a Backup Strategy

•
Backing Up User Databases

•
Restoring User Databases

•
Performing Online Restore Operations

•
Recovering Data from Database Snapshots

•
System Database and Disaster Recovery

Lab 3: Disaster Recovery

•
Implementing a Backup Strategy

•
Restoring and Recovering a Database

•
Performing Piecemeal Backup and Restore Operations

•
Restoring the master Database

After completing this module, students will be able to:

•
Plan a backup strategy for a database.

•
Back up user databases.

•
Restore user databases from backups.

•
Restore data in a user database while it is online.

•
Recover data for a user database from a database snapshot.

•
Restore and recover systems databases.

Module 4: Managing Security

This module explains how to manage principals, securables, and permissions, and how to implement cryptography in a SQL Server database.

Lessons

•
Overview of SQL Server Security

•
Protecting the Server Scope

•
Protecting the Database Scope

•
Managing Keys and Certificates in SQL Server

Lab 4: Managing Security

•
Creating Logins and Assigning Server-Scope Permissions

•
Creating and Managing Users

•
Using a Certificate to Protect Data

After completing this module, students will be able to:

•
Describe how SQL Server manages security.

•
Protect SQL Server at the server level.

•
Protect SQL Server databases.

•
Use keys and certificates to protect SQL Server objects.

Module 5: Monitoring SQL Server

This module explains how to monitor SQL Server performance and activity.

Lessons

•
Viewing Current Activity

•
Using System Monitor

•
Using SQL Server Profiler

•
Using DDL Triggers

•
Using Event Notifications

Lab 5: Monitoring SQL Server

•
Monitoring SQL Server Performance

•
Tracing SQL Server Activity

•
Implementing DDL Triggers

After completing this module, students will be able to:

•
Examine the current activity in a SQL Server instance.

•
Use System Monitor to obtain performance data about your computer and the instances of SQL Server running on your computer.

•
Use SQL Server Profiler to trace server and database activity.

•
Implement DDL triggers that enable you to audit changes to the structure of database objects.

•
Use event notifications to capture and monitor significant events for a SQL Server instance.

Module 6: Transferring Data

This module explains how to transfer and transform data.

Lessons

•
Overview of Data Transfer

•
Introduction to SQL Server Integration Services

•
Using SQL Server Integration Services

•
Features of SQL Server Integration Services

Lab 6: Transferring Data

•
Creating an SSIS Package

•
Deploying an SSIS Package

•
Using SSIS to Extract Data, Perform Lookups, Sort, and Split Data

After completing this module, students will be able to:

•
Describe the problems surrounding data transfer and the tools that SQL Server 2005 provides to perform data transfer.

•
Describe the purpose of SQL Server Integration Services.

•
Use SQL Server Integration Services to transfer data into a SQL Server database.

•
Describe the features of SQL Server Integration Services.

Module 7: Automating Administrative Tasks

This module explains how to use the SQL Server Agent to automate administrative tasks.

Lessons

•
Automating Administrative Tasks in SQL Server 2005

•
Configuring the SQL Server Agent

•
Creating Jobs and Operators

•
Creating Alerts

•
Managing Multiple Servers</