Developing in SQL Server 2005

5 Day Training Course London and UK Wide

Training formats available

• on-site at your company office UK wide (60 second quote)
• closed group at one of our London training venues (60 second quote)
• near-site at a location close to you
• bespoke one-to-one basis
• tailored training courses to your requirements

Who is this training course suitable for?

This 5 day course is designed for those who are responsible for implementing database objects and programming SQL Server databases by using Transact-SQL.

Benefits

This course will enable delegates to program a SQL Server 2005 database. This includes developing in Transact-SQL. The course will cover the structure of a SQL Server 2005 database. Delegates will be able to create stored procedures, triggers, functions and views.

The course will cover improving database performance by indexing tables and using SQL Servers inbuilt tools. The course will also look at the new features in SQL Server 2005 that are not available in SQL Server 2000.

Course Syllabus

SQL Server 2005 Overview SQL Server 2005 Components and Architecture SQL Server 2005 Developer Tools SQL Server 2005 Security Implementation Language Features SQL Server Programming Tools Elements of Transact-SQL SQL Server Object Names Additional Language Elements: 1. Local Variables 2. Operators 3. Functions Ways to Execute Transact-SQL Statements New Transact-SQL (T-SQL) Features in 2005 Design A Database Define Entities Apply Normalisation Database Objects System Tables Defining Databases Setting Database Options Managing Data and Log File Growth Implement Tables Create Tables Adding and Dropping a Column Generating Column Values Adding Constraints Access and Modify Data Selecting Data Using Sub Queries Select Queries With Joins Modifying Data Importing Data To Tables Writing And Reading XML Implement View Introduction to Views Advantages of Views Defining Views Modifying Data Through Views Optimizing Performance by Using Views Implement Functions What Is a User-defined Function? Defining User-defined Functions Types of User-defined Functions: 1. Scalar 2. Inline 3. Multi-statement Implement Triggers Introduction to Triggers Defining Triggers How Triggers Work Examples of Triggers Performance Considerations Implementing Stored Procedures What Is A Procedure? Types Of Stored Procedure Creating Stored Procedures Declaring Parameters Set Return Value In A Stored Procedure Indexing Tables to Improve Performance Introduction to Indexes Index Architecture How SQL Server Retrieves Stored Data How SQL Server Maintains Index and Heap Structures Deciding Which Columns to Index

Managing Transactions and Locks Introduction to Transactions and Locks Managing Transactions SQL Server Locking Managing Locks Design and Administer Security Levels Design security plan Administer authentication Administer authorisation Administer permissions Administer users, groups and roles Data Access from Applications ADO.NET architecture Creating Windows-based applications that use ADO.NET Connecting to a database Creating a query Using a DataSet object to manage data Binding a DataGrid object to a data source Using .NET Code in SQL Server 2005 Writing SQLCLR Code SQLCLR Code Modules Managing Code Modules SQLCLR Security T-SQL vs. .NET Code Advanced Query Techniques Full-Text Search Storing and Processing XML Using APPLY Creating Recursive Queries Creating Pivot Queries Executing Dynamic SQL The XML data type Analyzing and Tuning Performance Evaluating Performance Monitoring with SQL Server Profiler Tuning Queries Indexes and Partitions Using the Database Engine Tuning Advisor Understanding SQL Server Performance Problems Programming Replication Overview of SQL Server Replication Replication Programming Interfaces Configuring Replication Synchronizing Data Using Integration Services Importing and Exporting Data Integration Services Tools Building a Package Troubleshooting a Package Introduction to Reporting Services Understanding Reporting Services Configuring Reporting Services Building a Simple Report Creating, Publishing, and Viewing Reports Using and Managing Published Reports Using XML Web Services Web Service architecture Web Service protocols Consuming an XML Web Service Building an XML Web Service

What is Microsoft SQL Server?

SQL Server is a relational database management system (RDBMS) from Microsoft that's designed for the enterprise environment. SQL Server runs on T-SQL (Transact -SQL), a set of programming extensions from Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.