Course Content
The Toolsets
- SQL Server Management Studio
- SQLCMD Utility
- Business Intelligence Development Studio (BIDS)
- SQL Profiler
- Bulk Copy
- Adventure Works Sample Database
- Data Definition Language
Lab
- Set Options in SSMS
- Create a Solution and Project
- Explore the Query Editor
- Examine Graphical Execution Plans
- Use Object Explorer
- Use SQLCMD Utility
- Create a Basic SSIS Package Using the Wizard
- Run a SQL Profiler Trace
Data Types and New T-SQL Language Features
- SQL Server Data Types
- New Data Types
- Hierarchy id Data Type
- Spatial Data
- Date and Time
- FileStream Storage
- Grouping Sets
Lab
- Use Hierarchyid
- FILESTREAM Storage
- Use Merge Statement
Single Table Queries
- Logical Query Processing
- Query Clauses
Lab
- Query Clauses
Joining Multiple Tables
- Normalization
- Old Style vs. New Style
- Cross Join
- Inner Join
- Outer Join
- Self Join
- Joining Multiple Tables
Lab
- Cross Join
- Inner Join
- Outer Join
- Multiple Tables
Subqueries, Common Table Expressions and Ranking Functions
- Subqueries
- Self-Contained Subqueries
- Correlated Subqueries
- EXISTS
- Views
- Derived Tables
- Common Table Expressions
- Analytic Ranking Functions
Lab
- Subqueries
- EXISTS
- Views
- Derived Tables
- Common Table Expressions
Indexing
- Understanding Execution Plans
- Table and Index Structures
- Syntax
- Clustered Index
- Non Clustered Index on Heap
- Non Clustered Index on Clustered Index
Lab
- sys.dm_db_index_physical_stats
- Set Statistics IO
- WHERE Clause
- Narrow Indexes
- Column Uniqueness
- Column Ordering
Query Tuning
- System Performance Analysis
- Query Analysis
- Database Engine Tuning Advisor
- Bookmark Lookup
- Statistics Analysis
- Query Design
Lab
- Performance Monitor
- Dynamic Management Views
- SQL Profiler
- Database Engine Tuning Advisor
- Query Design
Data Modification, Transactions and Error Processing
- Modifying Data INSERT
- Modifying Data UPDATE
- Modifying Data DELETE
- Transactions
- Error Processing
- Debugging in SQL Server 2008
Lab
- Inserting Values
- Use Bulk Copy
- Modify Data
- DELETE Data
- Transactions
- Try-Catch
Stored Procedures and Triggers
- Stored Procedures
- Triggers
Lab
- Basic Stored Procedure
- Stored Procedure with Parameters (In)
- Stored Procedure with Parameters (In and Out)
- Triggers
- INSTEAD OF Trigger
XML
- XML Introduction
- XML in SQL Server
- XML Data Type
- XML Indexes
