Basics of Transact SQL with SQL Server

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