Publisher: Microsoft

Version: 2005/8
Length: 2 day s
Course-ID: SX687GRB

Course Overview

This course focuses on the new techniques and facilities offered by SQL Server 2005/2008 over and above the earlier versions.

Target student

Business Analysts, Developers and SQL Server users updating their skills from SQL Server 2000 or earlier. The change from SQL Server 2000 to SQL Server 2005 introduced a number of powerful new features that can dramatically improve query performance. Several “Real World” examples exist where large dataset query performance has been improved by a factor of 120 times by the use of the new techniques available in SQL Server 2005/8.

Prerequisites

Experience with SQL Server 2000.


Management Studio
  • Replaces Enterprise Manager and Query Analyser
New Database Structures
  • Schemas
  • Synonyms
Working with New Data Types
  • Date, Time DateTime2, DateTimeOffset
  • Geography
  • Geometry
  • HierarchyID
  • Varchar(MAX), nVarChar(MAX)
  • VarBinary
  • XML
New Query Techniques
  • TOP with variables
  • INTERSECT and EXCEPT
  • Recursive queries and CTE’s
  • Ranking Functions
  • ROW_NUMBER()
  • RANK()
  • DENSE_RANK()
  • NTILE()
  • PARTITION BY
  • TABLESAMPLE
  • INSERT – New Functionality
  • OUTPUT, MERGE
  • ON DELETE and ON UPDATE
  • PIVOT and UNPIVOT
  • APPLY
Programming SQL Server with T-SQL
  • Error handling with Try-Catch
  • DDL Triggers
  • Partitioned Tables
Extending SQL Server Functionality
  • XML
  • Common Language Runtime
  • Filestream
SQL Server Manageability Features
  • Windows Powershell