50439B: Basics of Transact SQL with SQL Server 2008 R2

Module 1: The ToolsetsLessons
- SQL Server Management Studio
- SQLCMD Utility
- Business Intelligence Development Studio (BIDS)
- SQL Profiler
- Bulk Copy
- Adventure Works Sample Database
- Data Definition Language
Lab : The Toolsets
- 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
Module 2: Data Types and New T-SQL Language FeaturesLessons
- SQL Server Data Types
- New Data Types
- Hierarchyid Data Type
- Spatial Data
- Date and Time
- FileStream Storage
- Grouping Sets
Lab : Data Types and New T-SQL Language Features
- Use Hierarchyid
- FILESTREAM Storage
- Use Merge Statement
Module 3: Single Table QueriesLessons
- Logical Query Processing
- Query Clauses (Listed in Logical Processing Order)
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- CASE
- NULLS
Lab : Single Table Queries
- Query Clauses
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- CASE
- NULLS
Module 4: Joining Multiple TablesLessons
- Normalization
- Old Style vs. New Style
- Cross Join
- Inner Join
- Outer Join
- Self Join
- Joining Multiple Tables
Lab : Joining Multiple Tables
- Cross Join
- Inner Join
- Outer Join
- Multiple Tables
Module 5: Subqueries, Common Table Expressions and Ranking FunctionsLessons
- Subqueries
- Self-Contained Subqueries
- Correlated Subqueries
- EXISTS
- Views
- Derived Tables
- Common Table Expressions
- Analytic Ranking Functions
Lab : Subqueries, Common Table Expressions and Ranking Functions
- Subqueries
- EXISTS
- Views
- Derived Tables
- Common Table Expressions
Module 6: IndexingLessons
- Understanding Execution Plans
- Table and Index Structures
- Syntax
- Clustered Index
- Non Clustered Index on Heap
- Non Clustered Index on Clustered Index
- Syntax
Lab : Indexing
- sys.dm_db_index_physical_stats
- Set Statistics IO
- WHERE Clause
- Narrow Indexes
- Column Uniqueness
- Column Ordering
Module 7: Query TuningLessons
- System Performance Analysis
- Query Analysis
- Database Engine Tuning Advisor
- Bookmark Lookup
- Statistics Analysis
- Query Design
Lab : Query Tuning
- Performance Monitor
- Dynamic Management Views
- SQL Profiler
- Database Engine Tuning Advisor
- Query Design
Module 8: Data Modification, Transactions and Error ProcessingLessons
- Modifying Data INSERT
- Modifying Data UPDATE
- Modifying Data DELETE
- Transactions
- Error Processing
- Debugging in SQL Server 2008
Lab : Data Modification, Transactions and Error Processing
- Inserting Values
- Use Bulk Copy
- Modify Data
- DELETE Data
- Transactions
- Try-Catch
Module 9: Stored Procedures and TriggersLessons
- Stored Procedures
- Triggers
Lab : Stored Procedures and Triggers
- Basic Stored Procedure
- Stored Procedure with Parameters (In)
- Stored Procedure with Parameters (In and Out)
- Triggers
- INSTEAD OF Trigger
Module 10: XMLLessons
- XML Introduction
- XML in SQL Server
- XML Data Type
- XML Indexes