This course is not currently offered by Global Knowledge. Information here is provided for reference only.

Get the knowledge and skills to implement a Microsoft SQL Server 2005 database in this course. Learn to use SQL Server 2005 product features and tools related to implementing a database.

Make the most of Microsoft SQL Server 2005's many new features for Developers to increase flexibility, productivity, scalability, and performance. Examine .Net integration, Native XML Data Type, SQL Service Broker, Native Data Encryption, Notification Services, Web Services, ADO.Net 2.0, SQL Server Management Studio, Business Intelligence Development Studio, and integration with Visual Studio 2005. In this hands-on course, you will explore the new and existing features using the development of example applications as a solution-based training model.

Along with our proprietary content, this course incorporates material from the following Official Microsoft Learning Product (OMLP):

• Course 2779: Implementing a Microsoft SQL Server 2005 Database

What You'll Learn

• Create databases and database files
• Create data types and tables
• Use XML-related features in Microsoft SQL Server 2005
• Plan, create, and optimize indexes for performance and storage
• Implement data integrity in Microsoft SQL Server 2005 by using constraints, triggers, and XML schemas
• Create and implement database objects: views, stored procedures, triggers, and functions
• Implement managed code in the database
• Manage transactions and locks
• Use Service Broker to build a messaging-based solution
• Use Service Broker for asynchronous messaging
• Use Notification Services to generate and send notifications
• Use Transact-SQL to query and manipulate data
• Use Transact-SQL to define and modify database objects
• Explore Transact-SQL enhancements
• Compare Transact-SQL and CLR-based stored procedures
• Utilize .Net Framework hosting with Visual Studio 2005
• Utilize ADO.Net 2.0
• Develop an XML Web Service in the database tier
• Enable Native Data Encryption

Who Needs to Attend

IT professionals wanting to become skilled on SQL Server 2005 product features and technologies for implementing a database. This course is also intended for application developers and individuals wanting to explore features of SQL Server specifically designed for developers.

Prerequisites

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

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Creating Databases and Database Files

• Create Databases
• Create Filegroups
• Create Schemas
• Create Database Snapshots

2. Creating Data Types and Tables

• Create Data Types
• Create Tables
• Create Partitioned Tables

3. Using Transact-SQL and XML

• Basic Select Statement and Select Operators
• Table Joins and Sub-queries
• Insert, Update, and Delete
• Aggregate Functions and Group By
• Compute, Compute By, Cube, and Rollup
• New Recursive Capabilities (CTEs)
• Pivot and Unpivot Operations
• Error Handling
• Retrieving XML by using FOR XML
• Shredding XML by using OPENXML
• Introducing XQuery
• Using the XML Data Type
• XML Web Services

4. Creating and Tuning Indexes

• Create Indexes
• Define Clustered, Non-Clustered, and Unique Indexes
• Define Index Creation Fill Factors
• Tune Indexes
• Optimize Indexes
• Use Online Index Operations
• Utilize Physical Storage
• Create XML Indexes

5. Implementing Data Integrity by Using Constraints

• Data Integrity Overview
• Implementing Constraints

6. Implementing Data Integrity by Using Triggers and XML Schemas

• Implementing Triggers
• Implementing XML Schemas

7. Implementing Views

• Introduction to Views
• Creating and Managing Views
• Optimizing Performance by Using Views

8. Implementing Stored Procedures

• Implementing Stored Procedures
• Creating Parameterized Stored Procedures
• Working with Execution Plans
• Handling Errors

9. Implementing Functions

• Create and Use Functions
• Work with Functions
• Control Execution Context

10. Implementing Managed Code in the Database

• Introduction to the SQL Server Common Language Runtime
• Import and Configure Assemblies
• Create Managed Database Objects
• Compare T-SQL and Managed Code

11. Managing Transactions and Locks

• Using Transactions
• Managing Locks

12. Using Service Broker and ADO.Net

• Service-Oriented Architecture (SOA)
• Service Broker Overview
• Create Service Broker Objects
• Send and Receive Messages
• Asynchronous Processing
• Example Application
• Explore ADO.Net 2.0
• Multiple Active Result Sets (MARS)
• Transparent Client Redirection

13. Using Notification Services

• Introduction to Notification Services
• Developing Notification Services Solutions
• Example Notification Services Application

14. Security - Native Data Encryption

• Object-Level Security
• New Schema Object
• Enable Native Data Encryption
• Example Application of Data Encryption

Labs

Lab 1: Creating Databases and Database Files

• Creating a Database
• Creating Schemas
• Creating a Database Snapshot

Lab 2: Creating Data Types and Tables

• Creating Data Types
• Creating Tables
• Creating Partitioned Tables

Lab 3: Using Transact-SQL and XML

• Writing Select Statements and Use Select Operators
• Writing Table Joins, Views, and Sub-queries
• Creating Views with Aggregate Functions and Group By
• Writing Insert, Update, and Delete statements
• Using Compute, Compute By, Cube and Rollup
• Using Pivot and Unpivot operations
• Utilizing Recursive Capabilities (CTEs)
• Mapping Relational Data and XML
• Storing XML Natively in the Database
• Using XQuery with XML Methods
• Creating native XML Web Services

Lab 4: Creating and Tuning Indexes

• Creating Indexes
• Defining Clustered, Non-Clustered, and Unique Indexes
• Defining Index Creation Fill Factors
• Tuning Indexes
• Optimizing Indexes
• Using Online Index Operations
• Utilizing Physical Storage
• Creating XML Indexes

Lab 5: Implementing Data Integrity by Using Constraints

• Creating Constraints
• Disabling Constraints

Lab 6: Implementing Data Integrity by Using Triggers and XML Schemas

• Creating Triggers
• Implementing XML Schemas

Lab 7: Implementing Views

• Creating Views
• Creating Indexed Views
• Creating Partitioned Views

Lab 8: Implementing Stored Procedures

• Creating Stored Procedures
• Working with Execution Plans
• Passing Parameters
• Returning Parameters
• Recompiling Stored Procedures

Lab 9: Implementing Functions

• Creating Functions
• Working with Functions
• Controlling Execution Context

Lab 10: Implementing Managed Code in the Database

• Importing an Assembly
• Creating Managed Database Objects
• Creating and Testing CLR Database Objects
• Creating a Stored Procedure and Loading an Assembly
• Comparing T-SQL and Managed Code
• Utilizing String Manipulation Comparison
• Exploring Data Access

Lab 11: Managing Transactions and Locks

• Using Transactions
• Managing Locks

Lab 12: Using Service Broker and ADO.Net (Optional)

• Creating Service Broker Objects
• Implementing the Initiating Service
• Implementing the Target Service
• Utilizing ADO.Net 2.0

Lab 13: Using Notification Services (Optional)

• Setting up Notification Services

Lab 14: Security - Native Data Encryption (Optional)

• Using the new security model
• Creating and Utilizing Schema Objects
• Utilizing System Tables as Views
• Using Native Data Encryption
• Creating Views for Security
• Implementing Ownership Chains