SitesPower Training Center 306 Montana Bldg, Zabeel Rd, Karama
Tel: 335-5549
Email: learn@sitespower.com
Course
2779: Implementing a Microsoft SQL Server 2005 Database
5 day Instructor-Led Course (40 hours)
COURSE USUALLY PROVIDED AS 3 DAY WORKSHOP / BOOTCAMP
Course Overview
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 This course is intended for IT Professionals wanting to become
skilled on SQL Server 2005 product features and technologies for
implementing a 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.
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.
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 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 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 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: 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 3: 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 4: 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 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 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 6: 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 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 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 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 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 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 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 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 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 11: 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 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.
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.
SitesPower
Training Center
Dubai Head Office :
+9714-335-5549 | Sharjah Institute : +9716-575-1315
