You work for a bank that uses a SQL Server 2005 database to store line items from customer banking transactions. The bank processes 50,000 transactions every day. The application requires a clustered index on the TransactionID column. You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What are the two ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A.
Place a nonclustered index on the date column.
B.
Add a unique clustered index on the date column.
C.
Map each partition to a filegroup, with each filegroup accessing a different physical drive.
D.
Create a partitioning scheme that partitions the data by date.
Explanation:
After you build a clustered index, you can create nonclustered indexes on the table. In contrast with a clustered index, a nonclustered index does not force a sort order on the data in a table. In addition, you can create multiple nonclustered indexes to most efficiently return results based on the most common queries you execute against the table. By using partitions, you can place a subset of a table or index on a designated filegroup. This capability lets you separate specific pieces of a table or index onto individual filegroups and effectively managae file input/output (I/O) for volatile tables. Additionally, as organizations collect more and more data and keep it longer and longer for analysis purposes, tables continue to grow larger and larger. Managing such massive tables can be difficult. With partitioning, however, you can segregate data within a table based on age.