You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The network contains a database server named Certkiller -DB01 that hosts a database named CK_Books. The CK_Books database has a table named SampleChapters that is used to store sample chapters from books published by Domain.com. The schema for the SampleChapters table is shown in the following exhibit:
The text for the sample chapters are stored in a column named SampleText. The SampleText column is defined as an xml data type column. The XML schema for the SampleText column is shown below:
<SampleChapter>
<BookTitle>title of book</BookTitle>
<Author>author of book</Author>
<ChapterText>
<ChapterTitle>title of chapter</ ChapterTitle >
<Intro>introduction or synopsis</Intro>
<Topic Title=”topic title”>topic text</Topic>
<Topic Title=”next topic title”>next topic text</Topic> </ChapterText>
</SampleChapter>
You need to create indexes that will optimize performance for search queries that are run against the SampleText column. You need to ensure best performance for a query that return the book title, author, and introduction of a specific sample chapter. The search query is shown below:
SELECT SampleText.value(‘SampleChapterBookTitle[1]’, ‘varchar’), SampleText.value(‘SampleChapterAuthor[1]’, ‘varchar’), SampleText.value(‘SampleChapterChapterTextChapterTitle[1]’, ‘varchar’),
SampleText.value(‘SampleChapterChapterTextIntro[1]’, ‘varchar’) FROM Samples
WHERE SampleID = @sampleID
You need to ensure that your solution meets Courseware Publisher’s technical and business requirements.
What should you do? (Choose all that apply.)
A.
Create a PROPERTY secondary XML index on the SampleText column.
B.
Create a VALUE secondary XML index on the SampleText column.
C.
Create a nonclustered index on the AuthorID column.
D.
Create a clustered index on the SampleID column.
E.
Create a nonclustered index on the SampleID column.
F.
Create a primary XML index on the SampleText column.
Explanation:
The SampleID column is the primary key column and will be used to locate chapter samples. You should create a clustered index on this column to improve search performance.
Keywords and concepts are stored in SampleText column which must hold XML data as the chapter samples must adhere to an XML schema. You need to use a PROPERTY secondary XML index which uses the SampleID and xml path and xml node values to create an index of the data. However, before you can create a secondary XML index, you must first create a primary XML index.
Incorrect Answers:
B: You need to use a VALUE secondary XML index as users will search for an attribute with a value such as <BookTitle>.
C: No searches are run against the AuthorID column; therefore you don’t need to index the AuthorID column.
E: You should create a clustered index on the SampleID column as this provides better performance.