SQL Server 2000/2005: Transact-SQL Querying/Updating + Programming

Course Overview

On completion of this course delegates will understand relational database concepts and the Structured Query Language (Transact-SQL) in the Microsoft SQL Server environment.

Audience

Database developers and support personnel who need to fully understand the principles of database implementation and programming.

Prerequisites

Delegates should have a good working knowledge of Windows 2000/XP/2003, together with a basic understanding of the principles of a database.

Course Outline

Microsoft SQL Server 2000/2005 Feature Overview and History

• What is Microsoft SQL Server?
• Client/Server Architecture
• Microsoft SQL Server - A Brief History
• SQL Server 2005 A Review of New Features

Data Storage and Management

• Creating Databases and Logs
• Using Filegroups
• Managing Database Availability
• Managing Database Size

Transact SQL Queries

• Querying SQL Server Tables
• What is Transact SQL?
• Basic SELECT Statement
• Transact SQL Built-In Functions
• The WHERE Clause
• Joining Tables
• ORDER BY
• Summary Queries
• Summary Queries
• Sub-Queries
• Full-Text Searching

Data Transformation Services

• What is DTS?
• Using tha Data Import Wizard
• Using DTS as a data pump
• Advanced DTS
• BCP, and other Import Export options

Management of Indexes in Microsoft SQL Server 2000/2005

• The Benefits of Indexes
• Clustered versus Non-Clustered indexes
• Creating Indexes
• Using composite indexes for index covering
• Determining which indexes are being used
• Duplicate Keys and Rows and How to solve such problems
• The Distribution Page and Statistics
• Using FILLFACTOR correctly.
• Useful Surrogate Keys
• • The Index Tuning Wizard
  • Maintenance Plan Wizard

Making Best Use of Views

• The Uses of Views
• Limitations of Views
• Creating Views
• Indexing Views

Transaction Programming in SQL Server

• How Data is Changed in SQL Server
• Inserting and Copying Records
• Changing Records
• Deleting Records
• Transaction Processing Commands
• Distributed Transactions
• Locking Issues

Programming with Transact SQL

• Transact SQL Batch and script rules
• Transaction processing commands
• Using Variables
• Control of flow Statements
• Using Global Variables
• Using the EXEC() Function
• Error Handling
• Using Cursors

Writing Stored Procedures and User-Defined Functions

• SQL Server 2000/2005 Query Caching and Related Features
• Uses for Stored Procedures
• Creating Stored Procedures
• Compilation problems and methods
• Passing parameters
• Returning Values and Updating Parameters
• Temporary Procedures and AutoStart Procedures

Implementing Triggers

• Basic Trigger assumptions and syntax
• Trigger Events
• Improving Trigger Performance
• Checking the Number of Rows Affected
• Trigger Applications
• Nested Triggers
• Disabling Triggers

Rapid Client-Side Development using Access 2000

• Microsoft Access - The Perfect Partner of SQL Server
• Creating Access Projects
• MSDE and Scalability Issues
• Access Forms - Beat That!
• The Report Designer
• Upsizing Access Applications to Microsoft SQL Server 2000/2005

Introduction to VBA Programming

• Introduction to VBA
• The VBA Programming Environment
• The VBA Language
• Error Handling

Writing Code Behind Forms.

• Using Code Behind Forms to build GUIs
• Code Behind Forms and Class Modules
• GUI Problems and Solutions

Data Access through ActiveX Data Objects

• Bound versus Unbound Forms
• Data Access through VBA
• The ActiveX Data Object Model
• Working with Objects and Collections
• Connection Management
• ADO Commands

Using DMO and DTSObjects to Build SQL Server Admin Tools

• Automating Microsoft SQL Server 2000/2005
• DMO Overview
• Creating a Database through DMO
• Modifying a Property
• The DTS Package Object Library

Web-Based SQL Server Front Ends

• Static Web Pages - A HTML Primer
• Generating Static Web Pages from SQL Server
• Starting out with XML

SQL Server 2000/2005: Transact-SQL Querying/Updating + Programming / MSQ-TQUP