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 responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month.
You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

Your company uses SQL Server 2005. Users report that report execution is slow. You

investigate and discover that some queries do not use optimal execution plans.
You also notice that some optimizer statistics are missing and others are out of date. You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use? (Each correct answer presents part of the solution. Choose two.)

A support engineer reports that inserting new sales transactions in a SQL Server 2005 database results in an error. You investigate the error. You discover that in one or the databases, a developer has accidentally deleted some data in a table that is critical for transaction processing.

The database uses the full recovery model. You need to restore the table. You need to achieve this goal without affecting the availability or other data in the database. What should you do?

You are moving a SQL Server 2005 database named Timesheet to a new database server. The Timesheet database is used by the TimeEntry application.
You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. What should you do?

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your

corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance.
You need to identity the most likely cause of the connection issues. What should you do first?

Your company has two SQL Server 2005 computers named SQL1 and SQL2. A user in your company named Eric writes many ad hoc queries against the company databases. Eric has access to the CusMikeer database on SQL1.

He does not have access to the Sales database on SQL2. You need to ensure that Eric can write queries that join information from both servers.
What should you do first?

Application developers in your company create an assembly that contains a CLR function. This CLR function reads data from a spreadsheet, performs some calculations, and returns the data to a SQL Server 2005 computer. You need to register the assembly with SQL Server 2005 by using the CREATE ASSEMBLY statement and the least privileged security’ permission set.

Which permission set should you use?

You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server. Which two protocols can you use to achieve this goal?

(Each correct answer presents a complete solution. Choose two.)

Your SQL Server 2005 database contains a table that has 500?million rows of data. Some of the data is

historical and some is current.

You need to partition the data on a single server to increase performance and optimize maintenance.

What should you do?