2071/2779: Implementing a Microsoft SQL Server 2005 Database &
Transact-SQL
Five days; Instructor-Led
Introduction
This five-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. This course will also
provide students with the technical skills required to write basic
Transact-SQL queries for Microsoft SQL.
Audience
This course is intended for IT
Professionals wanting to become skilled on SQL Server 2005 product features
and technologies for implementing a database and is intended for SQL Server
database administrators, implementers, system engineers, and developers who
are responsible for writing queries.
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, triggers, and
XML schemas.
•
Implement views.
•
Implement stored
procedures and functions.
•
Implement managed code
in the database.
•
Use Service Broker to
build a messaging-based solution.
Prerequisites
Before attending this course, students must have:
•
Basic knowledge of the
Microsoft Windows operating system and its core functionality.
•
Working knowledge of
relational databases.
•
Some experience with
database design.
Next Step
To continue in your technology
training, we recommend that you take the following course after successfully
completing this Microsoft 2071/2779 course:
OR
Course Outline -2071
Introduction
Module
: Introduction to Transact-SQL
Lab A: Using SQL Server
Books Online
Module
: Using Transact-SQL Querying Tools
Lab A: Creating and
Executing Transact-SQL Scripts
Module : Retrieving
Data
Lab A: Retrieving Data
and Manipulating Results Sets
Module
: Grouping and Summarizing Data
Lab A: Grouping and
Summarizing Data
Module
: Joining Multiple Tables
A: Querying Multiple
Tables
Module
: Working with Subqueries
Lab A: Working with
Subqueries
Module
: Modifying Data
Lab A: Modifying Data
Module
: Querying Full-Text Indexes
Lab A: Querying
Full-Text Indexes
Module
: Introduction to Programming Objects
Lab A: Working with
Views
Course Outline - 2779
Module : 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 a Database
•
Creating a Database.
•
Creating Schemas.
After
completing this module, students will be able to:
•
Create databases.
•
Create filegroups.
•
Create schemas.
•
Create database snapshots.
Module : 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 data types.
•
Create tables.
• Create
partitioned tables.
Module : 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.
•
Using
the xml Data Type.
Lab
3: Working with XML
•
Mapping Relational Data and XML.
•
Storing XML Natively in the Database.
After
completing this module, students will be able to:
•
Retrieve XML with FOR XML.
•
Shred
XML with OPENXML.
•
Use
the xml data type.
•
Use
the methods of the xml data type.
Module : 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 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 : Implementing Data Integrity
This
module explains how to implement constraints, triggers, and XML schemas.
Lessons
•
Data
Integrity Overview.
•
Implementing Constraints.
•
Implementing Triggers.
•
Implementing XML Schemas.
Lab
5: Implementing Data Integrity
•
Creating Constraints.
•
Creating Triggers.
•
Implementing XML Schemas.
After
completing this module, students will be able to:
•
Describe types of data integrity and options for enforcing it.
•
Implement constraints.
•
Implement triggers.
•
Implement XML schemas.
Module: Implementing Views
This
module explains how to create views.
Lessons
•
Introduction to Views.
•
Creating and Managing Views.
•
Optimizing Performance by Using Views.
Lab
6: Creating 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.
•
Design views for performance.
Module: Implementing Stored Procedures and Functions
This
module explains how to create stored procedures and functions.
Lessons
•
Implementing Stored Procedures.
•
Creating Parameterized Stored Procedures.
•
Creating Functions.
•
Handling Errors.
•
Controlling Execution Context.
Lab
7: Creating Stored Procedures and Functions
•
Creating Stored Procedures.
•
Creating Functions.
After
completing this module, students will be able to:
•
Implement stored procedures.
•
Create parameterized stored procedures.
•
Handle errors in a stored procedure.
•
Implement Scalar Functions.
•
Create Table Valued Functions.
•
Control Execution Context.
Module:
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
8: 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: 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
9: Using Service Broker
•
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.
|
