Implementing a Microsoft SQL Server 2005 Database

Course contentModule 1: Creating Databases and Database FilesThis module explains how to create databases, filegroups, schemas, and database snapshots.

• 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

After completing this module, students will be able to:

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

Module 2: Creating Data Types and TablesThis module explains how to create data types and tables. It also describes how to create partitioned tables.

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

Lab 2: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

After completing this module, students will be able to:

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

Module 3: UsingXMLThis module explains how to use theFORXMLclause and theOPENXMLfunction. It also describes how to use the xml data type and its methods.

• RetrievingXMLby UsingFORXML
• ShreddingXMLby UsingOPENXML
• Introducing XQuery
• Using the xml Data Type

• Lab 3: UsingXML*

• Mapping Relational Data andXML
• StoringXMLNatively in the Database
• Using XQuery with xml Methods

After completing this module, students will be able to:

• RetrieveXMLby using theFORXMLclause
• ShredXMLby using theOPENXMLfunction
• Use XQuery expressions
• Use the xml data type

Module 4: Creating and Tuning IndexesThis module explains how to plan, create, and optimize indexes. It also describes how to createXMLindexes.

• Planning Indexes
• Creating Indexes
• Optimizing Indexes
• CreatingXMLIndexes

Lab 4: Creating and Tuning Indexes

• Creating Indexes
• Tuning Indexes
• CreatingXMLIndexes

After completing this module, students will be able to:

• Plan indexes
• Create indexes
• Optimize indexes
• CreateXMLindexes

Module 5: Implementing Data Integrity by Using ConstraintsThis module explains how to implement constraints and provides an overview of data integrity.

• Data Integrity Overview
• Implementing Constraints

Lab 5: Implementing Data Integrity by Using Constraints

• Creating Constraints
• Disabling Constraints

After completing this module, students will be able to:

• Describe the options for enforcing data integrity inSQLServer 2005
• Implement data integrity inSQLServer 2005 databases by using constraints

Module 6: Implementing Data Integrity by Using Triggers andXMLSchemasThis module explains how to implement triggers andXMLschemas.

• Implementing Triggers
• ImplementingXMLSchemas

Lab 6: Implementing Data Integrity by Using Triggers andXMLSchemas

• Creating Triggers
• ImplementingXMLSchemas

After completing this module, students will be able to:

• Implement data integrity inSQLServer 2005 databases by using triggers
• Implement data integrity inSQLServer 2005 databases by usingXMLschemas

Module 7: Implementing ViewsThis module explains how to create 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

After completing this module, students will be able to:

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

Module 8: Implementing Stored ProceduresThis module explains how to create stored procedures and functions. It also describes execution plans, plan caching, and query compilation.

• 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

After completing this module, students will be able to:

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

Module 9: Implementing FunctionsThis module explains how to create functions. It also describes how to control the execution context.

• Creating and Using Functions
• Working with Functions
• Controlling Execution Context

Lab 9: Implementing Functions

• Creating Functions
• Controlling Execution Context

After completing this module, students will be able to:

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

Module 10: Implementing Managed Code in the DatabaseThis module explains how to implement managed database objects.

• Introduction to theSQLServer 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

After completing this module, students will be able to:

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

Module 11: Managing Transactions and LocksThis module explains how to use transactions and theSQLServer locking mechanisms to meet the performance and data integrity requirements of your applications.

• Overview of Transactions and Locks
• Managing Transactions
• UnderstandingSQLServer Locking Architecture
• Managing Locks

Lab 11: Managing Transactions and Locks

• Using Transactions
• Managing Locks

After completing this module, students will be able to:

• Describe howSQLServer 2005 transactions use locks
• Execute and cancel a transaction
• Describe concurrency issues andSQLServer 2005 locking mechanisms
• Manage locks

Module 12: Using Service BrokerThis module explains how to build a messaging-based solution with 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

After completing this module, students will be able to:

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

Module 13: Using Notification Services (Optional)This module explains how to develop applications that generate and send timely messages to subscribers.

• Introduction to Notification Services
• Developing Notification Services Solutions

After completing this module, students will be able to:

• Describe how Notification Services operates.
• Develop a Notification Services application.