Network Services Group .:.

Programming a Microsoft SQL Server 2000 Database

Published: July 25, 2002

Introduction

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

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

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

•

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
•	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.

Course Materials

The course materials are yours to keep. 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

Take a closer look: Download Sample Module 1 (Portable Document Format, 1.00 MB).

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.