Microsoft SQL Server 2008: Implementing a Microsoft SQL Server 2008 Database

Course Overview

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

Some Microsoft courses can also be delivered in-house or at a venue of your choice, please click here to send an enquiry and find out more

Related Courses

• Microsoft SQL Server 2008 New Features: Hands-On
• Microsoft SQL Server 2008 Comprehensive Introduction: Hands-On
• Microsoft SQL Server 2008: Database Administration
• Microsoft SQL Server 2008 Training Course UK: Comprehensive DBA & Programming Upgrade
• Microsoft SQL Server 2008 Analysis & Integration Services (SSAS & SSIS) - (Business Intelligence, BI, Data Warehouse, Mining)
• Microsoft SQL Server 2008 Reporting Services (SSRS) - (Business Intelligence, BI)
• Microsoft SQL Server 2008: Business Intelligence with SQL Server 2008 - (Microsoft, BI, SSRS, SSAS, SSIS, Data Warehouse, Mining)
• Microsoft SQL Server 2008: Maintaining a Microsoft SQL Server 2008 Database
• Microsoft SQL Server 2008 Reporting Services: Hands-On
• Microsoft SQL Server 2008: Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services

Audience

This course provides students with the knowledge and skills to implement a Microsoft SQL Server 2008 database and focuses on teaching individuals how to use SQL Server 2008 product features and tools related to implementing a database. It is intended for IT Professionals who administer and maintain SQL Server databases.

Examinations

• Microsoft® >>

• MCITP Microsoft Certified IT Professional >>

• Microsoft SQL Server >>

• Exam 70-433 MCITP: Database Developer 2008 >>

Prerequisites

Before attending this course, students must have the following pre-requisites:

• 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 Outline

Module 1: Creating Databases and Database Files

• The students will learn one of the most fundamental tasks that a database developer must perform, the creation of a database and its major components.
• Creating Databases
• Creating Filegroups
• Creating Schemas
• Creating Database Snapshots
• Lab: Creating Databases and Database Files

Skills

• Create databases.
• Create filegroups.
• Create schemas.
• Create database snapshots.

Module 2: Creating Data Types and Tables

• The students will learn about the system-supplied data types in SQL Server 2008.
• Creating Data Types
• Creating Tables
• Creating Partitioned Tables
• Lab: Creating Data Types and Tables

Skills

• Create new data types.
• Create new tables.
• Create partitioned tables.

Module 3: Creating and Tuning Indexes

• The students will learn how to plan, create, and optimize indexes to attain optimal performance benefits.
• Planning Indexes
• Creating Indexes
• Optimizing Indexes
• Lab: Creating and Tuning Indexes

Skills

• Plan indexes.
• Create indexes.
• Optimize indexes.

Module 4: Implementing Data Integrity by Using Constraints and Triggers

• The students will learn about implementing data integrity in SQL Server 2008 by using constraints and by using triggers.
• Data Integrity Overview
• Implementing Constraints
• Implementing Triggers
• Lab: Implementing Data Integrity by Using Constraints and Triggers

Skills

• Describe the options for enforcing data integrity in SQL Server 2008.
• Implement data integrity in SQL Server 2008 databases by using constraints.
• Implement data integrity in SQL Server 2008 databases by using triggers.

Module 5: Using XML

• The students will learn how to work with XML, including use of the FOR XML clause, the OPENXML function, XQuery expressions, and the xml native data type.
• 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

Skills

• Use the xml data type.
• Retrieve XML by using the FOR XML clause.
• Shred XML by using the OPENXML function.
• Use XQuery expressions.
• Create XML indexes.
• Implement data integrity in SQL Server 2008 databases by using XML schemas.

Module 6: Implementing Views

• The students will be introduced to the different types of views available in Microsoft SQL Server 2008 which provide a convenient way to access data through a query.
• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views
• Lab: Implementing Views

Skills

• Describe the purpose of views.
• Create and manage views.
• Explain how to optimize query performance by using views.

Module 7: Implementing Stored Procedures

• The students will learn the design and implementation of stored procedures to enforce business rules or data consistency, or to modify and maintain existing stored procedures written by other developers.
• Implementing Stored Procedures
• Creating Parameterized Stored Procedures
• Working With Execution Plans
• Handling Errors
• Lab: Implementing Stored Procedures

Skills

• Implement stored procedures.
• Create parameterized stored procedures.
• Work with execution plans.
• Handle errors in stored procedures.

Module 8: Implementing Functions

• The students will learn the design and implementation of user-defined functions that enforce business rules or data consistency, or to modify and maintain existing functions written by other developers.
• Creating and Using Functions
• Working with Functions
• Controlling Execution Context
• Lab: Implementing Functions

Skills

• Create and use functions.
• Work with functions.
• Control execution context.

Module 9: Implementing Managed Code in the Database

• The students will learn to use managed code to implement database objects, such as stored procedures, user-defined data types, user-defined functions, and triggers.
• Introduction to the SQL Server Common Language Runtime
• Importing and Configuring Assemblies
• Creating Managed Database Objects
• Lab: Implementing Managed Code in the Database

Skills

• Identify appropriate scenarios for managed code in the database.
• Import and configure assemblies.
• Create managed database objects.

Module 10: Managing Transactions and Locks

• The students will learn to use transactions and SQL Server locking mechanisms to meet the performance and data integrity requirements of their applications.
• Overview of Transactions and Locks
• Managing Transactions
• Understanding SQL Server Locking Architecture
• Managing Locks
• Lab: Managing Transactions and Locks

Skills

• Describe how SQL Server 2008 transactions use locks.
• Execute and cancel a transaction.
• Describe concurrency issues and SQL Server 2008 locking mechanisms.
• Manage locks.

Module 11: Using Service Broker

• The students will learn to use Service Broker to create secure, reliable, and scalable applications.
• Service Broker Overview
• Creating Service Broker Objects
• Sending and Receiving Messages
• Lab: Using Service Broker

Skills

• Describe Service Broker functionality and architecture.
• Create Service Broker objects.
• Send and receive Service Broker messages.