Identify the features of Microsoft SQL Server 2000.

Use Transact-SQL to query a SQL server.

Design, create, and manage databases.

Create and manage tables.

Implement data integrity techniques.

Design and implement indexes.

Query multiple tables through the use of joins.

Design subqueries.

Use aggregate functions in queries and create user-defined functions.

Create and manage views.

Design and implement stored procedures.

Create triggers.

Manage transactions and locks.

Implement queries across multiple servers.

Optimise queries.

Analyze query performance

Topic 1A: Exploring the Components of SQL Server

Task 1A-1: Exploring the Features of Microsoft SQL Server

Task 1A-2: Exploring the SQL Server Services

Task 1A-3: Identifying Administrative Utilities

Task 1A-4: Using SQL Server Enterprise Manager

Task 1A-5: Using SQL Query Analyzer

Topic 1B: SQL Server Database Structure

Task 1B-1: Discussing the Components of SQL Server

Task 1B-2: Identifying the Default Databases and System Tables

Task 1B-3: Creating and Working with a Database Diagram

Task 1C-1: Understanding the SQL Server Architecture

Task 1C-2: Exploring the Application Architecture

Topic 1D: Identifying SQL Server Management Tasks

Task 1D-1: Determining SQL Server Management Tasks

Task 1D-2: Configuring Your Server’s Authentication Mode

Task 1D-3: Creating a SQL Login Account

Task 1D-4: Working With Server and Database Roles

Topic 2A: Working with Transact-SQL

Task 2A-1: Using SQL Query Analyzer

Task 2A-2: Using Osql

Topic 2B: Transact-SQL Statements

Task 2B-1: Creating a Simple Table

Task 2B-2: Inserting Data

Task 2B-3: Selecting Data

Task 2B-4: Assigning Permissions to the Public Database Role

Topic 2C: Programming in Transact-SQL

Task 2C-1: Using Variables

Task 2C-2: Saving a Script File

Task 2C-3: Choosing an XML Format

Topic 3A: Identifying Database Design Issues

Task 3A-1: Designing Databases

Task 3A-2: Identifying the Space Requirements for Databases

Task 3A-3: Optimizing Database Performance

Topic 3B: Creating Databases

Task 3B-1: Setting Database Options

Task 3B-2: Using Stored Procedures to View Database Information

Topic 3C: Managing Databases

Task 3C-1: Configuring SQL Server to Automatically Shrink a DatabaseFile

Task 3C-2: Deleting a Database

Topic 4A: Design and Create Tables

Task 4A-1: Normalizing Table Designs

Topic 4B: Maintaining Tables

Task 4B-1: Using SQL Server Enterprise Manager to Generate a Script

Topic 5A: Understanding Data Integrity

Topic 5B: Implementing Constraints

Task 5B-1: Adding a Primary Key Constraint

Task 5B-2: Adding a Foreign Key Constraint to the Movie Table

Task 5B-3: Adding a Default Constraint to the Movie Table

Task 5B-4: Adding a Check Constraint to the Movie Table

Task 5B-5: Disabling Constraint Checking

Topic 6A: Designing Indexing

Task 6A-1: Understanding Index Architecture

Topic 6B: Implementing Indexes

Task 6B-1: Creating a Clustered Index on the Movie Table

Task 6B-2: Viewing the Information in Sysindexes

Topic 6C: Maintaining Indexes

Task 6C-1: Rebuilding an Index

Task 6C-2: Using DROP_EXISTING to Re-create an Index Based on a PrimaryKey

Task 6C-3: Generating a Workload File in SQL Profiler

Task 6C-4: Observing Index Statistics

Topic 7A: Querying Multiple Tables

Task 7A-1: Working with Cross Joins

Topic 7B: Implementing Advanced Table Joins

Task 7B-1: Joining Multiple Tables

Task 7B-2: Working with Self Joins

Task 7B-3: Combining SELECT Statements

Task 7B-4: Creating a New Table Based on a SELECT INTO Statement

Topic 8A: Designing Subqueries

Task 8A-1: Designing Single Value Subqueries

Topic 8B: Changing Data Through Queries

Task 8B-1: Inserting Data Based on a Query

Task 8B-2: Deleting Rows Based on a Query

Topic 9A: Creating and Managing Views

Task 9A-1: Creating a View

Task 9A-2: Displaying View Information

Task 9A-3: Dropping a View

Task 9A-4: Creating an Indexed View

Task 9A-5: Creating a Partitioned View

Topic 10A: Designing Stored Procedures

Task 10A-1: Using Books Online to Research System Stored Procedures

Task 10A-2: Running Extended Stored Procedures

Task 10A-3: Understanding How SQL Server Processes Stored Procedures

Topic 10B: Creating Stored Procedures

Task 10B-1: Creating a Stored Procedure

Task 10B-2: Executing a Stored Procedure

Topic 10C: Using Parameters in Stored Procedures

Task 10C-1: Creating a Stored Procedure With an Input Parameter

Task 10C-2: Creating and Executing a Stored Procedure With Output Parameters

Topic 10D: Managing Stored Procedures

Task 10D-1: Recompiling a Stored Procedure

Topic 11A: Working with Aggregate Functions

Task 11A-1: Using Aggregate Functions to Summarize Data

Task 11A-2: Designing GROUP BY Queries

Task 11A-3: Using TOP in a Query

Topic 11B: Designing and Creating User-defined Functions

Task 11B-1: Dropping a User-defined Function

Topic 12A: Designing and Implementing Triggers

Task 12A-1: Designing Triggers

Task 12A-2: Creating an INSERT Trigger

Task 12A-3: Creating a DELETE Trigger

Task 12A-4: Creating an INSTEAD OF Trigger

Topic 13A: Designing and Implementing Transactions

Task 13A-1: Working with Explicit Transactions

Topic 13B: Managing Locks

Task 13B-1: Observing the Current Locks on Your Server

Task 13B-2: Implementing Session Locking

Task 13B-3: Implementing a Lock Timeout

Task 13B-4: Implementing Table-level Locking

Topic 14A: Establishing Linked Servers

Task 14A-1: Defining a Linked Server

Task 14A-2: Logging in to Linked Servers

Topic 14B: Creating and Managing Distributed Queries

Task 14B-1: Configuring the MSDTC Service

Task 14B-2: Using Ad Hoc Queries to Retrieve Data From Remote Servers

Topic 15A: Exploring the Query Optimizer

Task 15A-1: Configuring the Query Governor

Task 15A-2: Using SHOWPLAN to View the Query Execution Plan

Task 15A-3: Analyzing a Graphical Execution Plan

Topic 15B: Using Indexes to Optimize Queries

Task 15B-1: Designing Indexing

Topic 16A: Analyzing the Performance of Queries