|
Module 1
Section A: Creating Databases
· Database Basics
· Installed Databases
· Sample Databases
· Database Objects
· Data and Log Files
· Pages and Extents
· Database Creation Process
· Transaction Log
· Create Database Syntax
· Space Estimation
· Log Size and Recovery
· Database Examples
Section B: Placing Database Files
· Storage Considerations
· Single vs. Multiple Data Files
· Hardware-Based RAID
· RAID 0
· RAID 1
· RAID 10
· RAID 5
Section C: Using Filegroups
· Files and Filegroups
· Default and Read-Only Filegroups
· Filegroup Maintenance
· Configuration Example
· Why Use Filegroups?
Section D: Finding Database Information
· Where to Look
· Using Object Explorer
· Database Properties
· Summary Filtering
· Reports
· Using Transact-SQL
Section E: Schemas
· Schema Namespaces
· Object Name Resolution
Section F: Database Snapshots
· Defined
· Restrictions
· Implementation
· Best Practices
· Creating a Snapshot
· Viewing and Updating Data
· Restoring Databases with Snapshots
Section G: Basic SQL Server Data Types
· CREATE TABLE Syntax
· Integer
· Exact and Money
· Approximate
· Character Data Types
· Space Allocation
· Datetime
· Date Formats
· Binary
· Global Identifier
· XML
· Special Data Types
|
Module 2
Section A: User-Defined Data Types
· Alias Data Types
· .NET Assemblies
Section B: Creating Tables
· Basic Syntax
· Allowing Null Values
· Identity Columns
· Unique Identifiers
· Rowversion Data Type
· Computed Columns
· Temporary Tables
· Table Variables
· Extended Properties
· Dropping and Altering Tables
Section C: Partitioning Tables
· Benefits
· Choosing Tables to Partition
· Three Partition Steps
· Partitioning Example
· Cost Benefits
· Index Partitions
· Three Partition Functions
· Switching Partitions
· Merging Partitions
· Splitting Partitions
Section D: Using XML
· Selecting for XML
· Using RAW mode
· Using XML Explicit
· XPATH
Section E: Shredding XML
· Three Basic Steps
· Flags, Column Patterns, and Edge Table
· XML Data Type
· FLOWR
· Query Method
· Value and Modify Methods
· Insert Statement
· Using the Value Method
· Using the Exist Method
· Other Queries
Section F: Indexing Concepts
· Pros and Cons of Indexing
· What Indexes Affect
· Table Scans
· Index Seeks
· Table Scan vs. Index Seek
· Choosing How Data is Accessed
|
|
Module 3
Section A: Index Structures
· Index Tables
· Sysindexes Table
· Types of Indexes
· Clustered Indexes
· Non-Clustered Indexes
· Uses for Non-Clustered Indexes
Section B: Creating Indexes
· Index Creation Rules
· Index Creation Options
· Include
· Dropping an Index
· Index Creation Hints
· Multicolumn Indexes
· Creating an Index
· Covering Indexes
· Creating Non-Clustered Indexes
· Getting Index Information
· Special Purpose Indexes
Section C: Index Fragmentation
· Fragmentation Overview
· Fixing Fragmentation
· Internal Fragmentation Page Fullness
· Too Full Pages
· External Fragmentation Page Fullness
· Setting Page Fullness
· SQL Server Profiler
· Setting Up Captures
· Capturing
· Analyzing Captures
Section D: XML Indexes
· XML Uses
· Types of XML Indexes
· Primary XML Indexes
· Secondary XML Indexes
· Creating XML Indexes
Section E: Data Integrity
· Types of Data Integrity
· Entity Integrity
· Domain Integrity
· Referential Integrity
Section F: Data Integrity Implementation
· Enforcing Data Integrity
· Constraints
· Defaults
· Using Constraints
· Check Constraints
· Using Check Constraints
· Multiple Constraints
· Using Multiple Constraints
· Alter Table Failures
Section G: PK and Unique Constraints
· Primary Key Constraints
· Using Primary Key Constraints
· Multicolumn Key Constraints
· Choosing Keys
· Unique Constraints
· Using Unique Constraints
· Multicolumn Unique Constraints
Section H: Foreign Key Constraints
· Foreign Keys
· Child Table Actions
· Parent Table Actions
· Foreign Key Rules
· Using Foreign Key Constraints
· Updating Foreign Key Constraints
· Disabling Constraints
· Business Rules
|
Module 4
Section A: After Triggers
· Triggers Defined
· Trigger Types
· Trigger Functions
· Inserted/Deleted Tables
· Update Statements
· Creating Special Trigger Functions
· Using Special Trigger Functions
· Other Trigger Functions
Section B: Instead Of Triggers
· Characteristics of Instead Of Triggers
· Creating Instead Of Triggers
· Inserting into Views
· DDL Triggers
· Nested and Recursive Triggers
· Trigger Comparison
· Instead Of Trigger Best Practices
Section C: XML Schemas
· Characteristics of XML Schemas
· Schema Options
· System Views
· Creating Schema Collections
· Validating Documents
Section D: Views
· Views Defined
· Characteristics of Views
· Creating Views
· Chain of Ownership
· Security Checks
Section E: Indexed/Partitioned Views
· Indexed Views
· Creating Indexed Views
· Using Indexed Views
· Partitioned Views
· Issues with Partitioned Views
Section F: Stored Procedures
· Characteristics of Stored Procedures
· Creating Stored Procedures
· Running Stored Procedures
· Late Binding
· Altering Stored Procedures
· Passing Parameters
· Making Parameters Optional
· Creating Output Parameters
· Using Output Parameters
· Testing Output Parameters
|
|
Module 5
Section A: User-Defined Functions
· Defining Functions
· Schema Binding
· Determinism
· Scalar Functions
· Calling Scalar Functions
· Using Scalar Functions
· Table-Valued Functions
· Replacing Stored Procedures
· Calling Table-Valued Functions
· Inline Functions
· Multi-Statement Functions
Section B: Handling Errors
· Try/Catch
· Transactions
· Using Try/Catch
· Inserting Into Tables
· Using Transaction Controls
· XACT_ABORT
Section C: Controlling Execution Context
· Execution Context
· Execute As
· Problems with Cross-Database Chaining
· Controlling Cross-Database Chaining
· Limitations of Cross-Database Chaining
Section D: Implementing Managed Code
· CLR Integration
· Assemblies
· Trust Levels
· Managed Objects
· Using CLR Integration
· Using Regular Expressions
Section E: Service Broker
· Characteristics of Service Broker
· Setting up Service Broker
· Adding Messages to Queues
· Processing Messages
|
|
