Course 2779 Implementing a Microsoft SQL Server 2005 Database
Course Length
5.0 day(s)Prerequisites
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 Objectives
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.
Course Topics
Module 1: Creating Databases and Database Files
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
Module 2: Creating Data Types and 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
Module 3: Using XML
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
Module 4: Creating and Tuning Indexes
Lessons
Planning Indexes
Creating Indexes
Optimizing Indexes
Creating XML Indexes
Lab 4: Creating and Tuning Indexes
Creating Indexes
Tuning Indexes
Creating XML Indexes
Module 5: Implementing Data Integrity by Using Constraints
Lessons
Data Integrity Overview
Implementing Constraints
Lab 5: Implementing Data Integrity by Using Constraints
Creating Constraints
Disabling Constraints
Module 6: Implementing Data Integrity by Using 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
Module 7: Implementing 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
Module 8: Implementing Stored Procedures
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
Module 9: Implementing Functions
Lessons
Creating and Using Functions
Working with Functions
Controlling Execution Context
Lab 9: Implementing Functions
Creating Functions
Controlling Execution Context
Module 10: Implementing Managed Code in the Database
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
Module 11: Managing Transactions and Locks
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
Module 12: Using 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
Module 13: Using Notification Services (Optional)
Lessons
Introduction to Notification Services
Developing Notification Services Solutions
