MCSE 2000 Elective: SQL Server 2000 Implementing Database Design

• 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

• Overview
• 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
Total Time: 111 Minutes

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

• Overview
• Implement Primary Key Constraint
• Duplicate Key Values
• Multi-Column Key
• Unique Constraint
• Add Unique Constraint

Section F: Foreign Key Constraint

• Overview
• Foreign Key Rules
• Cascade Update

SESSION 3
Total Time: 107 Minutes

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
Total Time: 104 Minutes

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

• Introduction
• 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

• Introduction
• 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
Total Time: 105 Minutes

Section A: Index Statistics

• Overview
• 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

• Overview
• Event Classes
• Run Profiler
• Tracefile Properties
• Index Tuning Wizard
• Analysis
• Trace Replay
• Access Query Analyzer
• Overview
• Set Processor Usage
• Query Governor

Section D: Views

• Overview
• View Details
• Create View
• Using Views
• Updateable Views
• Alter View
• Insert Record
• Check Options
• Update & Delete Records

Section E: Linked Servers

• Overview
• 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
Total Time: 110 Minutes

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

• Overview
• 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
Total Time: 107 Minutes

Section A: Lock Manipulation

• Lock Hints
• Using Locks
• Deadlock Error
• Avoiding & Handling Deadlocks
• Locking Best Practices

Section B: Cursors

• Overview
• Cursor Types & Behavior
• Cursor Steps
• Modifying Data
• Using Cursor
• Update Data
• Cursor Best Practices

Section C: Stored Procedures

• Overview
• 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

• Overview
• Plan Trigger Use
• Trigger Functionality
• Inserted, Updated, Deleted Tables
• Using Triggers
• Testing Triggers
• Cascading Updates

SESSION 8
Total Time: 98 Minutes

Section A: Instead-Of Triggers

• Overview
• Using Instead-Of Triggers
• Create Trigger
• Nested Triggers
• Instead-Of vs. After

Section B: Data Movement

• Overview
• DTS Tools
• DTS Packages
• DTS Data Lineage
• DTS Import/Export Wizard
• Export to Flat File
• Import Data
• DTS Package Workflow

Section C: Replication

• Overview
• Replication Roles
• Replication Types
• Merge
• Create Merge Publication
• Subscriptions
• Updating Subscribers
• New Replication Features

Section D: Permissions & Roles

• Overview
• 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