SQL Server 2005 > MOC 2779: Implementing a SQL Server 2005 Database

MOC 2779: Implementing a SQL Server 2005 Database


MOC 2779: Implementing s SQL Server 2005 Database

PACKAGE INCLUDES

  • 9 CD-ROMs featuring live instructor-led classroom sessions with full audio, video and demonstration components
  • Printable courseware
  • Step-by-step hands-on-labs
  • Comprehensive, understandable, and reusable as reference material
  • Focused on practical solutions to real-world development problems


    • Single User CD Course
    £ 298
    Single User Online Course
    £ 238
    Multi User CD Course
    £ 596

    COURSE INTRODUCTION

    The Technology Specialist certifications enable professionals to target specific technologies and distinguish themselves by demonstrating in-depth knowledge and expertise in their specialized technologies.

    This video-based training course will provides students with the knowledge and skills needed to implement a Microsoft SQL Server 2005 database. The course focuses on teaching individuals how to use SQL Server 2005 product features and tools related to implementing a database. The course is intended for IT Professionals wanting to learn SQL Server 2005 product features and technologies.

    Course Outline


    Course Introduction

    Course Introduction

    Creating Databases and Database Files

    Lesson 1: Databases
    Considerations
    Transaction Logging
    Options
    Finding Information about Databases
    Demo: Creating a Database
    Lesson 2: Filegroups
    What Are Filegroups?
    Why Create Filegroups?
    Demo: Creating a Filegroup
    Lesson 3: Schemas
    What is a Schema?
    Object Name Resolution
    Demo: Creating a Schema
    Lesson 4: Database Snapshots
    What is a Database Snapshot?
    How does a Database Snapshot Work?
    Demo: Creating a Database Snapshot
    Module 1 Review

    Creating Data Types and Tables

    Lesson 1: Data Types
    System-Supplied Data Types
    Alias Data Types
    Demo: Creating a Data Type
    Lesson 2: Tables
    Data Organization in Rows
    Large Data Value Organization
    Considerations
    Demo: Creating a Table
    Lesson 3: Partitioning a Table
    What is a Partitioned Table?
    What is a Partition Function?
    What is a Partition Scheme?
    Demo: Creating a Partitioned Table
    What can you do with Partitioned Data?
    Module 2 Review

    Using XML

    Lesson 1: Using FOR XML
    Introduction
    RAW Mode Queries
    AUTO Mode Queries
    EXPLICIT Mode Queries
    PATH Mode Queries
    Retrieving Nested XML
    Demo: Using FOR XML
    Lesson 2: Using OPENXML
    Overview
    Managing In-Memory Node Trees
    OPENXML Syntax
    XML Namespaces
    Demo: Shredding XML
    Lesson 3: xml Data Type
    Introduction to the xml Data Type
    Xquery
    Query, Value, and Exist Methods
    Modify Method
    The nodes Method
    Demo: The xml Data Type
    Module 3 Review

    Creating and Tuning Indexes

    Lesson 1: Planning
    SQL Server Data Access
    Clustered Indexes
    Heaps
    Nonclustered Indexes
    Lesson 2: Creating
    Introduction
    Unique Indexes
    Creating Indexes with Multiple Columns
    Indexes on Computed Columns
    Partitioned Indexes
    Incorporating Free Space in Indexes
    Obtaining Index Information
    Demo: Creating Indexes
    Lesson 3: Optimizing
    Database Tuning Advisor
    Demo: Database Tuning Advisor
    Index Fragmentation
    Defragmenting Indexes
    Demo: Defragmenting Indexes
    Lesson 4: XML Indexes
    Kinds of XML Indexes
    Module 4 Review

    Implementing Data Integrity

    Lesson 1: Overview
    Types of Data Integrity
    Enforcing Data Integrity
    Lesson 2: Constraints
    Introduction
    PRIMARY KEY
    DEFAULT
    CHECK
    UNIQUE
    FOREIGN KEY
    Cascading Referential Integrity
    Considerations
    Demo: Creating Constraints
    Lesson 3: Triggers
    Introduction
    INSERT Triggers
    DELETE Triggers
    UPDATE Triggers
    How an INSTEAD OF Trigger Works
    Nested Triggers
    Recursive Triggers
    Demo: Creating Triggers
    Lesson 4: XML Schemas
    Introduction
    XML Schema Collections
    Typed XML
    Demo: Typed XML
    Module 5 Review

         		 

    Implementing Views

    Lesson 1: Introduction
    What are Views?
    Kinds of Views

    Advantages

    Lesson 2: Managing Views
    Creating Views
    Demo: Creating a View
    Altering and Dropping Views
    How Ownership Chains Affect Views
    How To Get Information About Views
    Encryption
    Considerations for Modifying Data
    Lesson 3: Optimizing Performance
    Considerations
    Indexed Views
    Partitioned Views
    Module 6 Review

    Implementing Stored Procedures and Functions

    Lesson 1: Introduction
    Stored Procedures
    Creating Stored Procedures
    Guidelines
    Altering & Dropping Stored Procedures
    Lesson 2: Parameterized SP's
    Using Input Parameters
    Using Output Parameters/Return Values
    Demo: Creating a Parameterized SP
    Lesson 3: Functions
    Types of Functions
    Scalar Functions
    Inline Table-Valued Functions
    Multi-Statement Table-Valued Functions
    Demo: Creating Functions
    Lesson 4: Error Handling
    Structured Exception Handling
    Guidelines
    Demo: Handling Errors
    Lesson 5: Execution Context
    Execution Context
    EXECUTE AS
    Extending Impersonation Context
    Demo: Controlling Execution Context
    Module 7 Review

    Implementing Managed Code in the Database

    Lesson 1: Introduction
    Advantages
    SQL Server CLR Integration
    When to use Managed Code
    Lesson 2: Importing Assemblies
    What are Assemblies
    Importing an Assembly
    Assembly Trust Levels
    Demo: Import & Configure an Assembly
    Lesson 3: Creating Database Objects
    Overview
    Stored Procedures, Triggers, Functions
    Aggregates and User-Defined Types
    Demo: Managed Database Objects
    Module 8 Review

    Using Service Broker

    Lesson 1: Overview
    Introduction
    System Architecture
    Conversation Architecture
    Conversation Process
    Security Architecture
    Lesson 2: Service Broker Objects
    Creating Message Types
    Creating Contracts
    Creating Queues
    Creating Services
    Demo: Creating Service Broker Objects
    Lesson 3: Messages
    Sending Messages
    Receiving Messages
    Demo: Sending and Receiving Messages
    Module 9 Review
    Course Closure
    Page Execution time is 5.641 seconds.