| |
Implementing a MS SQL Server 2005 Database
Training 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
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.
List of Course Features
Implementing a MS SQL Server 2005 Database Course Contents
|
| |
Creating Databases and Database Files
Databases
Considerations
Transaction Logging
Options
Finding Information about Databases
Demo: Creating a Database
Filegroups
What Are Filegroups?
Why Create Filegroups?
Demo: Creating a Filegroup
Schemas
What is a Schema?
Object Name Resolution
Demo: Creating a Schema
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
Data Types
System-Supplied Data Types
Alias Data Types
Demo: Creating a Data Type
Tables
Data Organization in Rows
Large Data Value Organization
Considerations
Demo: Creating a Table
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
Using FOR XML
Introduction
RAW Mode Queries
AUTO Mode Queries
EXPLICIT Mode Queries
PATH Mode Queries
Retrieving Nested XML
Demo: Using FOR XML
Using OPENXML
Overview
Managing In-Memory Node Trees
OPENXML Syntax
XML Namespaces
Demo: Shredding XML
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
Planning
SQL Server Data Access
Clustered Indexes
Heaps
Nonclustered Indexes
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
Optimizing
Database Tuning Advisor
Demo: Database Tuning Advisor
Index Fragmentation
Defragmenting Indexes
Demo: Defragmenting Indexes
XML Indexes
Kinds of XML Indexes
Module 4 Review
Implementing Data Integrity
Overview
Types of Data Integrity
Enforcing Data Integrity
Constraints
Introduction
PRIMARY KEY
DEFAULT
CHECK
UNIQUE
FOREIGN KEY
Cascading Referential Integrity
Considerations
Demo: Creating Constraints
Triggers
Introduction
INSERT Triggers
DELETE Triggers
UPDATE Triggers
How an INSTEAD OF Trigger Works
Nested Triggers
Recursive Triggers
Demo: Creating Triggers
XML Schemas
Introduction
XML Schema Collections
Typed XML
Demo: Typed XML
Module 5 Review
|
Implementing Views
Introduction
What are Views?
Kinds of Views
Advantages
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
Optimizing Performance
Considerations
Indexed Views
Partitioned Views
Module 6 Review
Implementing Stored Procedures and Functions
Introduction
Stored Procedures
Creating Stored Procedures
Guidelines
Altering & Dropping Stored Procedures
Parameterized SP's
Using Input Parameters
Using Output Parameters/Return Values
Demo: Creating a Parameterized SP
Functions
Types of Functions
Scalar Functions
Inline Table-Valued Functions
Multi-Statement Table-Valued Functions
Demo: Creating Functions
Error Handling
Structured Exception Handling
Guidelines
Demo: Handling Errors
Execution Context
Execution Context
EXECUTE AS
Extending Impersonation Context
Demo: Controlling Execution Context
Module 7 Review
Implementing Managed Code in the Database
Introduction
Advantages
SQL Server CLR Integration
When to use Managed Code
Importing Assemblies
What are Assemblies
Importing an Assembly
Assembly Trust Levels
Demo: Import & Configure an Assembly
Creating Database Objects
Overview
Stored Procedures, Triggers, Functions
Aggregates and User-Defined Types
Demo: Managed Database Objects
Module 8 Review
Using Service Broker
Overview
Introduction
System Architecture
Conversation Architecture
Conversation Process
Security Architecture
Service Broker Objects
Creating Message Types
Creating Contracts
Creating Queues
Creating Services
Demo: Creating Service Broker Objects
Messages
Sending Messages
Receiving Messages
Demo: Sending and Receiving Messages
Module 9 Review
Course Closure |
