Implementing a Microsoft SQL Server 2005 Database

Code: 2779
Course duration: 5 days
Price:

Implementing a Microsoft SQL Server 2005 Database

Course 2779b: Five Days; Instructor-Led

About this Course

Elements of this syllabus are subject to change.

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.

Audience Profile

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

Prerequisites

Before attending this course, students must have:

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

In addition, it is recommended, but not required, that students have completed:

  • Course 2778: Writing Queries Using Microsoft SQL Server 2005 Transact-SQL.
  • Course 2780: Maintaining a Microsoft SQL Server 2005 Database.

At Course Completion

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

Module 1: Creating Databases and Database Files
This module explains how to create databases, filegroups, schemas, and database snapshots.
Lessons

  • Creating Databases
  • Creating Filegroups
  • Creating Schemas
  • Creating Database Snapshots

Lab : 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 Tables
This module explains how to create data types and tables. It also describes how to create partitioned tables.
Lessons

  • Creating Data Types
  • Creating Tables
  • Creating Partitioned Tables

Lab : 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: Using XML
This module explains how to use the FOR XML clause and the OPENXML function. It also describes how to use the xml data type and its methods.
Lessons

  • Retrieving XML by Using FOR XML
  • Shredding XML by Using OPENXML
  • Introducing XQuery
  • Using the xml Data Type

Lab : Using XML

  • Mapping Relational Data and XML
  • Storing XML Natively in the Database
  • Using XQuery with xml Methods

After completing this module, students will be able to:

  • Retrieve XML by using the FOR XML clause.
  • Shred XML by using the OPENXML function.
  • Use XQuery expressions.
  • Use the xml data type.

Module 4: Creating and Tuning Indexes
This module explains how to plan, create, and optimize indexes. It also describes how to create XML indexes.
Lessons

  • Planning Indexes
  • Creating Indexes
  • Optimizing Indexes
  • Creating XML Indexes

Lab : Creating and Tuning Indexes

  • Creating Indexes
  • Tuning Indexes
  • Creating XML Indexes

After completing this module, students will be able to:

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

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

  • Data Integrity Overview
  • Implementing Constraints

Lab : 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 in SQL Server 2005.
  • Implement data integrity in SQL Server 2005 databases by using constraints.

Module 6: Implementing Data Integrity by Using Triggers and XML Schemas
This module explains how to implement triggers and XML schemas.
Lessons

  • Implementing Triggers
  • Implementing XML Schemas

Lab : Implementing Data Integrity by Using Triggers and XML Schemas

  • Creating Triggers
  • Implementing XML Schemas

After completing this module, students will be able to:

  • Implement data integrity in SQL Server 2005 databases by using triggers.
  • Implement data integrity in SQL Server 2005 databases by using XML schemas.

Module 7: Implementing Views
This module explains how to create views.
Lessons

  • Introduction to Views
  • Creating and Managing Views
  • Optimizing Performance by Using Views

Lab : 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 Procedures
This module explains how to create stored procedures and functions. It also describes execution plans, plan caching, and query compilation.
Lessons

  • Implementing Stored Procedures
  • Creating Parameterized Stored Procedures
  • Working With Execution Plans
  • Handling Errors

Lab : 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 Functions
This module explains how to create functions. It also describes how to control the execution context.
Lessons

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

Lab : 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 Database
This module explains how to implement managed database objects.
Lessons

  • Introduction to the SQL Server Common Language Runtime
  • Importing and Configuring Assemblies
  • Creating Managed Database Objects

Lab : 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 Locks
This module explains how to use transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your applications.
Lessons

  • Overview of Transactions and Locks
  • Managing Transactions
  • Understanding SQL Server Locking Architecture
  • Managing Locks

Lab : Managing Transactions and Locks

  • Using Transactions
  • Managing Locks

After completing this module, students will be able to:

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

Module 12: Using Service Broker
This module explains how to build a messaging-based solution with Service Broker.
Lessons

  • Service Broker Overview
  • Creating Service Broker Objects
  • Sending and Receiving Messages

Lab : Using Service Broker (Optional)

  • Creating Service Broker Objects
  • Creating Service Broker Objects
  • 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.
Lessons

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

Guaranteed to Run

2019-08-28 09:00 to 2019-08-30 17:00
Attend Online
2019-08-14 09:00 to 2019-08-16 17:00
Attend Online
2019-08-09
09:00 to 17:00
Fort Lauderdale, Florida (Instructor-Led)
2019-08-07 09:00 to 2019-08-09 17:00
Attend Online
2019-08-02
09:00 to 17:00
Palm Beach County Florida (Instructor-Led)
2019-07-25 09:00 to 2019-07-26 17:00
NYC-23rd Street (Virtual Instructor-Led)
2019-07-25 09:00 to 2019-07-26 17:00
Attend Online
2019-07-24 09:00 to 2019-07-26 17:00
Washington, D.C. (Virtual Instructor-Led)

Course Reviews

No reviews found for this course.

Be the first to write a review