You are a database developer on an instance of SQL Server 2008. You have an EventMaster table defined using the following statement:
CREATE TABLE EventMaster (
EventID int PRIMARY KEY,
EventDesc varchar(50) NOT NULL,
Provider varchar(30),
EventDate datetime,
Cost money,
MaxSeats int);
You currently have a clustered index on the table’s primary key, and a nonclustered index on the Provider column.
Rows are frequently added to the EventMaster table as new events are scheduled. Your booking representatives frequently query the EventMaster table to return the event description and cost of events provided by specific providers within a given time range. You want to optimize query performance.
Which action should you take?
A. Modify the nonclustered index to include EventDate.
B. Create a filtered index on Provider and EventDate.
C. Create an indexed view on EventMaster.
D. Create a clustered index on EventDesc.
Correct Answer: A
Explanation/Reference:
To improve query performance, indexes should be created on the columns that are frequently referenced in a WHERE clause. In this scenario, queries are executed to search for the events offered by specific providers during specific date ranges. Therefore, a composite nonclustered index on these two columns is likely to improve query performance.
You should not create a clustered index on EventDesc because the EventMaster table already contains a clustered index. A clustered index defines the order in which rows in a table are physically stored. Each table can have only one clustered index. When defining a primary key, SQL Server automatically creates a clustered index on the primary key column or columns if a clustered index does not exist, unless you specify otherwise. In this scenario, a clustered index already exists on the EventID column. Therefore, you cannot create another clustered index on the EventDesc column.
You should not create an indexed view on the EventMaster table because the scenario did not state that a view was defined. An indexed view is created by creating a unique clustered index on a view. This can often increase the view’s performance. In a view with a clustered index, the result set is stored in the database, similar to storing a table with a clustered index. This reduces the overhead of generating a result set each time the view is referenced.
You should not create a filtered index on Provider and EventDate. A filtered index is a nonclustered index that is defined including a WHERE clause to optimize the index for queries that access specific subsets of the data.
