Developing in SQL Server 2005 Course Outline

Duration: 4 Days

Objectives
This course will enable delegates to program a SQL Server 2005 database. This includes developing in Transact-SQL. The course will cover the structure of a SQL Server 2005 database. Delegates will be able to create stored procedures, triggers, functions and views. The course will cover improving database performance by indexing tables. The will cover the new features in SQL Server 2005 that are not available in SQL Server 2000.

Audience
This course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL.

Prerequisites
An understanding of basic relational database concepts, including:

Logical and physical database design
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)

Module 1: SQL Server 2005 Overview

Module 2: Language Features

1. SQL Server Programming Tools
2. Elements of Transact-SQL
3. SQL Server Object Names
4. Additional Language Elements

1. Local Variables
2. Operators
3. Functions

Module 3: Design A Database

1. Define Entities
2. Apply Normalisation
3. Database Objects
4. System Tables
5. Defining Databases
6. Setting Database Options
7. Managing Data and Log File Growth

Module 4 Implement Tables

1. Create Tables
2. Adding and Dropping a Column
3. Generating Column Values
4. Adding Constraints

Module 5: Access and Modify Data

1 Selecting Data
2. Using Sub Queries
3. Select Queries With Joins
4. Modifying Data
5. Importing Data To Tables
6. Writing And Reading XML

Module 6: Implement View

1. Introduction to Views
2. Advantages of Views
3. Defining Views
4. Modifying Data Through Views
5. Optimizing Performance by Using Views

Module 7: Implement Functions

Implement Functions 1. What Is a User-defined Function?
2. Defining User-defined Functions
3. Types of User-defined Functions

1. Scalar
2. Inline
3. Multi-statement

Module 8: Implement Triggers

1. Introduction to Triggers
2. Defining Triggers
3. How Triggers Work
4. Examples of Triggers
5. Performance Considerations

Module 9: Implementing Stored Procedures

1. What Is A Procedure?
2. Types Of Stored Procedure
3. Creating Stored Procedures
4. Declaring Parameters
5. Set Return Value In A Stored Procedure

Module 10: Indexing Tables to Improve Performance

1. Introduction to Indexes
2. Index Architecture
3. How SQL Server Retrieves Stored Data
4. How SQL Server Maintains Index and Heap Structures
5. Deciding Which Columns to Index

Module 11: Accessing Linked Servers

1. Introduction to Distributed Queries
2. Executing an Ad Hoc Query on a Remote Data Source
3. Setting Up a Linked Server Environment
4. Executing a Query on a Linked Server
5. Executing a Stored Procedure on a Linked Server
6. Managing Distributed Transactions
7. Modifying Data on a Linked Server
8. Using Partitioned Views

Module 12: Managing Transactions and Locks

1. Introduction to Transactions and Locks
2. Managing Transactions
3. SQL Server Locking
4. Managing Locks

Module 13: Design and Administer Security Levels

1. Design security plan
2. Administer authentication
3. Administer authorisation
4. Administer permissions
5. Administer users, groups and roles

Module 14: Monitoring and Tuning

1. Use SQL Profiler to monitor a database
2. Describe how the Database Engine Tuning Advisor works and when to use it
3. Define database partitioning

Download the Microsoft Powerpoint Presentation for this Course >>