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. DURATION:   5 days. Hands on. OBJECTIVES:   On completion of this course delegates will understand relational database concepts and the Structured Query Language (Transact-SQL) in the Microsoft SQL Server environment. COURSE CONTENT:   Microsoft SQL Server 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 Designing Tables with Integrity Designing Tables through the Database Diagramer Creating Tables SQL Server Data Types Calculated Columns Setting up Constraints. The Identity Property Primary Key Constraints Foreign Key Constraints Check and Default Constraints Creating and using User-Defined Data Types 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 Data Warehousing with Analysis Services Introduction to Data Warehousing Designing a Database for OLAP Using DTS to Populate a Warehouse Using Analysis Manager to Create Cubes Browsing Cubes with the PivotTable Service Programming OLAP Maintaining Data Warehouses Building Applications with Microsoft English Query What is Microsoft English Query Developing applications with English Query