Moc-2073 Programming a Microsoft Sql Server 2000 Database

TEMARIO Y CONTENIDOS

Programming a MS SQL Server 2000 Database
Moc 2073

Introduction

This course provides students with the technical skills required to program a database solution by using Microsoft® SQL ServerTM 2000.

At Course Completion

At the end of the course, students will be able:

• To describe the elements of SQL Server 2000;
• Design a SQL Server enterprise application architecture;
• Describe the conceptual basis of programming in Transact-SQL;
• Create and manage databases and their related components;
• Implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers;
• Plan for the use of indexes;
• Create and maintain indexes;
• Create, use, and maintain data views;
• Implement user-defined functions;
• Design, create, and use stored procedures;
• Create and implement triggers;
• Program across multiple servers by using distributed queries, distributed transactions, and partitioned views; optimize query performance; analyze queries;
• Manage transactions and locks to ensure data concurrency and recoverability.

Prerequisites

Before attending this course, students must have:

• Experience using the Microsoft Windows® 2000 operating system
• An understanding of basic relational database concepts.
• Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).
• Familiarity with the role of the database administrator.

The course materials, lectures, and lab exercises are in English. To benefit fully from the instruction, students need an understanding of the English language and completion of the prerequisites.

Microsoft Certified Professional Exams

This course will help the student prepare for the following Microsoft Certified Professional exam:

• Exam 70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class. The following software is provided for use in the classroom:

• Microsoft SQL Server 2000, Enterprise Edition

Course Outline

Module 1: SQL Server Overview

• What Is SQL Server?
• SQL Server Integration
• SQL Server Databases
• SQL Server Security
• Working with SQL Server

Lab

• SQL Server Overview

Module 2: Overview of Programming SQL Server

• Designing Enterprise Application Architecture
• SQL Server Programming Tools
• The Transact-SQL Programming Language
• Elements of Transact-SQL
• Additional Language Elements
• Ways to Execute Transact-SQL Statement

Lab

• Overview of Transact-SQL

Module 3: Creating and Managing Databases

• Creating Databases
• Creating Filegroups
• Managing Databases
• Introduction to Data Structures

Lab

• Creating and Managing Databases

Module 4: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Generating Column Values
• Generating Scripts

Lab

• Creating Data Types and Tables

Module 5: Implementing Data Integrity

• Types of Data Integrity
• Enforcing Data Integrity
• Defining Constraints
• Types of Constraints
• Disabling Constraints
• Using Defaults and Rules
• Deciding Which Enforcement Method to Use

Lab

• Implementing Data Integrity

Module 6: Planning Indexes

• Introduction to Indexes
• Index Architecture
• How SQL Server Retrieves Stored Data
• How SQL Server Maintains Index and Heap Structures
• Deciding Which Columns to Index

Lab

• Determining the Indexes of a Table

Module 7: Creating and Maintaining Indexes

• Creating Indexes
• Creating Index Options
• Maintaining Indexes
• Introduction to Statistics
• Querying the sysindexes Table
• Setting Up Indexes Using the Index Tuning Wizard
• Performance Considerations

Labs

• Creating and Maintaining Indexes
• Viewing Index Statistics

Module 8: Implementing Views

• Introduction to Views
• Advantages of Views
• Defining Views
• Modifying Data Through Views
• Optimizing Performance by Using Views
• Performance Considerations

Lab

• Implementing Views

Module 9: Implementing Stored Procedures

• Introduction to Stored Procedures
• Creating, Executing, Modifying, and Dropping Stored Procedures
• Using Parameters in Stored Procedures
• Executing Extended Stored Procedures
• Handling Error Messages
• Performance Considerations

Labs

• Creating Stored Procedures
• Creating Stored Procedures Using Parameters

Module 10: Implementing User-defined Functions

• What Is a User-defined Function?
• Defining User-defined Functions
• Examples of User-defined Functions

Lab

• Creating User-defined Functions

Module 11: Implementing Triggers

• Introduction to Triggers
• Defining Triggers
• How Triggers Work
• Examples of Triggers
• Performance Considerations

Lab

• Creating Triggers

Module 12: Programming Across Multiple Servers

• Introduction to Distributed Queries
• Executing an Ad Hoc Query on a Remote Data Source
• Setting Up a Linked Server Environment
• Executing a Query on a Linked Server
• Managing Distributed Transactions
• Modifying Data on a Linked Server
• Using Partitioned Views

Lab

• Using Distributed Data

Module 13: Optimizing Query Performance

• Introduction to the Query Optimizer
• Obtaining Execution Plan Information
• Using an Index to Cover a Query
• Indexing Strategies
• Overriding the Query Optimizer

Lab

• Optimizing Query Performance

Module 14: Analyzing Queries

• Queries That Use the AND Operator
• Queries That Use the OR Operator
• Queries That Use Join Operations

Labs

• Analyzing Queries That Use the AND and OR Operators
• Analyzing Queries That Use Different Join Strategies

Module 15: Managing Transactions and Locks

• Introduction to Transactions and Locks
• Managing Transactions
• SQL Server Locking
• Managing Locks

Lab

• Managing Transactions and Locks