You have a table that contains a string column. Most of the rows have the same value. You

need to create a multistep stored procedure that meets the following requirements: •
Accepts a text input parameter • Provides optimal performance for all input values • Has
only one step using the input parameter as a predicate What should you use?

You are designing a CLR stored procedure that will access a remote Web service. You
need to ensure that the CLR stored procedure can access the remote Web service. Which
two actions should you perform? (Each correct answer presents part of the solution. Choose
two.)

You are a database developer. You plan to design a database solution by using SQL Server
2008. A database contains a table named Customer. The structure of the Customer table is
as shown in the following table. The solution uses the following stored procedure to return
customer details. Users report that the stored procedure takes a long time to execute. You
need to optimize the performance of the stored procedure. What should you do?

You have an office in Tokyo and an office in New York. Each office contains a server that
runs SQL Server 2008 Enterprise. The New York office contains a central database that is
mirrored to the Tokyo office for high-availability. You need to recommend a solution that
enables users in the Tokyo office to generate reports locally. The solution must minimize
WAN utilization. What should you recommend?

You are a database developer. You plan to design a database solution by using SQL Server
2008. The database contains a table that is stored as a heap. You perform page-level
compression on the table. When data is added to the table by using BULK INSERT, the new
pages are not compressed. You need to ensure page-level compression for all data pages
when they are allocated to the table. Which query hint should you use?

You are a database developer. You plan to design a database solution by using SQL Server
2008. Your company has a main office in New York and several branch offices globally. All
the offices use databases on their local SQL Server 2008 servers to support trading
applications. A low-bandwidth WAN link connects the offices. The WAN link is subject to
interruptions. You plan to implement a reporting instance that will be deployed in the main
office. You need to design a solution to transfer data from all offices to the reporting
instance in the main office. You also need to ensure that the solution has minimum effect on
the performance of all trading servers. What should you do?

Your company is developing an application. The database platform for the application will
be vendor-independent. You need to ensure that the application automatically stores the
current date and time data when a record is inserted or updated, regardless of the database
platform. What should you do?

A database contains two tables named Table1 and Table1_Details. Table1_Details contains
details about the items in Table1. You need to ensure that when an item is removed from
Table1, all related items are removed from Table1_Details. You must achieve this goal by
using the minimum amount of Transact-SQL code. What should you do?

You need to design a database solution that meets the following requirements: • Supports
different types of databases • Provides reliable messaging between databases • Provides
data workload distribution across several databases Which SQL Server component should
you use?

You have a table that contains 10 million rows. You need to design a query that returns a
100,000- row sample dataset. The solution must minimize the query’s execution time. The
sample may contain inconsistent data. Which isolation level should you set to ON?