Implementing a Microsoft SQL Server 2005 Database

Course Syllabus

Implementing a Microsoft SQL Server 2005

Database

Elements of this syllabus are subject to change.

This three-day instructor-led course provides students with product knowledge and skills needed 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.

Audience

This course is intended for IT

Professionals wanting to become skilled on SQL Server 2005 product features and technologies for 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, triggers, and XML schemas.

§ Implement views.

§ Implement stored procedures and functions.

§ Implement managed code in the database.

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

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 Transact-SQL.

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 a Database

§ Creating a Database.

§ Creating Schemas.After completing this module, students will be able to:

§ Create databases.

§ Create filegroups.

§ Create schemas.

§ Create database snapshots.

Module 2: Creating Data Types and TablesThis 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 data types.

§ Create tables.

§ Create partitioned tables.

Module 3: Using XMLThis 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 FORXML.

§ Shredding XML by Using OPENXML.

§ Using the xml Data Type.

Lab 3: Working with XML

§ Mapping Relational Data andXML.

§ Storing XML Natively in The Database.

After completing this module, students will be able to:

§ Retrieve XML with FOR XML.

§ Shred XML with OPENXML.

§ Use the xml data type.

§ Use the methods of 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 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

This module explains how to implement constraints, triggers, and XML schemas.

Lessons

§ Data Integrity Overview.

§ Implementing Constraints.

§ Implementing Triggers.

§ Implementing XML Schemas.

Lab 5: Implementing Data Integrity

§ Creating Constraints.

§ Creating Triggers.

§ Implementing XML Schemas.

After completing this module, students will be able to:

§ Describe types of data integrity and options for enforcing it.

§ Implement constraints.

§ Implement triggers.

§ Implement XML schemas.

Module 6: Implementing Views

This module explains how to create views.

Lessons

§ Introduction to Views.

§ Creating and Managing Views.

§ Optimizing Performance by Using Views.

Lab 6: Creating 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.

§ Design views for performance.

Module 7: Implementing Stored Procedures and

Functions

This module explains how to create stored procedures and functions.

Lessons

§ Implementing StoredProcedures.

§ Creating Parameterized StoredProcedures.

§ Creating Functions.

§ Handling Errors.

§ Controlling Execution

Context.

Lab 7: Creating Stored Procedures and Functions

§ Creating Stored Procedures.

§ Creating Functions.After completing this module, students will be able to:

§ Implement stored procedures.

§ Create parameterized stored procedures.

§ Handle errors in a stored procedure.

§ Implement Scalar Functions.

§ Create Table Valued Functions.

§ Control Execution Context.

Module 8: Implementing Managed Code in the Database

This module explains how to implement managed database objects.

Lessons

§ Introduction to the SQLServer Common Language Runtime.

§ Importing and ConfiguringAssemblies.

§ Creating Managed Database Objects.

Lab 8: 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 9: Using Service Broker

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

Lessons

§ Service Broker Overview.

§ Creating Service BrokerObjects.

§ Sending and Receiving Messages.

Lab 9: Using Service Broker

§ Creating Service BrokerObjects.

§ Implementing the InitiatingService.

§ 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.