Microsoft SQL database programming

Concentrika poppy

About the course

Delegates will attain an in-depth understanding of SQL Server’s programming environment and capabilities. You will need cursors, for instance, where the set returns of SQL no longer meet your programming requirements and you need to “stop on every row” to make coding decisions. The course covers control structures, stored procedures, stored functions and database triggers.

Audience

If you come from one of these business or technical areas, you would find the course very useful:

An application developer in a Microsoft environment

A skilled end user looking at advanced ways of accessing data
Programming VB clients to call stored procedures
Building tables with triggers
Project manager seeking to understand and control Microsoft database programming projects
Database administrators needing to write automated routines or other facilities for users
Skilled end-users wishing to author their own routines
Optimising SQL Server for performance

Prerequisites

Delegates should have a reasonable knowledge of Transact SQL and the SQL Server platform. Attendance on our security course or introduction to SQL Server would be a good preparation. Previous programming experience would be useful, but is not essential if you have the required aptitude for this type of work.

Duration

3 days

1. Tables and constraints

Designing tables
Calculated columns
Setting up constraints
The IDENTITY property
Primary keys and indexing
Check and default constraints
User-defined datatypes
Legacy rules and defaults

4. Indexes

The benefits of indexes
Clustered versus non-clustered indexes
Creating indexes
Using SHOWPLAN
Using composite indexes
Duplicate keys and rows
The distribution page and UPDATE STATISTICS
The index tuning wizard
Index maintenance plans

7. Stored procedures

Query caching
Introduction to stored procedures
Creating stored procedures
Passing parameters
Returning values and updating parameters
Compilation problems and methods
Temporary procedures and AutoStart procedures

2. DTS packages

Modifying DTS packages
DTS transformations
TRANSACT SQL queries
Querying Microsoft SQL Server 7.0 tables
What is SQL and Transact SQL?
Transact SQL built-in functions
Summary queries
Sub-queries

5. Transaction control

Overview
Inserting and copying records
Changing records
Removing records
Transaction processing commands
Locking issues

8. Triggers

Trigger syntax
Improving trigger performance
Checking the number of rows affected
Nested Triggers

3. Views

Uses of views
Limitations of views
Using calculated columns
Creating views

6. Programming basics

Transact SQL batch
Using variables
Control of flow statements
Using global variables
The EXEC() function
Error handling
Using cursors