Microsoft SQL Server 2000 Database

SQL Server 2000 Querying with Transact

Five Days

This course teaches you how to use the Transact-SQL Language query and program Microsoft SQL Server 2000 in a Windows 2000 Server environment.

Prerequisites: To ensure your success, we recommend you first take the following courses or have equivalent knowledge:

Windows 2000: Installation and Administration or the

Windows 2000 for Windows NT Administrators(you should know the basics of querying a SQL server by using the SELECT, INSERT, AND UPD SQL statements).
If you aren't familiar with using these commands, you should first take the

Microsoft SQL Server 2000: Querying with Transact-SQL course

Lesson 1: An Overview of SQL Server

• 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 the 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
• Topic 1C: SQL Architecture
  • 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

Lesson 2: Exploring Transact-SQL

• 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

Lesson 3: Designing and Implementing Databases

• 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 Database File
  • Task 3C-2: Deleting a Database

Lesson 4: Creating and Managing Tables

• 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

Lesson 5: Designing and Implementing Data Integrity

• 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

Lesson 6: Implementing Indexes

• 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 Bank
  • Task 6C-3: Generating a Workload File in SQL Profiler
  • Task 6C-4: Observing Index Statistics

Lesson 7: Joining Tables

• 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

Lesson 8: Designing Advanced Queries

• 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-1: Deleting Rows Based on a Query

Lesson 9: Designing Views

• 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

Lesson 10: Creating Stored Procedures

• 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 Procedure
• 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 Parameters
• Topic 10D: Managing Stored Procedures
  • Task 10D-1: Recompiling a Stored Procedure

Lesson 11: Using Functions

• 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 111B-1: Dropping a User-defined Function

Lesson 12: Creating Triggers

• Topic 12A: Designing and Implementing Triggers
  • Task 12A-1: Designing Triggers
  • Task 12A-2: Creating and INSERT Trigger
  • Task 12A-3: Creating a DELETE Trigger
  • Task 12A-4: Creating an INSTEAD OF Trigger

Lesson 13: Understanding Transactions and Locks

• 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

Lesson 14: Implementing Distributed Queries

• Topic 14A: Establishing Linked Servers
  • Task 14A-1: Defining a Linked Server
  • Task 14A-2: Logging in to Linked Servers
• Topic 14B: Creating and Managed Distributed Queries
  • Task 14B-1: Configuring the MSDTC Service
  • Task 14B-2: Using Ad Hoc Queries to Retrieve Data from Remote Server

Lesson 15: Optimizing Queries

• Topic 15A: Exploring the Query Optimizer
  • Task 15A-1: Configuring the Query Govenor
  • Task 15A-2: Using SHOWPLAN to View the Query Execution
  • Task 15A-3: Analyzing a Graphical Execution Plan
• Topic 15B: Using Indexes to Optimize Queries
  • Task 15B-1: Designing Indexing

Lesson 16: Analyzing Queries

• Topic 16A: Analyzing the Performance of Queries

Microsoft SQL Server 2000 Querying with Transact

SQL Server 2000 Database

Two Days

This course teaches you how to use the Transact-SQL language to query a Microsoft SQL Server 2000 database. In addition, this course assists you in preparing for the Microsoft Certified Systems Engineer and Microsoft Certified Database Administrator Exam.

Prerequisites: To ensure your success, we recommend you first take any of the following courses or have equivalent knowledge:

Windows 2000: Introduction

Windows 2000: Transition from Windows 98

Windows Millennium Edition: Introduction

Windows 98: Transition from Windows 3.1

Windows 98: Transition from Windows 95

Lesson 1: Exploring Transact-SQL

• Topic 1A: An Overview of SQL Server and Transact-SQL
• Topic 1B: Programming in Transact-SQL

Lesson 2: Querying Data

• Topic 2A: Using the SELECT Statement
• Topic 2B: Formatting the Results Set

Lesson 3: Changing Data Through Queries

• Topic 3A: Inserting Data
• Topic 3B: Deleting Data
• Topic 3C: Updating Data

Lesson 4: Summarizing Data

• Topic 4A: Working with Aggregate Functions
• Topic 4B: Grouping Data

Lesson 5: Joining Tables

• Topic 5A: Querying Multiple Tables
• Topic 5B: Implementing Advanced Table Joins

Lesson 6: Implementing Subqueries

• Topic 6A: Designing Subqueries

Lesson 7: Working with Full-text Indexes

• Topic 7A: Implementing the Microsoft Search Service
• Topic 7B: Querying Full-text Indexes

Lesson 8: Programming Transact-SQL

• Topic 8A: Creating and Managing Views
• Topic 8B: Implementing Stored Procedures, Triggers, and User-defined Functions