M2073: Microsoft SQL Server 2000 Database Programming

Prerequisites:

Students must have attended the following courses or have equivalent knowledge:

- Connect clients running Windows 2000 to networks and the Internet.
- Create and manage user accounts.
- Configure and manage disks and partitions, including disk striping and mirroring.
- Implement Windows 2000 security.
- An understanding of basic relational database concepts, including:
- Data integrity concepts.
- How data is stored in tables (rows and columns).
- Familiarity with the role of the database administrator.
- Experience using the Microsoft Windows 2000 operating system to:
- Configure the Windows 2000 environment.
- Manage access to resources by using groups.
- Manage data by using the NTFS file system.
- Optimize performance in Windows 2000.
- Logical and physical database design.
- Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many, many-to-many).
- Knowledge of basic Transact-SQL syntax (SELECT, UPDATE, and INSERT statements).

Course Description:

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

This course includes the following modules:

SQL Server Overview

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

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

Creating and Managing Databases

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

Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Generating Scripts

Implementing Data Integrity

• Types of Data Integrity
• Enforcing Data Integrity
• Defining Constraints
• Types of Constraints
• Disabling Constraints
• Using Defaults and Rules
• Deciding Which Data Integrity Methods to use

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

Creating and Maintaining Indexes

• Creating Indexes
• CREATE INDEX Options
• Modifying Data
• Maintaining Indexes
• Introduction to Statistics
• Using an Index to Cover a Query
• Performance Considerations
• Using Index Tuning Tools to Improve Query Performance

Implementing Views

• What Is a View?
• Advantages of Views
• Defining Views
• Partitioned Views and Scaling Out
• Security
• Modifying Data Through Views
• Performance Considerations

Implementing User-defined Functions

• Creating Executing and Altering User-defined Functions
• Using User-defined Functions with MDX
• UDF Permissions
• Using User-defined Functions with Dynamic/Static Filters
• Developing User-defined Functions for XML

Implementing Stored Procedures

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

Implementing Triggers

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

Programming Across Multiple Servers

• Security
• Distributed Queries
• Distributed Transactions
• Partitioned Views

Query Performance

• Introduction to the Query Optimizer
• Obtaining Query Plan Information
• Indexing Strategies

Analyzing Queries Using Graphical SHOWPLAN

• Queries That Use the AND Operator
• Queries That Use the OR Operator
• Queries That Use Join Operations
• Evaluating Query Performance

Managing Transactions and Locks

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