You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance that contains a database named ABC_Travel that has a table named ABC_Travel that
keeps data about the entire catalogue of travels. The users at ABC.com search ABC_Tbl on the
Travel_Type column. The Travel_Type column is planned to contain data such as walk, bike, car,
train, bus, ferry, boat, spaceshuttle. However, at the moment the column either contains bus or
NULL.
After some investigation, to your horror, you find out that you have forgotten to build an index on
this table. After examining the hard drive on the server you realize that you cannot afford to use
indexes that require a lot of hard drive space.
What action should you take to avoid to lose your job?
A.
You should consider developing view that is indexed on the Travel_Type column.
B.
You should consider developing a unique index on the Travel_Type column.
C.
You should consider developing a default index on the Travel_Type column.
D.
Replace the NULL data with the appropriate data such as walk, bike, car, train, bus, ferry, boat,
spaceshuttle.
E.
You should consider developing an un-cluttered index on the Travel_Type column.
F.
You should consider developing a filtered index on the Travel_Type column.
G.
You should consider developing an non-clustered index on the Travel_Type column.
H.
You should consider developing a general index on the Travel_Type column.
Explanation:
A filtered index is an optimized nonclustered index, especially suited to cover queries that select
from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.
A well-designed filtered index can improve query performance, reduce index maintenance costs,
and reduce index storage costs compared with full-table indexes.
Filtered indexes can provide the following advantages over full-table indexes:
Improved query performance and plan quality
Reduced index maintenance costs
Reduced index storage costs