Home / Courses / Microsoft Technology / 50439B: Basics of Transact SQL with SQL Server 2008 R2

SQL

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

Course Registration

 

3 days
$1,425.00

 

Course Outline

 

Request