What type of index should you use?

You manage a database named Customers, which includes a table named Orders. The Orders table is
frequently queried, but only orders with a sales total of more than $1000.00 are required in the query. You want
to create an index to speed up these types of queries at the same time, ensuring the index is as smallas
possible. What type of index should you use?

A.
Non-clustered

B.
Filtered

C.
Clustered

D.
XML

Explanation:
A filtered index is used to filter out unwanted data in the index. These indexes are ideally suited toimprove
queries on data that is mostly NULL values. A filtered query only indexes the rows that have non-null data in
the indexed column. The filtered index would be defined as a non-clustered index. Filtered indexes consume
less space in the system, and also require less processing power to query. Moreover, filtered indexes are
mainly created on columns configured as sparse columns. In this case, the filter within the CREATE INDEX
statement could look something like WHERE SalesTotal > 1000. Answer C is incorrect. A clustered index is
organized as a B-tree structure. The index consistsof a root page, intermediate levels, and leaf levels. The leaf
level nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index
pages that hold index rows. Each index row containsa key value and a pointer. A clustered index is useful
when you want to optimize queries based
on ranges since it orders the data based on the clustered index. However, the clustered index would include all
of the rows and will never be smaller than a filtered index.
Answer A is incorrect. A nonclustered index has thesame B-tree structure as the clustered index. The index
consists of a root page, intermediate levels, and aleaf level. The leaf level of a nonclustered indexdoes not
contain the actual data. It contains pointers to the data that is stored in the data pages. A nonclustered index
does not physically rearrange the data. A non-clustered index can be used to improve performance of queries.
However, a non-clustered index would include all ofthe rows and will never be smaller than a filteredindex.
While a filtered index must be a non- clustered index, all non-clustered indexes do not include WHERE clauses
and are not filtered indexes. Answer A is incorrect. A nonclustered index has the same B-tree structure as the
clustered index. The index consists of a root page,intermediate levels, and a leaf level. The leaf level of a
nonclustered index does not contain the actual data. It contains pointers to the data that is stored in the data
pages. A nonclustered index does not physically rearrange the data. A non-clustered index can be used to
improve performance of queries. However, a non-clustered index would include all of the rows and will never
be smaller than a filtered index. While a filtered index must be a non- clustered index, all non-clustered indexes
do not include WHERE clauses and are not filtered indexes. Answer D is incorrect. The XML column provides
great ability to retrieve data from within the XML column using various query methods. To support the querying
of XML data, users can create indexes on these columns. There are four different types of XML indexes
supported by SQL Server 2005/2008. There is a single primary XML index and three different flavors of
secondary XML indexes. The primary XML index is a clustered index on an internal table known as the node
table that users cannot use directly from their T-SQL statements. The primary XML index basically contains
one row for each node in the XML instance.After theprimary XML index has been created, users can create
supplementary three kinds of secondary XML indexes.The secondary XML indexes help in specific types of
XQuery processing. These are called the PATH, PROPERTY, and VALUE indexes. An XML index is only
appropriate for XML data.