Developing in SQL Server 2005 Course Outline

Duration: 5 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 and using SQL Servers inbuilt tools. The course will also look at 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: Managing Transactions and Locks

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

Module 12: 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 13: Data Access from Applications

1. ADO.NET architecture
2. Creating Windows-based applications that use ADO.NET
3. Connecting to a database
4. Creating a query
5. Using a DataSet object to manage data 6. Binding a DataGrid object to a data source

Module 14: Using .NET Code in SQL Server 2005

1. Writing SQLCLR Code
2. SQLCLR Code Modules
3. Managing Code Modules
4. SQLCLR Security
5. T-SQL vs. .NET Code

Module 15: Advanced Query Techniques

1. Full-Text Search
2. Storing and Processing XML
3. Using APPLY
4. Creating Recursive Queries
5. Creating Pivot Queries
6. Executing Dynamic SQL
7. The XML data type

Module 16: Analyzing and Tuning Performance

1. Evaluating Performance
2. Monitoring with SQL Server Profiler
3. Tuning Queries
4. Indexes and Partitions
5. Using the Database Engine Tuning Advisor
6. Understanding SQL Server Performance Problems

Module 17: Programming Replication

1. Overview of SQL Server Replication
2. Replication Programming Interfaces
3. Configuring Replication Synchronizing Data

Module 18: Using Integration Services

1. Importing and Exporting Data
2. Integration Services Tools
3. Building a Package
4. Troubleshooting a Package

Module 19: Introduction to Reporting Services

1. Understanding Reporting Services 2. Configuring Reporting Services 3. Building a Simple Report 4. Creating, Publishing, and Viewing Reports 5. Using and Managing Published Reports

Module 20: Using XML Web Services

1. Web Service architecture
2. Web Service protocols
3. Consuming an XML Web Service
4. Building an XML Web Service

Download the Microsoft Powerpoint Presentation for this Course >>