MS SQL Server Online Training

Part 1

Chapter 1: SQL SERVER OVERVIEW

• Understanding Client Server Architecture
• Introducing the Tools
• Selecting an Edition
• Creating Sample Databases

Chapter 2: USING MANAGEMENT STUDIO

• Arranging the Workspace
• Registering Servers
• Explaining Items in the Object Explorer
• Explaining the Database Properties Dialog
• Explaining the Columns Properties Dialog
• Continuing with Object Explorer Items
• Using the Query Editor

Chapter 3: DESIGNING RELATIONAL DATABASES

• Understanding Normalization
• Discovering Patterns
• Avoiding Over Complexity
• Managing Optional Data
• Creating Physical Keys
• Using the Database Diagramed Tool
• Final Comments

Part 2: Selecting Data

Chapter 1: WORKING WITH SINGLE TABLE QUERIES

• Understanding Query Flow
• Filtering Data with the Where Clause
• Columns, Expressions & Scalar Functions
• Using Case Expressions
• Working with Nulls & Coalesce
• Selecting Environment Functions
• Working with Dates & Times
• Parsing Strings
• Converting Data
• Sorting Data
• Topping Off the Query with Predicates

Chapter 2: MERGING DATA WITH JOINS & UNIONS

• Working with Inner Joins
• Using Left Outer Joins
• Multiplying Data with Cross Joins
• Referencing Self-Joins
• Working with Complex Joins
• Finding the Set Difference
• Stacking Data with Unions

Chapter 3: WORKING WITH SUBQUERIES

• Using Sub queries as Expressions
• Including Sub queries as Lists
• Adding Sub queries as Derived Tables
• Working with Correlated Sub queries
• Solving for X with Relations Division

Chapter 4: AGGREGATING DATA

• Using the New Windowing Functions
• Using Aggregate Functions
• Grouping a Data Set
• Nesting Aggregations
• Filtering Groups & Adding Grand Totals
• Generating Cross Tab Tables (Pivot)
• Calculating Cumulative Sums

Chapter 5: USING FULL-TEXT SEARCH

• Creating a Full-Text Index
• Searching for Words
• Using a Fuzzy Search to Search for Phrases

Chapter 6: SELECTING DATA THROUGH VIEW

• Creating Views
• Preventing Disappearing Rows
• Protecting from Schema Changes
• Nesting Views

Chapter 7: DISTRIBUTED QUERIES

• Linking to External Data Sources
• Executing Distributed Queries
• Looking at Where the Work Takes Place
• Using Pass Through Queries
• Controlling Distributed Transactions

Chapter 8: MODIFYING DATA

• Inserting Data
• Updating Data
• Deleting Data
• Using the Output Clause
• Final Comments & Credits

Part 3: Developing Solutions

Chapter 1: T-SQL & STORED PROCEDURES

• Introducing T-SQL
• Working with Variables
• Controlling Procedural Flow
• Examining SQL Server with Code
• Using Temp Tables
• Developing Dynamic SQL
• Introducing Stored Procedures
• Passing Parameters
• Returning Parameters
• Working with a Cursor
• Handling Errors
• Explaining the Raise Error
• Controlling Transactions

Chapter 2: TRIGGERS & AUDIT TRAILS

• Introducing After Triggers
• Using "Instead of" Trigger
• Disabling Triggers
• Using the Update ( ) Function
• Selecting Transaction Data
• Nesting & Recursion
• Enforcing Complex RI
• Auditing Data Changes

Chapter 3: USER-DEFINED FUNCTIONS

1. Creating Scalar Functions
2. Working with Inline Table-Valued Functions
3. Using Multi-Statement Table-Valued UDFs
4. Navigating a Hierarchy
5. Having Fun with Apply
6. Exploring Functions in Nordic

Chapter 4: INDEXING FOR PERFORMANCE

1. Introducing Indexes
2. Creating Indexes
3. Examining Query Execution Plans, Pt. 1
4. Examining Query Execution Plans, Pt. 2

Chapter 5: TRANSACTIONS, LOCKING & BLOCKING

1. Introducing ACID
2. Understanding Transactional Faults
3. Stepping through a Transaction
4. Exploring Locks
5. Working with Locks
6. Viewing Deadlocks
7. Using Snapshot Isolation

Chapter 6: INTRODUCTION TO OPTIMIZATION THEORY

1. Deconstructing the Database System
2. Walking through Optimization Theory
3. Optimization Theory Continued
4. Final Comments & Credits