You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table. You use the following WHERE clause in a query: WHERE CustomerData.exist…

The correct answer is A. CREATE CLUSTERED INDEX CL_IDX_Customer. The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to…

Troubleshoot and Optimize Databases

Question

You have a table named Customers that has an XML column named CustomerData. There are currently no indexes on the table. You use the following WHERE clause in a query:

WHERE CustomerData.exist ('/CustomerDemographic/@Age[.>="21"]') = 1 You need to create indexes for the query. Which Transact-SQL statements should you use?

Options

ACREATE CLUSTERED INDEX CL_IDX_Customer
BCREATE PRIMARY XML INDEX PXML_IDX_Customer
CCREATE PRIMARY XML INDEX PXML_IDX_Customer
DCREATE CLUSTERED INDEX CL_IDX_Customer

How the community answered

(26 responses)

A
77% (20)
B
8% (2)
C
12% (3)
D
4% (1)

Explanation

The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL Server uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column. The first index on the xml type column must be the primary XML index. Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. Following are some guidelines for creating one or more secondary indexes: If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of the exist() method on XML columns in the WHERE clause of Transact-SQL. If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario when properties of an object are fetched and its primary key value is known. If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups, such as //author[last- name="Howard"], where <author> elements can occur at any level of the hierarchy. It also occurs in wildcard queries, such as /book [@* = "novel"], where the query looks for <book> elements that have some attribute having the value "novel".

Topics

#primary XML index#XML column indexing#clustered index#query optimization

Community Discussion

No community discussion yet for this question.

Full 70-433 Practice