SQL Server 2005 Implementing a Database - SQ5D


Description

Course Outline
This course covers complete implementation procedures including creating data types and tables, using constraints, triggers, and XML schemas. Basic knowledge of Transact-SQL is recommended along with some database design experience. Completion of this course provides the skills necessary for understanding key technologies in database implementation and combined with ATS's SQL Server 2005 Writing Queries and SQL Server 2005 Maintaining a Database will prepare you for the Microsoft MCTS exam 70-431.

Benefits
• Gain technical mastery of database administration.
• Increase your IT professional credibility.
• Enhance employment opportunities with your IT knowledge


Module 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


Module 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

 


Module 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


Module 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


Module 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


Price £980 (Bundle of 5)

Complementary Courses
SQL Server 2005 Maintaining a Database
SQL Server 2005 Writing Queries

<<Back <<Contact Us