Implementing a Microsoft SQL Server 2005 database (MOC 2779)

Course overview

This 3-day instructor-led course provides students with product knowledge and skills needed 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

• 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

Audience

• This course is intended for IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing a database

Next steps

• No Microsoft Certified Professional exams are associated with this course currently

Benefits to you

After completing this course, students will be able to:

• Create databases and database
• Create data types and tables
• Use XML in SQL Server 2005
• Create and tune indexes
• Implement data integrity
• Create views
• Create stored procedures and functions
• Implement managed code in the database
• Use Service Broker to build a messaging-based solution

Course outline

Module 1: Creating Databases and Database Files

• Creating databases
• Creating filegroups
• Creating schemas
• Creating database snapshots

Module 2: Creating Data Types and Tables

• Creating data types
• Creating tables
• Creating partitioned tables

Module 3: Using XML

• Retrieving XML with FOR XML
• Shredding XML with OPENXML
• Using the XML data type

Module 4: Creating and Tuning Indexes

• Planning indexes
• Creating indexes
• Optimizing indexes
• Creating XML indexes

Module 5: Implementing Data Integrity

• Data integrity overview
• Implementing constraints
• Implementing triggers
• Implementing XML schemas

Module 6: Creating Views

• Introduction to views
• Creating and managing views
• Optimizing performance with views

Module 7: Creating Stored Procedures and Functions

• Implementing stored procedures
• Creating parameterized stored procedures
• Creating functions
• Handling errors
• Controlling execution context

Module 8: Implementing Managed Code in the Database

• Introduction to the SQL server common language runtime
• Importing and configuring assemblies
• Creating managed database objects

Module 9: Using Service Broker

• Service broker overview
• Creating service broker objects
• Sending and receiving messages