| Session 1
Section A: Introduction
· Overview
Section B: Normalization
· Basic Concept
· Redundancy
· Design Language
· Entity
· First Normal Form
· Primary Keys
· Second Normal Form
· Third Normal Form
· Relationships
· Business Rules
· Normalization Benefits
Section C: Creating a Database
· Database Objects
· Database Creation Process
· Transaction Log
· Create Option
· Create Syntax
· Query Analyzer
· Collate Order
· Enterprise Manager
· Recovery Considerations
Section D: Placing Database Files
· Storage Management
· Hardware RAID
· Filegroups
· Files & Filegroups
· Filegroups Maintenance
· Performance Considerations
Section E: Maintaining Databases
· Growth
· Alter Database
· Database Options
· Shrink Database
· Exam Database
· Space Estimation
· Transaction Log Size
· Rules of Thumb
· Log File Issues
· Extensive Log Activity
Section F: Basic SQL Data Types
· Create Table
· Standard Data Types
· Exact Numerics
· Integer Variables
· Approximate Numerics
· Character
· Variable vs. Fixed
· Unicode |
Session 2
Section A: Advanced SQL Data Types
· Date & Time
· Date Format
· Money
· Invalid Entry
· Binary
· GUID
· BLOB
· Special Data Types
Section B: Creating a Table
· Simply Syntax
· Null or Not Null
· Space Allocation
· Temporary Tables
· Primary Key Values
· Identity Column
· Using Identity Values
· Scope Identity
· Unique Identifier
· Newid Function
· Extended Properties
· Altering & Dropping Tables
Section C: Data Integrity Concepts
· Types
· Data Integrity
· Entity Integrity
· Domain Integrity
· Referential Integrity
Section D: Data Integrity Implementation
· Enforcement
· Constraints
· Generic Constraints
· Defaults
· Check Constraints
· Table vs. Column Constraint
· Column Constraint
· Alter Constraints
· Constraint Options
Section E: Primary Key Constraint
· Implement Primary Key Constraint
· Duplicate Key Values
· Multi-Column Key
· Unique Constraint
· Add Unique Constraint
Section F: Foreign Key Constraint
· Foreign Key Rules
· Cascade Update |
Session 3
Section A: Select Statement
· Basic Select
· Special Identifiers
· 4-Part Naming Convention
· Select Order
· Where Clauses
· Comparison Operators
· Range of Values
· Values in a List
· String Pattern Matching
· String Comparison Operator
· Like Operator
· Not Like Operator
· Null Checking
· Logical Operator
· Not, And, Or Operators
· Dynamic SQL
Section B: Formatting Result Sets
· Sorting
· Order By
· Eliminating Duplicates
· Order By with Distinct
· Column Alias
· Using Literals
Section C: Summarizing Data
· Aggregate Functions
· Aggregate Examples
· Count Distinct
· Group By
· Group By Having
· Having with Aggregates
· Rollups
· Cube
Section D: Functions & Set Options
· System Functions
· Set Options
· Scalar Functions
· Object Properties
· Convert Function
· String Functions
· GetDate Function
· Date & Time Functions
· Row Count Set Option
Section E: Inner Joins
· Joins
· GUI Joins
· Inner Join
· Alias Table Names
· Join Result Options
· Multiple Table Joins |
|
Session 4
Section A: Outer Joins, Cross Joins & Unions
· Outer Joins
· Left & Right Outer Join
· Cross Join
· Self-Referencing Table
· Self-Reference Outer Join
· Denormalizing
· Unions
Section B: Subqueries
· Rules
· Nested Subquery
· Multiple Value Nested Subqueries
· Distinct Subqueries
· Correlated Subqueries
· Complicated Subqueries
Section C: Insert, Delete & Update
· Insert Statement
· Defaults
· Insert Select
· Truncate Table
· Deleting Records
· Updating Records
· Update with Join
Section D: Indexing
· Heap vs. Clustered
· Data Access
· Clustered Index
· Why Clustered Indexes?
· Non-Clustered Indexes
· Why Non-Clustered Indexes?
Section E: Indexes & Fillfactor
· Creating Indexes
· Computed Columns
· Maintenance Issues
· Full Pages
· Fillfactor
· Execution Plan with Index
· Aggregate with Index
· Clustered Index
Section F: Indexes & Fragmentation
· External Fragmentation
· Data Fragmentation
· Scan Density
· DBCC Index Defrag
· Dropping Index
· Index Hints
|
Session 5
Section A: Index Statistics
· Density
· Index Statistics
· Index Statistics Usage
· Updating Statistics
· View Statistics
· Set Statistic Properties
· Using Query Plan
· Update Statistics
· Force Index Usage
· Use Updated Statistics
· Update Statistic Commands
· Show Sysindexes
· Turn On Statistic
Section B: Query Optimization
· Slow Queries
· Query Plan
· Dual Indexes Usage
· And Operators
· Or Operators
· Aggregates
· Joins
· Merge Join
· Entity Relationship Diagram
· Verify Cost
· Force Index
· Search Arguments
· Like Clause
Section C: SQL Profiler
· Event Classes
· Run Profiler
· Tracefile Properties
· Index Tuning Wizard
· Analysis
· Trace Replay
· Access Query Analyzer
· Set Processor Usage
· Query Governor
Section D: Views
· View Details
· Create View
· Using Views
· Updateable Views
· Alter View
· Insert Record
· Check Options
· Update & Delete Records Section E: Linked Servers
· Query Types
· Setting up the Link
· Login
· Options
· Open Query
· RPC's
· Create Linked Server (Excel)
· Distributed Passthrough Queries
· RPC/Adhoc
· Union Query |
Session 6
Section A: Indexed & Distributed Views
· Indexed View Usage
· Scheme Bound View
· Indexed View
· Data Partitioning
· Partition View
· Create Data Partition
· Create Data Partition View
· Setting Up
· Modify Users View
· Insert, Update & Delete
· View Issues
Section B: Programming SQL
· Global Functions
· Local Variables
· Define a Local Variable
· Define Multi-Local Variables
· Assignment Select Statement
· Variable Value Assignment
· Control of Flow
· Multiple Statement
· While Loop
· Until & Break
· GoTo & WaitFor
Section C: Other Language Elements
· Simple Case Statements
· Searched Case
· Raiserror
· View Logs
· User Messages
· Validate Data
· XPLOG Events
· Commenting
Section D: Transactions
· Rollback
· Transaction Examples
· Partial Rollback
· Nested Transactions
· Avoid Nested
· Implicit
· Implicit Starters
· Error Checking
· Set Implicit On
· Restricted & Best Practices
· Linked Server & Remote Procedure
· Distributed Transaction
Section E: Locks & Lock Types
· Lost & Erroneous Transactions
· Non-Repeatable & Phantoms
· Benefits & Usage
· Lock Types
· Lock Process
· Intent Locks & Bulk Update
· Isolation Levels
· Locked Record Trace
· Set Lock Time Out |
| Session 7
Section A: Lock Manipulation
· Lock Hints
· Using Locks
· Deadlock Error
· Avoiding & Handling Deadlocks
· Locking Best Practices
Section B: Cursors
· Cursor Types & Behavior
· Cursor Steps
· Modifying Data
· Using Cursor
· Update Data
· Cursor Best Practices
Section C: Stored Procedures
· Create & Execute
· Using Stored Procedures
· Parameters
· Using Parameters
· Local Variables
· Default Parameter Values
· Output Parameters
· Error Checking
· Testing Stored Procedures
· Return Status
· Plan Sharing & Best Practices
Section D: User Defined Functions
· Function Types
· Calling Functions
· Schema Binding
· Determinism
· Function Syntax
· Calling Scalar Functions
· Using Functions
· Table Value Functions
· Calling Table Functions
· Multi-Statement Table
· Best Practices
Section E: After Triggers
· Plan Trigger Use
· Trigger Functionality
· Inserted, Updated, Deleted Tables
· Using Triggers
· Testing Triggers
· Cascading Updates |
Session 8
Section A: Instead-Of Triggers
· Using Instead-Of Triggers
· Create Trigger
· Nested Triggers
· Instead-Of vs. After
Section B: Data Movement
· DTS Tools
· DTS Packages
· DTS Data Lineage
· DTS Import/Export Wizard
· Export to Flat File
· Import Data
· DTS Package Workflow
Section C: Replication
· Replication Roles
· Replication Types
· Merge
· Create Merge Publication
· Subscriptions
· Updating Subscribers
· New Replication Features
Section D: Permissions & Roles
· Object Permissions
· Chain of Ownership
· Permissions & Chain of Ownership
· Row Level Security
· Assign Row Level Security
· Roles
· Understanding Roles
· Application Roles
Section E: XML
· Selecting
· For XML Auto
· For XML Raw
· For XML Explicit
· Open XML
· Additional Resources |