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.

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.