You need to design a table that will contain a monetary value. The value must support five
digits to the right of the decimal point. Which data type should you choose?

You are a database developer. You plan to create a database by using SQL Server 2008.
The database has a table named Employees. The table contains records of employees and
their managers. The table includes the EmployeelD and ManagerlD fields. The EmployeelD

values are unique. The value in the M anagerlD field is the employee I D of t he e mployee’s
m anager. A Web s ite r equires X ML formatted output of all managers and employees to
be displayed as a tree diagram. You need to produce the required output by querying the
database without using system stored procedures. What should you do?

You have a table that contains an XML column named XMLDatal. The column contains the
following nodes: • Title • Description • Author • Weight • NumPages You need to design a
stored procedure that will return only the title and description in a tabular result set. The
solution must be developed by using minimum amount of effort. How should you extract the
information?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a large table that has 20 million rows. The table contains the
following columns: • CustomerNumber • CompanyName • ContactFirstName •
ContactLastName The table currently has single-column nonclustered indexes on the
CustomerNumber, CompanyName, and ContactFirstName columns. An application uses
data from this table. The user interface of the application allows the usage of any one filter
from the following list of filters: • CustomerNumber and CompanyName • CompanyName •
ContactLastName • ContactLastName and ContactFirstName In all cases, the listed order
of the columns is the order in which they will appear in the WHERE clause that is
generated. You need to design an indexing strategy for this table, so that the query
optimizer can quickly perform an index seek when searching through the table data. What
should you recommend?

You have a server that runs SQL Server 2008 Standard. The server provides data storage
for a thirdparty contact management application. A service-level agreement for the
application does not allow for modifications to be made to the application or to the database
objects used by the application. You need to recommend a strategy to mitigate performance
issues. The strategy must minimize database growth. What should you recommend?

Your company has four offices. Each office contains a server that runs SQL Server 2008. All
corporate data is stored in a central location. You need to create a reporting solution. The
solution must meet the following requirements: • Minimize hardware costs • Minimize
administrative effort • Minimize the time it takes to generate the reports • Enable users to
run reports locally, even if a WAN link fails What should you do?

You have a server that runs SQL Server 2008 Enterprise. The server contains a 300-GB
database. At the end of every month, several reports are generated from the database.
Users report that it takes a long time to access data from the database while the reports are
being generated. You need to recommend a solution to improve the data access time while
the reports are being generated from the database. The solution must use a minimum
amount of administrative effort. What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server

2008. The database will contain a table named Claims. The Claims table will contain a large
amount of data. You plan to partition the data into following categories: • Open claims •
Claims closed before January 1, 2005 • Claims closed between January 1, 2005 and
December 31, 2007 • Claims closed from January 1, 2008 till date The close_date field in
the Claims table is a date data type and is populated only if the claim has been closed. You
need to design a partition function to segregate records into the defined categories. What
should you do?

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