You have been hired as an administrator of an SQL Server 2000 computer. The server contains a database named Assets. The database is configured as shown in the exhibit:
4 таблицы с PRIMARY KEY
Parts - PartsID,
Manufacturer - ManufacturerID,
PartsLocations - PartsID и ,LocationID
Locations - LocationID

Users of the database frequently require details about parts. Database users search for parts information using the following query:

SELECT Parts.SKU, Locations.Description, Manufacturer.Name,
PartsLocations.City, PartsLocations.LastInventoried
FROM Parts
INNER JOIN Manufacturer ON Parts.ManufacturerID= Manufacturer.ManufacturerID
INNER JOIN PartsLocations ON Parts.PartID= PartsLocations.PartID
INNER JOIN Locations ON Parts.LocationID= Loactions.LocationID
WHERE SKU = ?

You need to accelerate query response time. What should you do?

A. Create a parameterized stored procedure to retrieve the data.
B. Create a denormalized table that is maintained by triggers.
C. Use ad hoc queries to retrieve the data.
D. Create a nonclustered index on the primary key of each table.

Answer из Testking: D
Зачем делать nonclustered index on the primary key если при создании PRIMARY KEY создается unique index по умолчанию clustered
Мой ответ А для экономии на создании планов запроса
хотя ответ B приводит большему ускорению query response time

На экзамене мне как раз попался этот вопрос.
Долго решал, что выбрать ответил А.
Экзамен сдал с результатом 838.
По графику думаю, что ошибся на 3 вопросах из 45.


ad hoc queries - queries made while an application is running.