Your company Web site includes a page that customers use to send feedback about the company and its

products. You use a SQL Server 2005 database to store the comments in the Comments column of a table named

Feedback.

You need to implement full-text searching so that you can run reports on the comments.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You are responsible for managing the database schema for an order entry application in a SQL Server 2005

database.

After a design review, a member of the development staff asks you to add a new column named CommissionRate

to the Product table. Because the actual commission rates for all products are not known at this time, each

product has a default commission rate of 12 percent. The rate can be modified as necessary. You add the new

column.

You need to configure the table to assign the default value as efficiently as possible.

What should you do?

You are modifying a table named Product in a SQL Server 2005 database. You want to add a new column

named FriendlyName to the Product table. A friendly name for each product will be stored in this column. The

table currently contains data. The sales department has not yet created a friendly name for each product.

FriendlyName is a required value for each product.

You want to add this new column by using the least amount of effort.

What should you do?

You are creating a SQL Server 2005 database for a mortgage company. The database will support a new

Web-based application that will handle up to 1,000 simultaneous users. This application must quickly display the

results of calculation-intensive operations, such as calculating mortgage payments and amortization schedules.

You need to ensure that the database processes calculations as quickly and efficiently as possible.

What should you do?

Your company has a SQL Server 2005 computer. You have been assigned the task of retrieving information

about a user who is currently logged in.

You need to create a function that returns scalar information about the activity time for a particular user.

What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Your company uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries.

The views are used to enforce your companys security policy of abstracting data.

Some of these views perform slowly. You create indexes on those views to increase performance, while still

maintaining the companys security policy. One of the views returns the current date as one of the columns. The

view returns the current date by using the GETDATE() function. This view does not allow you to create an index.

You need to create an index on the view.

Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You work with a SQL Server 2005 database that provides banking information for customers.

You want customers to see banking reports that combine data that is retrieved from the database with real-time

investment information that comes from a Web service provided by a third party. The investment information

must be current when the reports are executed.

You need to create the appropriate objects that support the reports.

What should you do?

You are responsible for managing a SQL Server 2005 database that stores sales information.

Many values in nchar columns in the database tables contain preceding or trailing spaces.

You need to implement a mechanism that selects the data from the tables without leading and trailing spaces.

Your solution must be available for reuse in Transact-SQL statements and views.

What should you do?

Your company uses a SQL Server 2005 database. This database contains a trigger named trg_InsertOrders,

which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a

customer exists in the Customers table before data is inserted into the Orders table.

You need to configure the trigger to prevent it from firing during the data import process. You must accomplish

this goal while using the least amount of administrative effort.

Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete

solution. Choose two.)

Your company uses a SQL Server 2005 database. Customer data from your trading partners is imported into

the Customers table every night.

You need to ensure that the customer record is updated if it already exists in the Customers table. If the record

does not exist, the data needs to be inserted into the Customers table.

What should you do?