Description
This 3-day class teaches practical techniques for developing and troubleshooting high performance SQL queries and optimizing the performance of Microsoft SQL Server 7.0.

Audience
Application designers, developers and programmers and database administrators who will be developing solutions with Microsoft SQL Server 7.0.

Prerequisites
Participants must have prior knowledge of SQL. Database administration experience is helpful but not required.

Hardware and Software
Microsoft SQL Server 7.0 properly installed on Windows NT/2000. Windows 95/98/NT/2000 clients. Administrator privileges must be available for student exercises.

Format

• Presentation
  
• Hands-on
  

Objectives
After completing this course, participants should be able to:

• Configure SQL Server for performance
• Describe Transact-SQL
• Write stored procedures
• Develop cursors and optimize their performance
• Develop efficient stored procedures
• Develop triggers
• Enforce referential integrity
• Describe how SQL Server 7.0 processes queries
• Troubleshoot queries to improve performance
• Improve the performance of SQL statement processing
• Optimize SQL Server 7.0 tables and indexes
• Optimize join processing
• Write more efficient subqueries

Session Configuration

• Configure SQL Server defaults
• Set database options
• Configure ANSI SQL-92 compliance

Programming with Transact-SQL

• Basic programming constructs
• Batches
• Conditional statements
• Transactions

Stored Procedures

• Write and debug stored procedures
• Stored procedure return codes
• Execute remote procedures

Cursors

• Cursor types
• Declaring cursors
• Opening and closing cursors

Triggers

• Trigger uses
• Trigger creation
• INSERT and DELETE in triggers
• Savepoints

Declarative Referential Integrity

• Enforce primary and foreign key integrity with triggers
• Constraints and indexes

Improving SQL Query Performance

• Views and performance
• Data type conversions
• Join processing
• Subqueries
• Union queries