You are the database administrator for your company. One of the databases that you maintain contains largevolumes of XML data.
An XML column appears in the WHERE clauses of most queries that users execute againstthe database.
The large binary objects (BLOBs) are shredded at runtime for each row of the query.
This processbecomes very expensive for the database server in terms of resources and performance.
In this scenario, you decide to create an index on the XML columns.
You need to ensure that you understand XML indexes.
Which statements about XML indexes are true? (Choose all that apply.)
A.
PATH, VALUE, and XML are types of secondary XML indexes.
B.
XML indexes exist in the same namespace as non-XML indexes.
C.
You can create only one primary XML index on an XML data type column.
D.
A primary key clustered index must exist before an XML index can be created on a table.
E.
An XML index can be created on an XML column in a database table or view.
F.
It is not necessary to have a primary XML index on an XML column to create a secondary XML index.
Explanation:
The following statements about XML indexes are true:
? XML indexes exist in the same namespace as non-XML indexes.
? You can create only one primary XML index on an XML data type column.
? A primary key clustered index must exist before creating an XML index on a table. An XML index is createdon a column of the data type XML.You can create two types of XML indexes, primary and secondary. Primary XML indexes shred the XML BLOB inthe XML data type column and save the XML BLOB as several rows of data. Secondary indexes are created onthe path, value, and property of the column. XML indexes exist in the same namespace as non-XML indexes, andyou can create only one XML index on an XML data type column. When you create an XML index, a clusteredindex on the primary key should always exist to ensure that if the table containing the XML column is partitioned,the primary XML index on the columns can also be partitioned based on the same partitioning scheme andpartitioning function. The option stating that PATH, VALUE, and XML are types of secondary XML indexes is incorrect. The three typesof secondary indexes are PATH, VALUE, and PROPERTY. XML is not a type of secondary XML index. The option stating that you can create an XML index on an XML column in a database table or view is incorrect.You can create an XML index on an XML column only in a database table. You cannot create an XML index onan XML column in a view. The option stating that it is not necessary to have a primary XML index on an XML column to create a secondaryXML index is incorrect. You must have a primary XML index on an XML column to create a secondary XML index.Objective:
Performing Data Management TasksSub-Objective:
Maintain indexes.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Development > Designing andImplementing Semistructured Storage (Database Engine) > Implementing XML in SQL Server > XML Data TypeVariables and Columns > Indexes on XML Data Type Columns