Overview
This course provides the knowledge and skills to implement a Microsoft SQL Server 2005 database. It focuses on teaching you how to use SQL Server 2005 product features and tools related to implementing a database.
Learning Method:
Instructor led.
There is a heavy emphasis on practical exercises.
Who will benefit from this course?
IT Professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing a database.
Pre-requisites:
Before attending this course, you should have:
- a basic knowledge of the Microsoft Windows operating system
- a working knowledge of Transact-SQL.
- a working knowledge of relational databases.
- some experience with database design.
In addition, it is recommended, but not required, that you complete Querying SQL Server 2000 with Transact- SQL (Course Code: 2071)
What can you expect to gain from this course?
After completing this course, you will be able to:
- create databases and database files
- create data types and tables
- use XML-related features in SQL Server 2005
- plan, create, and optimise indexes.
- implement data integrity in SQL Server 2005 databases by using constraints
- implement data integrity in 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
- use notification services to generate and send notifications.
Course Content:
Module 1: Creating databases and database files
- In this module you will be:
- creating a database
- creating schemas
- creating a database snapshot
Module 2: Creating data types and tables
- In this module you will be:
- Creating Data Types
- Creating Tables
- Creating Partitioned Tables
- In this module you will be:
- using FOR XML clause and the OPENXML function
- using the XML data type and its methods
- mapping relational data and XML
- storing XML natively in the database
- using XQuery with XML methods
Module 4: Creating and tuning indexes
- In this module you will be:
- planning, create, and optimise indexes.
- creating indexes
- tuning indexes
- creating XML indexes.
Module 5: Implementing data integrity by using constraints
- In this module you will be:
- providing an overview of data integrity
- implementing constraints
- creating constraints
- disabling constraints.
Module 6: Implementing data integrity by using triggers and XML schemas
- In this module you will be:
- Creating triggers
- Implementing XML schemas
Module 7:Implementing Views
- In this module you will be:
- Introduction to views
- Creating and managing views
- Optimising performance by using views
Module 8: Implementing stored procedures
- Explains how to create stored procedures and functions.
- Describes execution plans, plan caching, and query compilation.
- In this module you will be:
- implementing stored procedures
- creating parameterised stored procedures
- creating functions
- working with execution plans
- executing plan caching and query compilation
- handling errors.
Module 9: Implementing functions
- In this module you will be:
- Creating and using functions
- Working with functions
- Controlling execution context
Module 10: Implementing managed code in the database
- In this module you will be:
- implementing and creating managed database objects.
- using the SQL Server common language runtime
- importing and configuring assemblies.
Module 11: Managing transactions and locks
- In this module you will be:
- using transactions and the SQL Server locking mechanisms to meet the performance and data integrity requirements of your applications.
- reviewing transactions and locks
- managing transactions
- understanding SQL Server locking architecture
- managing locks.
Module 12: Using Service Broker
- In this module you will be:
- building a messaging-based solution with Service Broker
- reviewing Service broker
- creating service broker objects
- sending and receiving messages
- implementing the initiating service
- implementing the target service.
Module 13: Using notification services (optional)
- In this module you will be:
- developing applications that generate and send timely messages to subscribers
- using notification services
- developing notification services solutions