Layout Table~~~~7773~7773~~

SQL Server 2005 MCTS Certification Series 15 Sessions - 45 Hours of Interactive Training~
This course is intended for IT professionals who want to expand their SQL knowledge and prepare to successfully pass the Microsoft MCTS exam 70-431. Expert Wayne Snyder begins with object-oriented databases, teaching you how to build upon basic query concepts and create stored procedures, demonstrates the technologies for maintaining a database and shows complete implementation procedures including creating data types, using tables, constraints, triggers, and XML schemas.

Benefits
• Learn from the most detailed instruction available.
• Enhance employment opportunities with database design knowledge.
• Prepare to pass Microsoft MCTS exam 70-431

Also available SQL Server 2005 High Availability Course~~~7773~7774~~

Course Outline Table~~~~7773~7775~~

SQL Server 2005 Maintaining a Database Session 1~Section A: Installation
· 2005 Versions
· System Requirements
· Licensing
· SQL Services
· SQL Server Collations
· Collation Effects
· Warnings
· Unattended Setup

Section B: SQL Install
· Developer Edition Installation
· System Configuration Check
· Server Components
· Set Up Service Account
· Collation Settings

Section C: Post Install Configuration
· Dedicated Administrator Account
· SQL Config Manager
· Configuration Tool
· Management Studio
· Startup Options

Section D: Creating Databases
· Database Basics
· Installed Databases
· Sample Databases
· Database Objects
· Data and Log Files
· Pages and Extents
· Database Creation Process
· Transaction Log
· Create Database Syntax
· Space Estimation
· Log Size and Recovery
· Database Examples

Section E: Placing Database Files
· Storage Management
· Single vs. Multiple Data Files
· Hardware-Based RAID
· RAID 0
· RAID 1
· RAID 10
· RAID 5

Section F: Using Filegroups
· Files and Filegroups
· Default and Read-Only Filegroups
· Filegroup Maintenance
· Configuration Example
· Why Use Filegroups?

Section G: Finding Database Information
· Where to Look
· Using Object Explorer
· Database Properties
· Summary Filtering
· Reports
· Using Transact-SQL

Section H: Schemas
· Schema Namespaces
· Object Name Resolution~~~7773~7798~~

Session 2~Section A: Managing Databases
· Growing a Database File
· Setting Database Options
· Shrinking a Database
· Shrink Database Example

Section B: Disaster Recovery Planning
· Why Backup?
· Full Backup Strategy
· DB & Log Backup Strategy
· Differential Backup Strategy
· Quick Restore
· File/Filegroup Backup Strategy
· Recovery Considerations
· Backing Up
· Backup - How Often?
· SQL 2005 Backup Methods
· SQL 2005 Backup Models

Section C: Full Backup
· Full Backup Details
· Full Backup When?
· Full Backup to Dump Service
· Backup Options
· Tape Options
· Differential Backups
· Transaction Log Backup
· Normal Log Backup
· When the Log Gets Full
· Backup..No_truncate
· Log Away from Data
· Standard Scenario

Section D: File/Filegroup Backup
· Filegroup Backup
· Filegroup Backup for VLDB
· Partial Backup
· Mirror To
· Checksum/Restore Verifyonly
· Full Backup
· Striped Backup
· Backup Setup
· Backing Up
· Creating a Snapshot

Section E: Restoring
· Restore vs. Recovery
· Automatic Recovery
· When to Restore a Database
· Restore Types
· Restore Process
· What is on the Media?
· Restore Steps
· Restore Command/Options
· Restoring the Log
· Restoring Syntax and Master

Section F: Restoring Databases
· Running Recovery
· Restore from Differential
· Piecemeal Recovery~~~7773~7797~~

Session 3~Section A: Database Snapshots
· Snapshot Defined
· Using Snapshots
· Snapshot Recovery

Section B: SQL 2005 Security
· Principles and Securables
· Permissions and Modes
· Password Policies
· Fixed Server Roles
· Credentials

Section C: Securing the Database
· Three Security Steps
· Adding Users
· Database Roles
· Application Role
· Using Application Roles
· Create a Role

Section D: Managing Keys and Certificates
· Keys and Certificates
· Certificate Levels
· Decrypt by Key Function

Section E: Monitoring SQL Server
· Admin To-do List
· Monitoring Memory
· Monitoring Processor
· Monitoring Hard Disk I/O
· Baseline Monitoring SQL Server
· Profiles~~~7773~7796~~

Session 4~Section A: DDL Triggers and Event Notifications
· DDL Triggers
· Event Data Function
· Event Notification

Section B: Transferring Data
· Relocating and Transforming Data
· Data Flow
· Lookup
· Introduction to SSIS
· DTS Package
· Package Configurations

Section C: Jobs, Operators, and Security
· SQL Server Agent
· Schedules and Notifications
· Credentials
· Security/MSDB

Section D: Alerts
· Enterprise Automation
· Event Forwarding~~~7773~7795~~

Session 5~Section A: High Availability
· Failover Clustering
· SQL Editions

Section B: Database Mirroring
· Mode Benefits and Setup
· Mirroring Steps
· Log Shipping
· Switching Roles

Section C: Replication
· Overview
· Publications and Articles

Section D: Replication Types
· Snapshot, Transactional, Merge
· Replication and Distribution
· Transactional Replication~~~7773~7794~~

SQL Server 2005 Implementing a Database Session 1~Section A: Creating Databases
· Database Basics
· Installed Databases
· Sample Databases
· Database Objects
· Data and Log Files
· Pages and Extents
· Database Creation Process
· Transaction Log
· Create Database Syntax
· Space Estimation
· Log Size and Recovery
· Database Examples

Section B: Placing Database Files
· Storage Considerations
· Single vs. Multiple Data Files
· Hardware-Based RAID
· RAID 0
· RAID 1
· RAID 10
· RAID 5

Section C: Using Filegroups
· Files and Filegroups
· Default and Read-Only Filegroups
· Filegroup Maintenance
· Configuration Example
· Why Use Filegroups?

Section D: Finding Database Information
· Where to Look
· Using Object Explorer
· Database Properties
· Summary Filtering
· Reports
· Using Transact-SQL

Section E: Schemas
· Schema Namespaces
· Object Name Resolution

Section F: Database Snapshots
· Defined
· Restrictions
· Implementation
· Best Practices
· Creating a Snapshot
· Viewing and Updating Data
· Restoring Databases with Snapshots

Section G: Basic SQL Server Data Types
· CREATE TABLE Syntax
· Integer
· Exact and Money
· Approximate
· Character Data Types
· Space Allocation
· Datetime
· Date Formats
· Binary
· Global Identifier
· XML
· Special Data Types~~~7773~7803~~

Session 2~Section A: User-Defined Data Types
· Alias Data Types
· .NET Assemblies

Section B: Creating Tables
· Basic Syntax
· Allowing Null Values
· Identity Columns
· Unique Identifiers
· Rowversion Data Type
· Computed Columns
· Temporary Tables
· Table Variables
· Extended Properties
· Dropping and Altering Tables

Section C: Partitioning Tables
· Benefits
· Choosing Tables to Partition
· Three Partition Steps
· Partitioning Example
· Cost Benefits
· Index Partitions
· Three Partition Functions
· Switching Partitions
· Merging Partitions
· Splitting Partitions

Section D: Using XML
· Selecting for XML
· Using RAW mode
· Using XML Explicit
· XPATH

Section E: Shredding XML
· Three Basic Steps
· Flags, Column Patterns, and Edge Table
· XML Data Type
· FLOWR
· Query Method
· Value and Modify Methods
· Insert Statement
· Using the Value Method
· Using the Exist Method
· Other Queries

Section F: Indexing Concepts
· Pros and Cons of Indexing
· What Indexes Affect
· Table Scans
· Index Seeks
· Table Scan vs. Index Seek
· Choosing How Data is Accessed~~~7773~7802~~

Session 3~Section A: Index Structures
· Index Tables
· Sysindexes Table
· Types of Indexes
· Clustered Indexes
· Non-Clustered Indexes
· Uses for Non-Clustered Indexes

Section B: Creating Indexes
· Index Creation Rules
· Index Creation Options
· Include
· Dropping an Index
· Index Creation Hints
· Multicolumn Indexes
· Creating an Index
· Covering Indexes
· Creating Non-Clustered Indexes
· Getting Index Information
· Special Purpose Indexes

Section C: Index Fragmentation
· Fragmentation Overview
· Fixing Fragmentation
· Internal Fragmentation Page Fullness
· Too Full Pages
· External Fragmentation Page Fullness
· Setting Page Fullness
· SQL Server Profiler
· Setting Up Captures
· Capturing
· Analyzing Captures

Section D: XML Indexes
· XML Uses
· Types of XML Indexes
· Primary XML Indexes
· Secondary XML Indexes
· Creating XML Indexes

Section E: Data Integrity
· Types of Data Integrity
· Entity Integrity
· Domain Integrity
· Referential Integrity

Section F: Data Integrity Implementation
· Enforcing Data Integrity
· Constraints
· Defaults
· Using Constraints
· Check Constraints
· Using Check Constraints
· Multiple Constraints
· Using Multiple Constraints
· Alter Table Failures

Section G: PK and Unique Constraints
· Primary Key Constraints
· Using Primary Key Constraints
· Multicolumn Key Constraints
· Choosing Keys
· Unique Constraints
· Using Unique Constraints
· Multicolumn Unique Constraints

Section H: Foreign Key Constraints
· Foreign Keys
· Child Table Actions
· Parent Table Actions
· Foreign Key Rules
· Using Foreign Key Constraints
· Updating Foreign Key Constraints
· Disabling Constraints
· Business Rules~~~7773~7801~~

Session 4~Section A: After Triggers
· Triggers Defined
· Trigger Types
· Trigger Functions
· Inserted/Deleted Tables
· Update Statements
· Creating Special Trigger Functions
· Using Special Trigger Functions
· Other Trigger Functions

Section B: Instead Of Triggers
· Characteristics of Instead Of Triggers
· Creating Instead Of Triggers
· Inserting into Views
· DDL Triggers
· Nested and Recursive Triggers
· Trigger Comparison
· Instead Of Trigger Best Practices

Section C: XML Schemas
· Characteristics of XML Schemas
· Schema Options
· System Views
· Creating Schema Collections
· Validating Documents

Section D: Views
· Views Defined
· Characteristics of Views
· Creating Views
· Chain of Ownership
· Security Checks

Section E: Indexed/Partitioned Views
· Indexed Views
· Creating Indexed Views
· Using Indexed Views
· Partitioned Views
· Issues with Partitioned Views

Section F: Stored Procedures
· Characteristics of Stored Procedures
· Creating Stored Procedures
· Running Stored Procedures
· Late Binding
· Altering Stored Procedures
· Passing Parameters
· Making Parameters Optional
· Creating Output Parameters
· Using Output Parameters
· Testing Output Parameters~~~7773~7800~~

Session 5~Section A: User-Defined Functions
· Defining Functions
· Schema Binding
· Determinism
· Scalar Functions
· Calling Scalar Functions
· Using Scalar Functions
· Table-Valued Functions
· Replacing Stored Procedures
· Calling Table-Valued Functions
· Inline Functions
· Multi-Statement Functions

Section B: Handling Errors
· Try/Catch
· Transactions
· Using Try/Catch
· Inserting Into Tables
· Using Transaction Controls
· XACT_ABORT

Section C: Controlling Execution Context
· Execution Context
· Execute As
· Problems with Cross-Database Chaining
· Controlling Cross-Database Chaining
· Limitations of Cross-Database Chaining

Section D: Implementing Managed Code
· CLR Integration
· Assemblies
· Trust Levels
· Managed Objects
· Using CLR Integration
· Using Regular Expressions

Section E: Service Broker
· Characteristics of Service Broker
· Setting up Service Broker
· Adding Messages to Queues
· Processing Messages~~~7773~7799~~

SQL Server 2005 Writing Queries Session 1~Section A: Relational Databases
· Introduction
· About Databases and SQL Server
· Data Types and Nulls
· Table Relationships

Section B: Starting with SQL
· Client/Server Architecture
· SQL Server Management Studio
· SQL Language
· Batches
· OSQL and SQLCMD

Section C: Introduction to Transact-SQL
· Statement Types
· Language Elements

Section D: SELECT Statements
· Basic Elements
· Column Aliases
· Four-Part Name

Section E: Operators and Expressions
· Arithmetic
· Operator Precedence
· Unary and Bitwise Operators
· String Concatenation
· Table Alias~~~7773~7808~~

Session 2~Section A: Filtering with Comparison Operators
· WHERE Clause
· Comparison Operators

Section B: Filtering with Logical Operators
· Logical Operators
· Operator Precedence

Section C: Filtering with Range of Values
· BETWEEN Operator
· BETWEEN Syntax

Section D: Filtering with Pattern Matching
· LIKE Clause
· Percent and Wildcard
· Other Wildcards

Section E: Writing Good WHERE Clauses
· What Is a SARG?
· Optimizer Plans
· Query Plans

Section F: Handling Missing Values
· Defining Null Values
· Null Basics
· Null Functions

Section G: Scalar Functions
· Function Types
· Date/Time Functions
· Metadata Functions
~~~7773~7807~~

Session 3~Section A: Result Sets
· Sorting
· Eliminating Duplicates

Section B: Using Variables
· Local Variables
· Scalar Variables
· EXEC Command
· Value in SELECT
· Variable Examples

Section C: Control of Flow
· Control of Flow Commands
· IF and CASE Statements
· WHILE Statement
· Simple CASE Expression

Section D: CASE and Ranking Functions
· Converting Codes
· Searched CASE
· Ranking Functions

Section E: Aggregate Functions
· Defining Aggregates
· Aggregate Examples
· Nulls and Aggregates
· CHECKSUM

Section F: Grouping and Summarizing Data
· GROUP BY
· HAVING

Section G: Advanced Grouping and Summarizing
· ROLLUP and CUBE
· COMPUTE and COMPUTE BY
· PIVOT and UNPIVOT

Section H: Retrieving Data with INNER JOINS
· Defining INNER JOINS
· Understanding Joins
· Why Use Joins?

Section I: Retrieving Data with OUTER/CROSS JOINS
· Defining OUTER and CROSS JOINS
· Using OUTER JOINS
· Join Tables with RANGE~~~7773~7806~~

Session 4~Section A: Combining and Limiting Result Sets
· Unions
· UNION Operator UNION ALL
· TOPn
· INTERSECT and EXCEPT
· TABLESAMPLE

Section B: Basic Subqueries
· Subqueries
· Using a Subquery

Section C: Correlated Subqueries
· Using a Correlated Subquery
· EXISTS/NOT EXISTS Function

Section D: Common Table Expressions
· Non-Recursive CTEs
· Using Non-Recursive CTEs

Section E: Recursive (Queries) CTEs
· Traversing Hierarchies

Section F: Modifying Data with INSERT
· Defining an INSERT
· INSERT SELECT and INSERT EXEC

Section G: Modifying Data with SELECT INTO
· SELECT INTO

Section H: Modifying Data with DELETE
· Deleting Rows

Section I: Modifying Data with UPDATE
· Updating Rows~~~7773~7805~~

Session 5~Section A: OUTPUT Clause
· OUTPUT and INSERTED Clauses
· Output to Tables
· DELETED Clause

Section B: Querying Full-Text Indexes
· Full-Text Indexes
· CONTAINS
· Table Versions and FREETEXT

Section C: Using XML
· XML-Defined
· Storing XML Data
· Querying XML Data
· XML Indexes
· Selecting

Section D: Programming Objects for Data Retrieval
· Views
· Selecting from Views
· Updatable Views
· WITH CHECK OPTION

Section E: User Defined Functions
· Scalar UDFs
· Using Scalar UDFs

Section F: Inline and Multi-Statement Functions
· Table Value Functions
· Inline Table Values
· Multi-Statement Table Values

Section G: Stored Procedures
· Stored Procedures Defined
· Creating a Stored Procedure
· Stored Procedure Parameters

Section H: Advanced Query Techniques
· Using CONVERT
· Heterogeneous Queries~~~7773~7804~~

Ordering~~~~7773~7782~~

SQL Server 2005 MCTS Certification Series (70-431) Training Course by LearnKey~Per Seat Licencing Available

Call 01223 894 136 for quotes for MULTIPLE USERS and Training Centre/Bootcamp & Education Solutions!~~PER USER%3A CD-ROM Course|!104177|LIBRARY LICENCE%3A CD-ROM Course|!104178|~7773~7783~sql server 2005 exam certification mcts~