Implementing a SQL Server 2005 Database

The course focuses on teaching delegates how to use SQL Server 2005 product features and tools related to implementing a database.

Who should attend?

This course is intended for information technology (IT) professionals and developers.

Delegates will be able to

• Create databases and database files
• Create data types and tables
• Use XML-related features in Microsoft SQL Server 2005
• Plan, create, and optimize indexes
• Implement data integrity in Microsoft SQL Server 2005 databases by using constraints
• Implement data integrity in Microsoft SQL Server 2005 by using triggers.
• Implement views
• Implement stored procedures
• Implement functions
• Implement managed code in the database
• Manage transactions and locks
• Use Service Broker to build a messaging-based solution

Pre-requisites

Experience with these products or technologies is necessary,

• Basic knowledge of the Microsoft Windows operating system and its core functionality
• Working knowledge of Transact-SQL
• Working knowledge of relational databases
• Some experience with database design

Topics covered

Creating Databases and Database Files

• Understand how to create a database and its major components including setting database options, creating file groups, schemas and database snapshots

Creating Data Types and Tables

• How to define custom Transact-SQL
• How to create tables and use partitioned tables to organise data in multiple partitions

Creating and Tuning Indexes

• How to plan, create, and optimise indexes to attain optimal performance benefits

Implementing Data Integrity by Using Constraints and Triggers

• Implementing data integrity in SQL Server 2005 by using constraints and triggers

Using XML

• How to work with XML, including FOR XML clause and OPEN XML function, XQuery expressions and the xml native data type
• The considerations for creating XML indexes and the syntax used to create them
• XML schemas
• XML schema collections

Implementing Views

• Different views available in Microsoft SQL Server 2005

Implementing Stored Procedures

• The design and implementation of stored procedures to enforce business rule or data consistency
• Modifying and maintaining existing stored procedures written by other developers

Implementing Functions

• The design and implementation of user-designed functions
• Modifying and maintaining existing functions written by other developers

Implementing Managed Code in the Database

• How to use managed code to implement database objects such as stored procedures, user-defined data types, user-defined functions and triggers

Managing Transactions & Locks

• Students will learn how to use transactions and SQL Server locking mechanisms

Using Service Broker

• How to use Service Broker to create secure, reliable and scalable applications