Exam 98-364 Topic 4 Question 118 Discussion

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 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 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.