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 small as 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 to
improve 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 consists of 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 contains a 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 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 nonclustered 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 nonclustered 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 the primary 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.
Filtered nonclustered indexes
Filtered indexes for SQL Server were introduced in SQL Server 2008. Put simply, filtered indexes are nonclustered indexes that have the addition of a WHERE clause. Although the WHERE clause in a filtered index allows only simple predicates, it provides notable improvements over a traditional nonclustered index. This allows the index to target specific data values – only records with certain values will be added to the index – resulting in a much smaller, more accurate, and more efficient index.
The basic syntax for creating a filtered index is:
CREATE NONCLUSTERED INDEX
ON ()
WHERE ;
GO