Programming a Microsoft SQL Server 2000 Database
Course 2073 - Instructor Led - 5 Days

TITLE

On This Page

Introduction

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

Audience

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; and manage transactions and locks to ensure data concurrency and recoverability.

Prerequisites

Before attending this course, students must have:

Experience using the Microsoft Windows2000 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

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, Designing Data Services and Data Models
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, Querying Microsoft SQL Server 2000 with Transact-SQL

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

Course 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
The following topics are covered in this module:

What Is SQL Server?
SQL Server Integration
SQL Server Databases
SQL Server Security
Working with SQL Server
The following lab is covered in this module:

SQL Server Overview
At the end of this module, you will be able to:

Describe SQL Server 2000 and its supported operating system platforms.
Describe SQL Server integration with Microsoft Windows 2000 and other server applications.
Describe SQL Server databases.
Describe SQL Server security.
Describe SQL Server administration and implementation activities, as well as SQL Server application design options.

Module 2: Overview of Programming SQL Server
The following topics are covered in this module:

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
The following lab is covered in this module:

Overview of Transact-SQL
At the end of this module, you will be able to:

Describe the concepts of enterprise-level application architecture.
Describe the primary SQL Server programming tools.
Explain the difference between the two primary programming tools in SQL Server.
Describe the basic elements of Transact-SQL.
Describe the use of local variables, operators, functions, control of flow statements, and comments.
Describe the various ways to execute Transact-SQL statements.

Module 3: Creating and Managing Databases
The following topics are covered in this module:

Creating Databases
Creating Filegroups
Managing Databases
Introduction to Data Structures
The following lab is covered in this module:

Creating and Managing Databases
At the end of this module, you will be able to:

Create a database.
Create a filegroup.
Manage a database.
Describe data structures.

Module 4: Creating Data Types and Tables
The following topics are covered in this module:

Creating Data Types
Creating Tables
Generating Column Values
Generating Scripts
The following lab is covered in this module:

Creating Data Types and Tables
At the end of this module, you will be able to:

Create and drop user-defined data types.
Create and drop user tables.
Generate column values.
Generate a script.

Module 5: Implementing Data Integrity
The following topics are covered in this module:

Types of Data Integrity
Enforcing Data Integrity
Defining Constraints
Types of Constraints
Disabling Constraints
Using Defaults and Rules
Deciding Which Enforcement Method to Use
The following lab is covered in this module:

Implementing Data Integrity
At the end of this module, you will be able to:

Describe the types of data integrity.
Describe the methods to enforce data integrity.
Determine which constraint to use and create constraints.
Define and use DEFAULT, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints.
Disable constraints.
Describe and use defaults and rules.
Determine which data integrity enforcement methods to use.

Module 6: Planning Indexes
Take a closer look: Download Sample Module 6 (Portable Document Format, 730 KB).

The following topics are covered in this module:

Introduction to Indexes
Index Architecture
How SQL Server Retrieves Stored Data
How SQL Server Maintains Index and Heap Structures
Deciding Which Columns to Index
The following lab is covered in this module:

Determining the Indexes of a Table
At the end of this module, you will be able to:

Describe why and when to use an index.
Describe how SQL Server uses clustered and nonclustered indexes.
Describe how SQL Server index architecture facilitates the retrieval of data.
Describe how SQL Server maintains indexes and heaps.
Describe the importance of selectivity, density, and distribution of data when deciding which columns to index.

Module 7: Creating and Maintaining Indexes
The following topics are covered in this module:

Creating Indexes
Creating Index Options
Maintaining Indexes
Introduction to Statistics
Querying the sysindexes Table
Setting Up Indexes Using the Index Tuning Wizard
Performance Considerations
The following labs are covered in this module:

Creating and Maintaining Indexes
Viewing Index Statistics
At the end of this module, you will be able to:

Create indexes and indexed views with unique or composite characteristics.
Use the CREATE INDEX options.
Describe how to maintain indexes over time.
Describe how the query optimizer creates, stores, maintains, and uses statistics to optimize queries.
Query the sysindexes table.
Describe how the Index Tuning Wizard works and when to use it.
Describe performance considerations that affect creating and maintaining indexes.

Module 8: Implementing Views
The following topics are covered in this module:

Introduction to Views
Advantages of Views
Defining Views
Modifying Data Through Views
Optimizing Performance by Using Views
Performance Considerations
The following lab is covered in this module:

Implementing Views
At the end of this module, you will be able to:

Describe the concept of a view.
List the advantages of views.
Define a view with the CREATE VIEW statement.
Modify data through views.
Optimize performance by using views.

Module 9: Implementing Stored Procedures
The following topics are covered in this module:

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
The following labs are covered in this module:

Creating Stored Procedures
Creating Stored Procedures Using Parameters
At the end of this module, you will be able to:

Describe how a stored procedure is processed.
Create, execute, modify, and drop a stored procedure.
Create stored procedures that accept parameters.
Execute extended stored procedures.
Create custom error messages.

Module 10: Implementing User-defined Functions
The following topics are covered in this module:

What Is a User-defined Function?
Defining User-defined Functions
Examples of User-defined Functions
The following lab is covered in this module:

Creating User-defined Functions
At the end of this module, you will be able to:

Describe the three types of user-defined functions.
Create and alter user-defined functions.
Create each of the three types of user-defined functions.

Module 11: Implementing Triggers
The following topics are covered in this module:

Introduction to Triggers
Defining Triggers
How Triggers Work
Examples of Triggers
Performance Considerations
The following lab is covered in this module:

Creating Triggers
At the end of this module, you will be able to:

Create a trigger.
Drop a trigger.
Alter a trigger.
Describe how various triggers work.
Evaluate the performance considerations that affect using triggers.

Module 12: Programming Across Multiple Servers
The following topics are covered in this module:

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
The following lab is covered in this module:

Using Distributed Data
At the end of this module, you will be able to:

Describe distributed queries.
Write ad hoc queries that access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
Set up a linked server environment to access data that is stored in a remote SQL Server 2000 or in an OLE DB data source.
Write queries that access data from a linked server.
Execute stored procedures on a remote server or linked server.

Module 13: Optimizing Query Performance
The following topics are covered in this module:

Introduction to the Query Optimizer
Obtaining Execution Plan Information
Using an Index to Cover a Query
Indexing Strategies
Overriding the Query Optimizer
The following lab is covered in this module:

Optimizing Query Performance
At the end of this module, you will be able to:

Explain the role of the query optimizer and how it works to ensure that queries are optimized.
Use various methods for obtaining execution plan information so that they can determine how the query
optimizer processed a query and validate that the most efficient query plan was generated.
Create indexes that cover queries
Identify indexing strategies that reduce page reads.
Evaluate when to override the query optimizer.

Module 14: Analyzing Queries
The following topics are covered in this module:

Queries That Use the AND Operator
Queries That Use the OR Operator
Queries That Use Join Operations
The following labs are covered in this module:

Analyzing Queries That Use the AND and OR Operators
Analyzing Queries That Use Different Join Strategies
At the end of this module, you will be able to:

Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the AND logical operator.
Analyze the performance gain of writing efficient queries and creating useful indexes for queries that contain the OR logical operator.
Evaluate how the query optimizer uses different join strategies for query optimization.

Module 15: Managing Transactions and Locks
The following topics are covered in this module:

Introduction to Transactions and Locks
Managing Transactions
SQL Server Locking
Managing Locks
The following lab is covered in this module:

Managing Transactions and Locks
At the end of this module, you will be able to:

Describe transaction processing.
Execute, cancel, or roll back a transaction.
Identify locking concurrency issues.
Identify resource items that can be locked and the types of locks.
Describe lock compatibility.
Describe how SQL Server 2000 uses dynamic locking.
Set locking options and display locking information.