You are a database administrator for a toy manufacturing company named NetFx.
The company stores all itsproduct-related data in a database named Netfx_data that resides on a SQL Server 2008 server named Server1 .
A table named Product_details exists in the database and contains the details of all the products manufacturedby the company.
The table contains a brief description of each product in the Prod_desc column that is definedas varchar(300) .
Users query the Product_details table by using SQL statements like the following:
SELECT Prod_name, Prod_id, Prod_desc
FROM Product_details
WHERE CONTAINS (Prod_desc, ‘ " Blue Toy" ‘);
You must ensure optimal performance of this query. What will you do to accomplish the objective?
A.
Create a clustered index on the Prod_desc column.
B.
Create a nonclustered index on the Prod_desc column.
C.
Create a full-text index on the Prod_desc column.
D.
Create a clustered index on the Prod_name column.
E.
Create an indexed view containing the Prod_name and Prod_id columns.
Explanation:
You should create a full-text index on the Prod_desc column. A full-text index should be created on a columnwhen you want to perform keyword searches on the column. In this scenario, users are specifying keywords to search for data in the Product_details table. Full-text indexes are designed for use in keyword-based queries. Afull-text index can be created on columns that contain the char , varchar , nvarchar , and varbinary(max) datatypes. Full-text indexes cannot be created on columns containing numeric values. You should not create a clustered index on the Prod_desc column because a clustered index will only enhancethe performance of a query when the column contains unique values. Aclustered index on a column with a varchar data type of 300 characters will not provide selectivity in the index. Clustered indexes should be createdon columns that uniquely identify a row. You should not create a nonclustered index on the Prod_desc column. Similar to a clustered index, anonclustered index does not provide good selectivity in the index if the column contains a large number of non-unique values. A nonclustered index will be beneficial when the column contains unique values such as numbers.A nonclustered index should be created on columns that are not covered by the clustered index. You should not create a clustered index on the Prod_name
column because the query in this scenario uses the Prod_desc column instead of the Prod_name column in the WHERE clause. A clustered index on the Prod_name
column will not affect the performance of the query. You should not create an indexed view containing the Prod_name and Prod_id columns. Creating an indexedview on the two columns will not affect the performance of the query. Indexed views are not used during full-textsearches. Creating afull-text index on the Prod_desc column will improve the performance in this scenario.Objective:
Installing and Configuring SQL Server 2008Sub-Objective:
Configure full-text indexing.References:
TechNet > TechNet Library > Full-Text Search Introduction TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData > Full-Text Search > Full-Text Search Concepts > Querying SQL Server Using Full-Text Search > Searchingfor Specific Word or Phrase (Simple Term)