Course 10364: Creating and Optimizing Indexes in Microsoft SQL Server 2008 R2

Introduction This two-hour course provides you with the knowledge and skills to create and optimize indexes in Microsoft SQL Server 2008 R2.

This course covers the following topics.

• Describing indexes
• Creating indexes
• Optimizing indexes
• Maintaining indexes
• Working with Spatial indexes
• Best practices for creating and optimizing indexes

This course helps you prepare for Exam 70-433: Microsoft SQL Server 2008, Database Development.

The prerequisites for this course are as follows:

• A good general knowledge of database concepts.
• Experience working with Windows and programming concepts.
• Understanding of server management concepts such as roles and tracing.
• A basic understanding of XML.

Objectives At the end of the course, students will be able to:

• Describe indexes.
• Create Indexes.
• Optimize Indexes.
• Maintain Indexes.
• Work with Spatial Indexes.
• Describe best practices for creating and optimizing indexes.

Modules & Lessons Course Overview Module Overview Navigation Overview Navigation Overview Course Information Course Information Creating and Optimizing Indexes in Microsoft SQL Server 2008 R2 Module Overview Overview of Indexes Overview of Indexes How SQL Server 2008 R2 Retrieves Data What Is a Heap? What Is a Clustered Index? What Is a Nonclustered Index? Traversing Data with Indexes What are Spatial Indexes? Self Test Creating Indexes Creating Indexes How to Create Indexes Creating Indexes on Multiple Columns Creating Unique Indexes Creating Partitioned Indexes Creating Indexes to Cover Queries How to Create Indexes on Computed Columns Self Test Optimizing Indexes Optimizing Indexes How SQL Server 2008 Handles Rows in Pages Understanding Page Splits Using the FILLFACTOR Option to Control Page Splits Using PAD_INDEX Option to Control Page Splits Self Test Maintaining Indexes Maintaining Indexes Understanding Index Statistics Data Fragmentation Managing Indexes Obtaining Information About Indexes The Database Engine Tuning Advisor Tool Self Test Spatial Indexes Spatial Indexes How SQL Server 2008 R2 Uses a Spatial Index What Are Tessellation Schemes? Restrictions on Spatial Indexes How to Create, Alter, and Drop Spatial Indexes How to Retrieve Spatial Index Information Self Test Best Practices Best Practices Lab: Creating and Optimizing Indexes in Microsoft SQL Server 2008 R2 Lab: Creating and Optimizing Indexes in Microsoft SQL Server 2008 R2 Scenario Exercise Information Launch the Lab Lab Review Module Summary Module Summary Glossary Glossary 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