Course Outline: Microsoft SQL Server 2000: Database Design
(5 Days) with Hands-On Labs
Learning Objectives
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
Optimize queries
Analyze query performance
This course description should be used to determine whether the course is appropriate for you based on your current skill and technical training needs. Technical information is provided on the intended audience, course prerequisites, and covered topics. Course content, prices, and availability are subject to change without notice.
Course Audience
This course is designed for system and database administrators who would like to expand their knowledge of database design using Microsoft’s SQL Server 2000.
Course Description
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.
Course Objectives
At course completion the student will be able to perform the following tasks:
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
Optimize queries
Analyze query performance architecture
Prerequisites
Attendees should have experience with installation and administration of Windows 2000 and basic knowledge of querying a SQL server or equivalent knowledge.
TOPICS COVERED IN LECTURE & LAB
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
SQL Server 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
