You are hired as a database consultant at Domain.com. The Domain.com network contains a SQL Server database server named Certkiller -DB01. Certkiller -DB01 host a database named CK_Sales that stores line items from Domain.com’s online sales transactions. Domain.com processes about 40,000 transactions every day. An application that processes the sales transactions requires a clustered index on a column named TransID.
You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What should you do? (Each correct answer presents part of the solution. Choose TWO.)
A.
You should place a nonclustered index on the date column.
B.
You need to add a unique clustered index on the date column.
C.
You must map each partition to a filegroup, with each filegroup accessing a different physical drive.
D.
You should 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 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.