SQL Server 2000 Implementing Data Base Design - SQ2D

Description
The SQL Server 2000 Implementing Database Design training course from ATS will teach you how to design and implement a powerful, functional database with Microsoft SQL Server 2000. Expert instructor Wayne Snyder’s friendly presentation style makes this complex technical subject matter easy to understand. At the conclusion of this course you will understand the core techniques of implementing database design and will be prepared to pass exam #70-229 which provides core credit toward Microsoft Certified Database Administrator certification, elective credit toward Microsoft Certified Systems Engineer certification, and elective credit toward Microsoft Certified Solution Developer certification

Course Outline

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

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

Module 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

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

Module 5

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


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

Module 7

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

Module 8

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

 

Price £1100 (Bundle of 8)

Complementary Courses
SQ2A - SQL Server 2000 Admin
SQD1 - SQL Server for Developers Part 1
SQD2 - SQL Server for Developers Part 2
SQD3 - SQL Server for Developers Part 3
SQ2D - SQL Server DTS Data Transformation Services
SXM2 - XML SQL Server 2000

<<Back <<Contact Us