You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com are the administration of a SQL Server 2005 database named Certkiller -DB01.
Certkiller -DB01 stores line items from the sales transactions from clients. The sales department processes 50,000 transactions daily. You have been informed by the CIO that the application requires a clustered index on the TransactionID column. You create a table that will support an efficient reporting solution that queries the transactions by date.
How can you accomplish this? (Choose all that apply)
A.
You need to map every partition to a filegroup.
Then you can access a different physical drive with every filegroup.
B.
You need to create a partitioning scheme that partitions the data by date.
C.
You need to place a nonclustered index on the date column n order to accomplish this.
D.
You need to ensure that a unique clustered index is added on the date column.
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 manage 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.