SQL Server 2000: Database Design Course

This course teaches you how to use the Transact-SQL language to query and program Microsoft SQL Server 2000 in a Windows 2000 Server environment. This course also assists you in preparing for the Microsoft Certified Systems Engineer and Microsoft Certified Database Administrator Exam #70-229, Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition.

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

Windows 2000 for Windows NT Administrators: or have equivalent knowledge. You should also know the basics of querying a SQL server by using the SELECT, INSERT, and UPDATE SQL statements.

Topics covered on the 5 day SQL Server 2000: Database Design course

An Overview of SQL Server

• Exploring the Components of SQL Server
• Exploring the Features of Microsoft SQL Server
• Exploring the SQL Server Services
• Identifying Administrative Utilities
• Using SQL Server Enterprise Manager
• Using SQL Query Analyzer
• Database Structure
• Discussing the Components of SQL Server
• Identifying the Default Databases and System Tables
• Creating and Working with a Database Diagram
• Understanding the SQL Server Architecture
• Exploring the Application Architecture
• Identifying SQL Server Management Tasks
• Determining SQL Server Management Tasks
• Configuring Your Server's Authentication Mode
• Creating a SQL Login Account
• Working With Server and Database Roles

Exploring Transact-SQL

• Working with Transact-SQL
• Using SQL Query Analyzer
• Using Osql
• Transact-SQL Statements
• Creating a Simple Table
• Inserting Data
• Selecting Data
• Assigning Permissions to the Public Database Role
• Programming in Transact-SQL
• Using Variables
• Saving a Script File
• Choosing an XML Format

Designing and Implementing Databases

• Identifying Database Design Issues
• Designing Databases
• Identifying the Space Requirements for Databases
• Optimizing Database Performance
• Creating Databases
• Setting Database Options
• Using Stored Procedures to View Database Information
• Managing Databases
• Configuring SQL Server to Automatically Shrink a DatabaseFile
• Deleting a Database

Creating and Managing Tables

• Design and Create Tables
• Normalizing Table Designs
• Maintaining Tables
• Using SQL Server Enterprise Manager to Generate a Script

Designing and Implementing Data Integrity

• Understanding Data Integrity
• Implementing Constraints
• Adding a Primary Key Constraint
• Adding a Foreign Key Constraint to the Movie Table
• Adding a Default Constraint to the Movie Table
• Adding a Check Constraint to the Movie Table
• Disabling Constraint Checking

Implementing Indexes

• Designing Indexing
• Understanding Index Architecture
• Implementing Indexes
• Creating a Clustered Index on the Movie Table
• Viewing the Information in Sysindexes
• Maintaining Indexes
• Rebuilding an Index
• Using DROP_EXISTING to Re-create an Index Based on a PrimaryKey
• Generating a Workload File in SQL Profiler
• Observing Index Statistics

Joining Tables

• Querying Multiple Tables
• Working with Cross Joins
• Implementing Advanced Table Joins
• Joining Multiple Tables
• Working with Self Joins
• Combining SELECT Statements
• Creating a New Table Based on a SELECT INTO Statement

Designing Advanced Queries

• Designing Subqueries
• Designing Single Value Subqueries
• Changing Data Through Queries
• Inserting Data Based on a Query
• Deleting Rows Based on a Query

Designing Views

• Creating and Managing Views
• Creating a View
• Displaying View Information
• Dropping a View
• Creating an Indexed View
• Creating a Partitioned View

Creating Stored Procedures

• Designing Stored Procedures
• Using Books Online to Research System Stored Procedures
• Running Extended Stored Procedures
• Understanding How SQL Server Processes Stored Procedures
• Creating Stored Procedures
• Creating a Stored Procedure
• Executing a Stored Procedure
• Using Parameters in Stored Procedures
• Creating a Stored Procedure With an Input Parameter
• Creating and Executing a Stored Procedure With Output Parameters
• Managing Stored Procedures
• Recompiling a Stored Procedure

Using Functions

• Working with Aggregate Functions
• Using Aggregate Functions to Summarize Data
• Designing GROUP BY Queries
• Using TOP in a Query
• Designing and Creating User-defined Functions
• Dropping a User-defined Function

Creating Triggers

• Designing and Implementing Triggers
• Designing Triggers
• Creating an INSERT Trigger
• Creating a DELETE Trigger
• Creating an INSTEAD OF Trigger

Understanding Transactions and Locks

• Designing and Implementing Transactions
• Working with Explicit Transactions
• Managing Locks
• Observing the Current Locks on Your Server
• Implementing Session Locking
• Implementing a Lock Timeout
• Implementing Table-level Locking

Implementing Distributed Queries

• Establishing Linked Servers
• Defining a Linked Server
• Logging in to Linked Servers
• Creating and Managing Distributed Queries
• Configuring the MSDTC Service
• Using Ad Hoc Queries to Retrieve Data From Remote Servers

Optimizing Queries

• Exploring the Query Optimizer
• Configuring the Query Governor
• Using SHOWPLAN to View the Query Execution Plan
• Analyzing a Graphical Execution Plan
• Using Indexes to Optimize Queries
• Designing Indexing

Analyzing Queries

• Analyzing the Performance of Queries