Course Overview
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.
Skills Gained
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 2005 Transact-SQL.
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 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 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 data types.
- Create 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.
- Using the xml Data Type.
Lab 3: Working with XML
- Mapping Relational Data and XML.
- 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 Stored Procedures.
- Creating Parameterized Stored Procedures.
- 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 SQL Server Common Language Runtime.
- Importing and Configuring Assemblies.
- 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 Broker Objects.
- Sending and Receiving Messages.
Lab 9: Using Service Broker
- 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.
