New Horizons Computer Learning Center
410-597-9722

6232A: Implementing a Microsoft SQL Server 2008 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 2008 database. The course focuses on teaching individuals how to use SQL Server 2008 product features and tools related to implementing a database.

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

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 2008
• Plan, create, and optimize indexes
• Implement data integrity in Microsoft SQL Server 2008 databases by using constraints
• Implement data integrity in Microsoft SQL Server 2008 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

Course Content

Lesson 1: Creating Databases and Database Files

• Creating Databases
• Creating Filegroups
• Creating Schemas
• Creating Database Snapshots
• Lab: Creating Databases and Database Files
• Lab Exercise 1: Creating a Database
• Lab Exercise 2: Creating Schemas
• Lab Exercise 3: Creating a Database Snapshot

Lesson 2: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables
• Lab: Creating Data Types and Tables
• Lab Exercise 1: Creating Data Types
• Lab Exercise 2: Using New Date and Time Data Types
• Lab Exercise 3: Creating Tables
• Lab Exercise 4: Creating Partitioned Tables

Lesson 3: Creating and Tuning Indexes

• Planning Indexes
• Creating Indexes
• Optimizing Indexes
• Lab: Creating and Tuning Indexes
• Lab Exercise 1: Creating Indexes
• Lab Exercise 2: Tuning Indexes

Lesson 4: Implementing Data Integrity by Using Constraints and Triggers

• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers
• Lab: Implementing Data Integrity by Using Constraints and Triggers
• Lab Exercise 1: Creating Constraints
• Lab Exercise 2: Disabling Constraints
• Lab Exercise 3: Creating Triggers

Lesson 5: Using XML

• Using the XML Data Type
• Retrieving XML by Using FOR XML
• Shredding XML by Using OPENXML
• Introducing XQuery
• Creating XML Indexes
• Implementing XML Schemas
• Lab: Using XML
• Lab Exercise 1: Mapping Relational Data and XML
• Lab Exercise 2: Storing XML Natively in the Database
• Lab Exercise 3: Using XQuery with XML Methods
• Lab Exercise 4: Create XML Indexes
• Lab Exercise 5: Implementing XML Schemas

Lesson 6: Implementing Views

• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views
• Lab: Implementing Views
• Lab Exercise 1: Creating Views
• Lab Exercise 2: Creating Indexed Views
• Lab Exercise 3: Creating Partitioned Views

Lesson 7: Implementing Stored Procedures

• Implementing Stored Procedures
• Creating Parameterized Stored Procedures
• Working With Execution Plans
• Handling Errors
• Lab: Implementing Stored Procedures
• Lab Exercise 1: Creating Stored Procedures
• Lab Exercise 2: Working with Execution Plans

Lesson 8: Implementing Functions

• Creating and Using Functions
• Working with Functions
• Controlling Execution Context
• Lab: Implementing Functions
• Lab Exercise 1: Creating Functions
• Lab Exercise 2: Controlling Execution Context

Lesson 9: Implementing Managed Code in the Database

• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects
• Lab: Implementing Managed Code in the Database
• Lab Exercise 1: Importing an Assembly
• Lab Exercise 2: Creating Managed Database Objects

Lesson 10: Managing Transactions and Locks

• Overview of Transactions and Locks
• Managing Transactions
• Understanding SQL Server Locking Architecture
• Managing Locks
• Lab: Managing Transactions and Locks
• Lab Exercise 1: Using Transactions
• Lab Exercise 2: Managing Locks
• Lab Exercise 3: Using Partition Locking

Lesson 11: Using Service Broker

• Service Broker Overview
• Creating Service Broker Objects
• Sending and Receiving Messages
• Lab: Using Service Broker
• Lab Exercise 1: Creating Service Broker Objects
• Lab Exercise 2: Implementing the Initiating Service
• Lab Exercise 3: Implementing the Target Service

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