Course 3182: Applying Advanced Query Techniques in Microsoft SQL Server 2005

Introduction In this 2-hour premium course, you will learn how to harness advanced query capabilities within your SQL Server™ 2005 Database solutions. Topics range from CTEs and Pivot Queries to working with XML Data. In the lab, you will write different kinds of queries and evaluate their execution plans.

Using these advanced query techniques will enable you to build more scaleable and responsive databases.

This course will also prepare you for exam 70-441 PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 and 70-442 PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005. Objectives At the end of the course, students will be able to:

• Evaluate CTEs
  • Describe types of CTEs
  • Create Nonrecursive and Recursive CTE Queries
  • Create Multi-Parent Hierarchy CTE Queries
• Evaluate Pivot Queries
  • Describe syntax of a Pivot Query
  • Describe a Pivot Query on an Open Schema
  • Describe an Unpivot Query
• Evaluate Ranking Queries
  • Define Types of Ranking Queries
  • Explain Syntax of a Ranking Query
  • Use Ranking Queries
• Work with XQuery
  • Write Queries Using XQuery
  • Create Parameterizing XQueries
  • Use Path Expressions
  • Use XQuery in the WHERE Clause of a DML Statement
• Overview of Strategies for Converting Data Between XML and Relational Forms
  • Create a Data Conversion Strategy
  • Convert Relational Data into XML Data
  • Convert XML Data into a Rowset

Modules & Lessons Course Overview Module Introduction Navigation Overview Lab Information Designing a Server-Side Solution for SQL Server 2005 Evaluating Advanced Query and XML Techniques Module Introduction Evaluating CTEs Lesson Introduction What Is a CTE? Types of CTEs Syntax of a Nonrecursive CTE Query Syntax of a Recursive CTE Query Writing a CTE Query for a Multi-Parent Hierarchy Common Issues When Querying Hierarchical Data Self Test Evaluating Pivot Queries Lesson Introduction What Is a Pivot Query? Syntax of a Pivot Query Writing a Pivot Query on an Open Schema Writing an Unpivot Query Self Test Evaluating Ranking Queries Lesson Introduction Types of Ranking Queries Syntax of a Ranking Query Guidelines for Using Ranking Queries Self Test Overview of XQuery Lesson Introduction Syntax for Writing Queries Using XQuery Guidelines for Parameterizing XQueries Guidelines for Using Path Expressions Guidelines for Using XQuery in the WHERE Clause of a DML Statement Self Test Overview of Strategies for Converting Data Between XML and Relational Forms Lesson Introduction Process of Creating a Data Conversion Strategy Creating a Data Conversion Strategy Guidelines for Converting Relational Data into XML Data Guidelines for Converting XML Data into a Rowset Self Test Lab: Evaluating Advanced Query and XML Techniques Lesson Introduction Scenario Exercises Lab Review Module Summary Glossary Module Introduction System Requirements To view this course, you need:

• A Pentium II, 256 MB RAM with a processor speed greater than or equal to 400 MHZ
• Microsoft® Windows® 2000 or higher
• Microsoft Internet Explorer 6.0 or higher
• Adobe Flash 7.0 or higher (1MB disk space needed to install)
• Microsoft Silverlight 4.0 or higher
• Microsoft Windows Media Player 7.0 or higher
• Microsoft XML Core Services 3.0 or higher
• A Super VGA monitor with minimum screen resolution 1024x768, with 16-bit color.
• A sound card, and either speakers or headphones (for multi-media audio).
• Internet bandwidth of 56K or faster. Broadband internet access is recommended.

For courses containing Virtual Server-based labs (courses in the IT Professional and Developer Catalog), you will need:

• Microsoft Virtual Server ActiveX control (1MB disk space needed to install)

Test your computer's capabilities to see if it supports this course. Accessibility Requirements The following software must be running on your computer in order to access this course with Screen Reader Software:

• Microsoft Internet Explorer 6.0 or higher
• Window-Eyes from GW Micro
• JAWS from Freedom Scientific
• MSAA enabled