Back to Search Results

Training Course Summary:

This course is designed as a 4 day course. It is aimed at developers who have some basic knowledge of SQL Server administration and basic SQL syntax who wish to develop applications using SQL Server. It concentrates on the use of TSQL, therefore is appropriate for developers using any Client (Web, VB, Crystal etc)

Building SQL Server Databases
Designing a database
Designing and building tables with a database diagram
SQL Server Column types
Using Calculated Columns
Enabling Referential integrity through constraints
Transact SQL Queries
Querying basics review
Basic select statements
Summary queries
Data manipulation functions
Sub Queries
Joins – Inner, Outer and Cross Joins
Co-related sub queries
Joining local and remote tables

Programming Basics
Batch definition
Use of Local variables and global variables
Flow of control statements – IF and WHILE
Error Handling
Using Query Analyser Debug functions *

Views
Use of views
Creating Views
Calculated Columns

Creating and maintaining Indexes
Overview of Indexes
Clustered v Non-clustered
Using Query analyser to chose indexes
Index tuning wizard
How SQL server chooses Indexes and Optimiser hints
Maintaining Indexes
Indexes and Views *
Transactions and Locks
Definition of a transaction
Inserting, Updating and Deleting Data
Locking Issues
Viewing locks

Implementing Stored Procedures and User Defined Functions
Defining a stored procedure
Creating Basic Procedures
Sending Variables to procedures
Outputing variables from procedures
Returning Error codes from procedures
Defining User Defined Functions *
Creating User Defined functions *
Triggers
Definition of a trigger
Use of INSERTED and DELETED
INSTEAD OF and AFTER Triggers *
Nested and Recursive triggers
Triggers for Auditing

Cursors
Definintion of a Cursor
Using Cursors to solve programming problems
Using Cursors to improve server performance


· SQL 2000 only topics (not valid for SQL 7.0)

SQL Database Development 2 day Advanced Course (or part 2)

Executing Full text Searches
Defining Full text search capabilities
Using full-text functions
Taking advantage of proximity and weighting
Creating User Defined Functions
Calling functions in SQL
Functions v Stored Procedures
Binding functions to the schema
Improving Query techniques
Using derived tables
Optimising Queries through Hints and Lock types
Using profiler to identify poorly executing queries
Evaluating Query performance of JOINS, ANDs and ORs

Programming across Multiple servers
Using Federated databases
Design Criteria for a Multi Server Application
Distributed Queries
Distributed transactions
Partitioned Views

XML support
XML Definitions
Configuring XML Support for SQL Server
Retrieving XML using HTTP
Submitting SQL as part of a URL
Saving Query output using FOR XML
Modifying data using OpenXML function