SQL Server 2005 Implementing a Database Short Course

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

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

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

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

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