2073 – Programming MS SQL Server 2000 Database

Course Specifications

Course length: 5 days

Location: Entre Technology Services, LLC  1501 N. 14th St. West, Suite 1511

Registration: Call your Account Manager at 406.256.5700 or use our Registration Forms

Course Description

This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server™ 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 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, Microsoft Windows 2000 Network and Operating System Essentials 

Course 2152, Implementing Microsoft Windows 2000 Professional and Server 

Course Outline

Lesson 1: SQL Server Overview

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

Lesson 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 

Lesson 3: Creating and Managing Databases

Creating Databases 
Creating Filegroups 
Managing Databases 
Introduction to Data Structures 

Lesson 4: Creating Data Types and Tables

Creating Data Types 
Creating Tables 
Generating Column Values 
Generating Scripts 

Lesson 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 

Lesson 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 

Lesson 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 

Lesson 8: Implementing Views

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

Lesson 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 

Lesson 10: Implementing User-defined Functions

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

Lesson 11: Implementing Triggers

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

Lesson 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 

Lesson 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 

Lesson 14: Analyzing Queries 

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

Lesson 15: Managing Transactions and Locks

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