You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain three tables. The structure of the three tables is as shown
in the following table. You need to minimize disk space usage without altering the data
types in the tables of the database. What should you do?

You are designing a database that will be used for reporting purposes. You need to
minimize the data storage requirements and improve the application response time. What
should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table that has a column defined as a smallint data type. The
table is partitioned on the basis of the smallint column. The partition function, named
MyRangePF1, is a range-right function that has boundaries of 100 and 1,000. The table
must be altered to contain the following partitions: ·< 100 ·>= 100 and < 400 ·>= 400 and <
700 ·>= 700 and < 1000 ·>= 1000 You need to alter the partition function to provide the
required partitions. Which code fragment should you use?

You are designing a table that will store transactions for an online retailer. You anticipate
that the online retailer will have an average of 500,000 transactions per day. You need to
design a solution for data aging and archiving. The solution must minimize the amount of
time it takes to return query results for active data. What should you implement?

You have a database that has 20 large tables. All the tables have qualified indexes. As the
tables grow, you discover that queries that contain JOIN statements execute more slowly.
You need to recommend a solution to decrease the query response time and the I/O. The
solution must minimize hardware costs. What should you recommend?

You are designing a table to store date and time information for an application. The
application will be used by users in Europe, Asia, and North America. You need to add a
column that stores the date and time information for all users. The column must contain
information that can be used to identify the users’ local time zone. What should you include
in the design?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table named SaverPlans that stores information about
savings plans. The table contains a column named MinBalance that stores the minimum
balance for a given plan. You need to ensure that each new savings plan, for which the
MinBalance value is unspecified, has a minimum balance of 1,000 U.S. dollars. You want to
achieve this goal by using the minimum amount of cost. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains two tables named Orders and OrderDetails. There is also a data
warehouse containing a table named factSales. The factSales table has a de-normalized
structure and contains columns from Orders and OrderDetails. You plan to design a solution
that will extract all data modifications from Orders and OrderDetails and load them into
factSales. You have the following requirements: • The load operation is incremental and
runs daily. • The schema of the tables cannot be modified. • The history of each modification
is maintained for one month. You need to implement the solution by using the least amount

of coding and administrative effort. What should you do?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain a table to stage data from an external source. The imported
data will contain a field named TransactionTime. The TransactionTime field will use the
hh:mm:ss.[m] format (such as 12:55:30.1). You need to select the appropriate data type for
the TransactionTime field. You want to achieve this goal by incurring minimum amount of
storage cost. Which data type should you use?

You are designing a table that will contain the columns in the following table. You need to
add a column that will display the sum of the Subtotal, TaxAmt, and FreightAmt columns.
The solution must minimize the amount of storage space required to store the table. What
should you create?