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.)

Your company uses SQL Server 2005. A user reports that an order processing application stopped responding

in the middle of an order transaction. The users SQL Server session ID is 54.

You need to find out if session?54 is blocked by another connection. If it is, you need to identify the blocking

session ID.

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

Your company has a server named SQL1 that runs SQL Server 2005 Enterprise Edition. SQL1 has 2 GB of

RAM, 1.6?GB of which are used by the default SQL Server database engine instance. The average data growth of

all databases combined is 100 MB a month.

Users state that report execution times are increasing. You want to assess whether more RAM is needed.

You need to use System Monitor to create a counter log that will help you decide whether to add RAM.

Which performance object should you add to the counter log?

Your database named DB1 is one of 15?databases located on a SQL Server 2005 computer named SQL1.

Recently, users who work with DB1 report that they experience long query execution times. You want to

reproduce and analyze this problem in a test environment that holds all copies of the production databases.

You need to record the workload for an entire day on SQL1 for later execution on the test server. You must ensure

that all events are captured without skipping any, even if SQL1 is under stress.

What should you do?