New Horizons Computer Learning Center
410-597-9722

2779: Designing/Implementing a Microsoft SQL Server 2005 Database

Course Length: 5 Days

Overview:
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.

Prerequisites:
Writing Queries Using Microsoft SQL Server 2005 Transact-SQL
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 Objectives:
Upon successful completion of 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 Content

Lesson 1: Creating Databases and Database Files

• 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

Lesson 2: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables
• Lab 2: Creating Data Types and Tables
• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

Lesson 3: Using XML

• 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

Lesson 4: Creating and Tuning Indexes

• Planning Indexes
• Creating Indexes
• Optimizing Indexes
• Creating XML Indexes
• Lab 4: Creating and Tuning Indexes
• Creating Indexes
• Tuning Indexes
• Creating XML Indexes

Lesson 5: Implementing Data Integrity by Using Constraints

• Data Integrity Overview
• Implementing Constraints
• Lab 5: Implementing Data Integrity by Using Constraints
• Creating Constraints
• Disabling Constraints

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

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

Lesson 7: Implementing Views

• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views
• Lab 7: Implementing Views
• Creating Views
• Creating Indexed Views
• Creating Partitioned Views

Lesson 8: Implementing Stored Procedures

• 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

Lesson 9: Implementing Functions

• Creating and Using Functions
• Working with Functions
• Controlling Execution Context
• Lab 9: Implementing Functions
• Creating Functions
• Controlling Execution Context

Lesson 10: Implementing Managed Code in the Database

• 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

Lesson 11: Managing Transactions and Locks

• Overview of Transactions and Locks
• Managing Transactions
• Understanding SQL Server Locking Architecture
• Managing Locks
• Lab 11: Managing Transactions and Locks
• Using Transactions
• Managing Locks

Lesson 12: Using Service Broker

• 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

Lesson 13: Using Notification Services (Optional)

• Introduction to Notification Services
• Developing Notification Services Solutions

6940 Tudsbury Road, Baltimore, MD 21244
P: 410-597-9722
www.nhbaltimore.com