Technical Training - Programming a Microsoft SQL Server 2000 Database

Course No: M2073 Course Length: 5 Days

Who Should Attend: This course has been designed for delegates who have a requirement program a database solution by using Microsoft SQL Server™ 2000.

Pre-Requisites:: Before attending this course, students must have:

Experience using the Microsoft Windows® 2000 operating system to:
Connect clients running Windows 2000 to networks and the Internet.
Configure the Windows 2000 environment.
Create and manage user accounts.
Manage access to resources by using groups.
Configure and manage disks and partitions, including disk striping and mirroring.
Manage data by using the NTFS file system.
Implement Windows 2000 security.
Optimize performance in Windows 2000.

For students who do not meet this prerequisite, the following courses provide students with the necessary knowledge and skills:

Course 2151 & 2152
An understanding of basic relational database concepts, including:
Logical and physical database design.
Data integrity concepts.
Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many).
How data is stored in tables (rows and columns).

For students who do not meet this prerequisite, the following course provides students with the necessary knowledge and skills:

Course 1609
Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).
For students who do not meet this prerequisite, the following course provides students with the necessary knowledge and skills: Course 2071
Familiarity with the role of the database administrator.

Module 1: SQL Server Overview
What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server

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

Module 3: Creating and Managing Databases
Creating Databases
Creating Filegroups
Managing Databases
Introduction to Data Structures

Module 4: Creating Data Types and Tables
Creating Data Types
Creating Tables
Generating Column Values
Generating Scripts

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

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

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

Module 8: Implementing Views
Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views
Performance Considerations

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

Module 10: Implementing User-defined Functions
What Is a User-defined Function?
Defining User-defined Functions
Examples of User-defined Functions

Module 11: Implementing Triggers
Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations

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

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

Module 14: Analyzing Queries
Queries That Use the AND Operator
Queries That Use the OR Operator
Queries That Use Join Operations

Module 15: Managing Transactions and Locks
Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks

Site by Notcon