2 run ddemofilesmod05setupcmd as an administrator to

2. Run D:\Demofiles\Mod05\Setup.cmd as an administrator to revert any changes.
3. In the virtual machine, on the taskbar, click SQL Server 2014 Management Studio .
4. In the Connect to Server window, in Server name , type MIA-SQL and then click Connect .
5. On the File menu, click Open , click Project/Solution , navigate to
D:\Demofiles\Mod05\Demo05.ssmssln , and then click Open .
6. On the View menu, click Solution Explorer .
7. Open the 31 – Demonstration 3A.sql script file.
8. Follow the instructions contained within the comments of the script file.

MCT USE ONLY. STUDENT USE PROHIBITED
Developing Microsoft® SQL Server® Databases 5-17
Lesson 4
Designing Effective Nonclustered Indexes
Before you start to implement nonclustered indexes, you need to design them appropriately. In this
lesson, you will learn how to find information about the indexes that have been created and how to create
filtered indexes.
Lesson Objectives
After completing this lesson, you will be able to:
 Consider various methods for obtaining index information.
 Use filtered indexes.
Methods for Obtaining Index Information
You might require information about existing
indexes before you create, modify, or remove an
index. SQL Server provides many ways to obtain
information about indexes.
SQL Server Management Studio
SQL Server Management Studio offers a variety of
ways to obtain information about indexes. Object
Explorer lists the indexes that are associated with
tables. This includes indexes that users have created
and those indexes that relate to PRIMARY KEY and
UNIQUE constraints in cases where SQL Server has
created indexes to support those constraints.
Each index has a property page that details the structure of the index and the characteristics of its
operational, usage, and physical layout.
SQL Server Management Studio also includes a set of prebuilt reports that show the state of a database.
Many of these reports relate to index structure and usage.
System Stored Procedures and Catalog Views
The sp_helpindex system stored procedure returns details of the indexes that have been created on a
specified table.
SQL Server provides a series of catalog views that provide information about indexes. Some of the more
useful views are shown in the following table.
System view Notes
sys.indexes Index type, filegroup, or partition scheme ID, and the
current setting of index options that are stored in metadata
sys.index_columns Column ID, position within the index, type (key or nonkey),
and sort order ( ASC or DESC )
sys.stats Statistics associated with a table, including statistic name
and whether it was created automatically or by a user

MCT USE ONLY. STUDENT USE PROHIBITED
5-18 Advanced Indexing
System view Notes
sys.stats_columns Column ID associated with the statistic
Dynamic Management Views
SQL Server provides a series of dynamic management objects that contain useful information about the
structure and usage of indexes. Some of the most useful views and functions are shown in the following
table.