You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Projects. The CK_Projects database contains a table named Documents that contains an XML column named DocLocation. Domain.com users complain that queries against the DocLocation column are processed very slowly. You want to improve the performance of queries that are run against the DocLocation column by creating an index on the column.
What should you do?
A.
Create a primary index on the primary key of the Documents table and secondary index on the DocLocation column.
B.
Create a clustered index on the primary key of the Documents table and secondary index on the DocLocation column.
C.
Create a nonclustered index on the primary key of the Documents table and primary index on the DocLocation column.
D.
Create a clustered index on the primary key of the Documents table and primary index on the DocLocation column.
Explanation:
You can create XML on XML data type columns to index all tags, values and paths in the column. This will improve query performance. The first XML index must be a primary index and the Documents table must have a clustered index on the primary key. A clustered index determines the physical ordering of the rows in a table and is required as the primary key is used to correlate XML index rows with the rows in the table.
Incorrect Answers:
A: You cannot create a unique index, clustered index or nonclustered index but not a primary index on a non-XML column. You must create a clustered index on the primary key as the primary key is used to correlate XML index rows with the rows in the table. Also, the first XML index must be a primary index. Secondary indexes can be created once the primary index has been created.
B: The first XML index must be a primary index. Secondary indexes can be created once the primary index has been created.
C: You must create a clustered index on the primary key as the primary key is used to correlate XML index rows with the rows in the table. A clustered index determines the physical ordering of the rows in a table while a nonclustered index creates a logical ordering.
Reference:
Microsoft SQL Server 2005 Books Online (2006), Index: indexes [SQL Server], creating Microsoft SQL Server 2005 Books Online (2006), Index: indexes [SQL Server], XML Microsoft SQL Server 2005 Books Online (2006), Index: indexes [SQL Server], clustered
Microsoft SQL Server 2005 Books Online (2006), Index: indexes [SQL Server], nonclustered