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

After completing this 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.

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.

This course will help the student prepare for this Microsoft Certified Professional exam:

• Exam 70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition

The student kit includes a comprehensive workbook and other necessary materials for this class.

The following software is provided in the student kit.

• Microsoft SQL Server 2000, Enterprise Edition

Module 1: SQL Server Overview

Topics:

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

Lab:

• SQL Server Overview

Skills:

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

Topics:

• 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

Lab:

• Overview of Transact-SQL

Skills:

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

Topics:

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

Lab:

• Creating and Managing Databases

Skills:

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

Topics:

• Creating Data Types
• Creating Tables
• Generating Column Values
• Generating Scripts

Lab:

• Creating Data Types and Tables

Skills:

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

Topics:

• Types of Data Integrity
• Enforcing Data Integrity
• Defining Constraints
• Types of Constraints
• Disabling Constraints
• Using Defaults and Rules
• Deciding Which Enforcement Method to Use

Lab:

• Implementing Data Integrity

Skills:

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

Topics:

• Introduction to Indexes
• Index Architecture
• How SQL Server Retrieves Stored Data
• How SQL Server Maintains Index and Heap Structures
• Deciding Which Columns to Index

Lab:

• Determining the Indexes of a Table

Skills:

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

Topics:

• Creating Indexes
• Creating Index Options
• Maintaining Indexes
• Introduction to Statistics
• Querying the sysindexes Table
• Setting Up Indexes Using the Index Tuning Wizard
• Performance Considerations

Lab:

• Creating and Maintaining Indexes
• Viewing Index Statistics

Skills:

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

Topics:

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

Lab:

• Implementing Views

Skills:

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

Topics:

• 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

Lab:

• Creating Stored Procedures
• Creating Stored Procedures Using Parameters

Skills:

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

Topics:

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

Lab:

• Creating User-defined Functions

Skills:

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

Topics:

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

Lab:

• Creating Triggers

Skills:

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

Topics:

• 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

Lab:

• Using Distributed Data

Skills:

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

Topics:

• Introduction to the Query Optimizer
• Obtaining Execution Plan Information
• Using an Index to Cover a Query
• Indexing Strategies
• Overriding the Query Optimizer

Lab:

• Optimizing Query Performance

Skills:

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

Topics:

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

Lab:

• Analyzing Queries That Use the AND and OR Operators
• Analyzing Queries That Use Different Join Strategies

Skills:

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

Topics:

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

Lab:

• Managing Transactions and Locks

Skills:

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.