Implementing a Microsoft SQL Server 2005 Database
Course 2779b: Five Days; Instructor-Led
About this Course
Elements of this syllabus are subject to change.
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.
Audience Profile
This course is intended for IT Professionals who want to become skilled on SQL Server 2005 product features and technologies for implementing a database.
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.
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.
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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : 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 : Using Service Broker (Optional)
- Creating Service Broker Objects
- Creating Service Broker Objects
- 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.