Course Introduction
Course Introduction
Creating Databases and Database Files
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 1 Review
Creating 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 2 Review
Using 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
Module 3 Review
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 4 Review
Implementing Data Integrity
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
Lesson 3: Triggers
Introduction
INSERT Triggers
DELETE Triggers
UPDATE Triggers
How an INSTEAD OF Trigger Works
Nested Triggers
Recursive Triggers
Demo: Creating Triggers
Lesson 4: XML Schemas
Introduction
XML Schema Collections
Typed XML
Demo: Typed XML
Module 5 Review
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: Creating Triggers
Introduction
XML Schema Collections
Typed XML
Demo: Typed XML
Module 6 Review
Implementing 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 7 Review
Implementing 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 Plan
Query Compilation
Forcing Recompilation
Demo - Execution Plan
Module 8 Review
Implementing 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
Execution Context
EXECUTE AS
Extending Impersonation Context
Demo: Controlling Execution Context
Deterministic and Nondeterministic Functions
Rewriting Stored Procedures as Functions
Module 9 Review
Implementing Managed Code in the Database
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
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
Using 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
Using Notification Services
Lesson 1: Using Notifcation Services
What is a Notifcation Service?
Architecture
Subscription Management
Event Collection
Notifcation Generation
Notification Formatting and Delivery
Lesson 2: Developing Notification Services Solutions
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
