Objectives:
At the end of the course the delegate will be able to complete the following list of functions:
Implement a Database Design
Describe SQL Server 6
Describe the Distributed Management Framework
Identify key SQL Server 6.5 administrative tools
Define the roles and responsibilities of a database implementor
Define key data modeling terminology and concepts such as tables, table restrictions, primary key, and foreign key
Describe the library case study
Explain which tables are referenced when specific queries and/or updates are made to the library database

Define Data Storage
Identify system components and types of databases
Describe the information contained in system tables and databases
Describe how SQL Server allocates storage

Creating and Managing Databases
Create a database and assign the transaction log to a separate device
Create user tables
Describe a system-supplied datatype and create user-defined datatypes
Describe the methods for placement of data on drives

Retrieving Data
Write SELECT statements to retrieve specified columns
Select specific rows based on comparisons, ranges, lists, character strings, and search arguments
Manipulate character, numeric, and date time data
Format and sort query results

Retrieving Data—Advanced Topics
Generate summary data with aggregate functions and the GROUP BY and HAVING clauses
Correlate data with natural joins, outer joins, and self joins
Write subqueries
Combine results sets with the UNION operator
Use the new online query extensions: CUBE & ROLLUP

Modifying Data
Add new rows with the INSERT statement
Understand and Use Dynamic Row level Locking
Update and remove rows based on information from other tables
Use data in other tables to determine which rows to delete or update
Use data in other tables as the values for updating
Update and remove rows based on information from other tables

Implementing Indexes
Determine when indexes are useful
Create indexes using the CREATE INDEX statement
Describe the difference between a clustered and non-clustered index, and explain when a clustered index would be advantageous
Create unique and composite indexes
Describe performance considerations when using indexes
Define and describe the terms UPDATE STATISTICS and FILLFACTOR
Create clustered indexes using SORTED_DATA and SORTED_DATA_REORG

Designing Data Integrity
Define how Microsoft SQL Server ensures data integrity
Implement the IDENTITY property
Define the purpose and function of defaults and rules
Define the purpose and function of constraints
Identify the appropriate uses for the different types of constraints
Implement constraints using the ALTER TABLE statement
Apply reference constraints to enforce referential integrity

Implementing Views, Triggers, and Stored Procedures
Explain the purpose and benefits of using views
Create a view using the CREATE VIEW statement
Modify data through views
Define triggers and explain how they differ from stored procedures
Create triggers using the CREATE TRIGGER statement
List the items that triggers enforce
Create a stored procedure
Describe the execution plan of a stored procedure

Programmability
Describe the differences and limitations of batches and scripts
Describe the characteristics of transaction management
Identify control-of-flow statements
Use cursors to work through a results set
Use the CASE statement in a SELECT, UPDATE, or INSERT statement to perform condition checking
Describe the benefits of using the EXECUTE statement
Dynamically build a T-SQL statement using the EXECUTE command

Distributed Data Overview
Determine which method to use in implementing distributed data in your environment
Define the function and purpose of ODS
List the benefits of ODS
Identify the role of the components needed to write applications for SQL Server
Determine which method of replication fits your environment
Use the Distributed Transaction Coordinator
Set up Replication to an ODBC subscriber
Recognise the roles of the publishing server, distribution server, and subscription server

Extensibility of SQL Server 6.5
Describe the role of SQL Executive service
Define the function and purpose of Extended Stored Procedures
Describe the capabilities of MAPI integrated with SQL Server 6.5
Define OLE technology
Describe the components of SQL Server DMO
Describe the relationships among the Alert Manager, the Task Manager, and the Event Manager
Automate the SQL Server DMO model
Define the function and purpose of ODBC
Define the function and purpose of DB-Library

Internet Publishing
Use the Web Assistant to create HTML files
Use scheduled tasks to update pages on the Internet

Use SQL Tables with Access
Utilise the Full Power of SQL Server from Access
Optimising Cursor support Returning Multiple results sets
Optimise Data Access
General Issues Optimise Data Access
SQL PassThru Relevance of 3-tier Client/Server Model

Implement Views and Advanced SQL Select Statements
Create Views
Understand Updatable Views
Create Outer Joins between two Tables
Create a Temporary Table
Use UNION in a SQL Statement
Understand Sub Queries

Understand Transact SQL and Implement Triggers
Understand Transact SQL and Program Flow Structures
Implement Field and Record Validation using Triggers
Ensure Data and Referential Integrity
Implement a Cascading Delete with a Delete Trigger
Update other Tables using Triggers

Implement Stored Procedures
Implement a Stored Procedure
Pass Parameters to a Stored Procedure
Return Values and Results Sets for a Stored Procedure
Understand System Procedures
Use Extended Procedures to send eMail Messages

Utilise the Full Power of SQL Server from Access
Optimising Cursor support
Returning Multiple results sets
Optimise Data Access :General Issues
Optimise Data Access :SQL PassThru
Relevance of 3-tier Client/Server Model

Administrate SQL Server Security
Understand NT Integrated Security
Add new Users to a Database
Define permissions for Tables, Views, and Columns
Define permissions for Stored Procedures
Preview some of the issues in the physical optimisation and tuning of the SQL server

Setting Up Replication
Understand the Replication Process
Understand how to Publish Data
Understand how to Subscribe to Data
Be able to Synchronise Databases

Upsize an Existing Access Application
Understand the Upsizing Process
Import and Export Data from SQL Server
Use the Access Upsizing Wizard
Maintain Tables, Indexes, and Triggers from within Access
Maintain Field Defaults and Rules from within Access

Particulars:
Cost: £1395 (excluding VAT)
Platform: Windows NT
Numbers: Maximum of 6 people on each course at F1’s training facilities in London, Bath and Manchester