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?

You are planning to import customer data from the Customer table and the Country table in your SQL

Server?2000 database into a SQL Server 2005 database.

You want to ensure that during the import process, each value in the CountryCode column of the Customer table

has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database.

You define a foreign key between these two tables.

Thus, referential integrity will guarantee that the import routine fails if there are any records where a

CountryCode value does not exist in the Country table but does exist in the Customer table.

You need to ensure that the import process does not fail if records are encountered that do not exist in the

Country table.

What should you do?

You discover that the schema changes that were recently made to your SQL Server 2005 database have caused

your Web site to stop functioning. It is unclear who made the changes.

Your company now mandates that all changes to the database schema be tracked.

You need to implement a mechanism that will track schema changes in your database.

What should you do?

You are creating a view to join the Customers and Orders tables in a SQL Server 2005 database.

You need to ensure that the view cannot be affected by modifications to underlying table schemas. You want to

accomplish this goal by using the least possible amount of overhead.

What should you do?

You are responsible for maintaining a SQL Server 2005 database. Business analysts in the company routinely

use a view named v_CustomerSales to join the Customers and Sales tables in the database. They use the view to

aggregate total sales by customer by month.

You need to increase the performance of the view.

What should you do?

You use a SQL Server 2005 computer that was installed with the default settings. A database named DB1 is

located on the server.

Due to the recent failure of a hard disk controller, you decide to check the integrity of DB1. You create a

maintenance task named CheckDB1 that uses default settings. Because this check must not interfere with user

transactions, you schedule it to run over the weekend.

The following Monday, you need to retrieve the outcome details of the CheckDB1 maintenance plan.

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

You manage a SQL Server 2005 computer that was installed using default settings. After a power failure, the

SQL Server (MSSQLSERVER) service on your database server does not start.

You need to find out the cause of the problem.

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

Your company has a server named SQL1 that is dedicated to SQL Server 2005. SQL1 is configured for SQL

Server and Windows Authentication mode. Using Windows Authentication, you have sysadmin privileges on

SQL1.

All users who work with SQL1 report that all applications that use SQL1 stop responding. Your supervisor asks

you to investigate the problem. In Task Manager, you notice that CPU usage is at 100 percent. You try to connect

to SQL1 by using SQL Server Management Studio. SQL1 does not respond.

You need to connect to SQL1 to diagnose the problem.

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