Course Introduction
Module 01 - Creating a Database
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 01 Review
Module 02 - 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 02 Review
Module 03 - Understanding 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
Introducing XQuery
Xquery Basics
Expressions
Module 03 Review
Module 04 - 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 04 Review
Module 05 - Using Constraints
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
Module 05 Review
Module 06 - Triggers and XML Schemas
Lesson 1: Triggers
Introduction
INSERT Triggers
DELETE Triggers
UPDATE Triggers
How an INSTEAD OF Trigger Works
Nested Triggers
Recursive Triggers
Demo: Creating Triggers
Lesson 2: XML Schemas
Introduction
XML Schema Collections
Typed XML
Demo: Typed XML
Module 06 Review
Module 07 - Creating and Managing 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 07 Review
Module 08 - Building Stored Procedures
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
Execution Plans
What are Execution Plans?
Viewing Execution Plans?
Caching an Execution Plans
Query Compilation
Forcing Recompilation
Demo - Execution Plan
Module 08 Review
Module 09 - Creating and Using Functions
Lesson 1: Functions
Types of Functions
Scalar Functions
Inline Table-Valued Functions
Multi-Statement Table-Valued Functions
Demo: Creating Functions
Lesson 2: Error Handling
Structured Exception Handling
Guidelines
Demo: Handling Errors
Lesson 3: Execution Context
Introduction to Execution Context
EXECUTE AS
Extending Impersonation Context
Demo: Controlling Execution Context
Deterministic and Nondeterministic Functions
Rewriting Stored Procedures as Functions
Module 09 Review
Module 10 - Creating Managed Code
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 10 Review
Module 11 - Transactions and Locks
Managing Transactions and Locks
What Are Transactions?
What Are Locks?
Concurrency Control
Managing Transactions
Autocommit Transactions
Explicit Transactions
Implicit Transactions
The Recovery Process
Considerations
Restricted Statements
The Architecture of Locking
Concurrency Problems Prevented by Locks
Resources That Can Be Locked
Types of Locks
Lock Compatibility
Managing Locks
Session - Level Locking Options
Viewing Locking Info
Deadlocks
Module 11 Review
Module 12 - Understanding 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 12 Review
Module 13 - Notification Services
Lesson 1: Using Notification Services
Outline
Lesson 1: Introducing Notification Services
What is Notification Services?
Architecture
Subscription Management
Event Collection
Notification Generation
Notification Formatting and Delivery
Example
Lesson 2: Developing Notification Services Solutions
Overview
Configuring an Instance
Demo - Viewing a Configuration File
Defining an Application
Demo - Viewing an ADF File
Managing Notification Services
Demo - Managing Notification Services
Creating Subscriptions
Demo - Subscription Management Application
Defining Event Providers
Best Practices
Module 13 Review
Course Closure
