You are a database developer on an instance of SQL Server 2008. Your database contains the SalesHeader and SalesDetail tables as shown:
Correct Answer: B
Explanation/Reference:
This is really strange question.
First of all, if you have the database structure depicted on the picture above and run the query mentioned in the question, you will get another execution plan:
To get the execution plan shown in the question SalesHeader table should be a heap. So probably author added PK to the table and forgot to save it, as evidenced by an asterisk next to SalesHeader title.
Second, if you want to get ProductID and SpecialOfferID from SalesDetail table what is the point to join it with SalesHeader table?
We can achieve the same result by running the following query:
SELECT ProductID, SpecialOfferID
FROM SalesDetail
WHERE SpecialOfferID = 5;
And the last point, it is not the best option to create this index (as mentioned in the list of available choices):
CREATE NONCLUSTERED INDEX IX_SalesDetail_SpecialOfferID
ON SalesDetail (SpecialOfferID)
Fot this query:
SELECT ProductID, SpecialOfferID
FROM SalesDetail d
INNER JOIN SalesHeader h ON d.SalesOrderID = h.SalesOrderID
WHERE SpecialOfferID = 5;
it is better to create this index:
CREATE NONCLUSTERED INDEX IX_SalesDetail_SpecialOfferID_incl_ProductID_SalesOrderID
ON SalesDetail (SpecialOfferID) INCLUDE (ProductID, SalesOrderID)
For this query:
SELECT ProductID, SpecialOfferID
FROM SalesDetail
WHERE SpecialOfferID = 5;
it is better to create this index:
CREATE NONCLUSTERED INDEX IX_SalesDetail_SpecialOfferID_incl_ProductID
ON SalesDetail (SpecialOfferID) INCLUDE (ProductID)
You should not create a partitioned view over the table and use it in the query. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented by creating a view that queries several tables and combines the results using the UNION ALL operator. A partitioned view can improve performance and increase availability, but would not be applicable in this scenario. Partitioned views can also be used to query data across multiple servers and provide additional scalability.
You should not implement page-level compression for the SalesDetail table. Implementing compression would save storage space for the SalesDetail table, but this would not be applicable in this scenario. Page-level compression will not reduce the cost associated with the SalesHeader or SalesDetail table.
You should not rebuild statistics for both tables. There is no indication that statistics for the table are stale.
