You are employed as a database administrator at Hi-tech Company. The Hi-tech Company network consists of a single Active Directory domain named Hi-tech Company. You have received instruction from the network CIO to create a Web-based application to manage data aggregation for reports.
The Application that you create will connect to a SQL 2005 database named DB_Data and another page of your application has controls which are used to execute stored procedures in a database named DB_Sales. Hi-tech Company has an existing Service Broker connection between the DB_Data and DB_Sales databases.
You have received additional instruction to add two new message types to the existing service. In each database, you create message types named DBReport and DBResult. You are required to add the two new message types to the existing service. What should you do first?

Users of the sales application have been complaining about the time it takes to generate the TotalSalesPerDay report. The report is created from the SalesByDate XML data type column in the Sales tables. The TotalSalesPerDay report is fed by the TotalSalesPerDaySP stored procedure, which executes the following query:

SELECT SalesByDate.query(‘/Sales[//@reportDate = sql:variable("@today")]’) FROM Sales

How can you improve the performance on this query?

You work with a database named DB1, which is located on a SQL Server 2005 computer. You discover that a table scan on Table1 in DB1 causes a slow query. Table1 is a very large table that is used frequently.
You ascertain that the query contains the following statement. SELECT col1, col2 FROM table1 WHERE col3 = <value>. You need to provide maximum query performance. Table1 must remain available to users. What should you do?

Your company uses SQL Server 2005. Users report with increasing frequency that they receive deadlock error messages in an order processing application. You need to monitor which objects and SQL Server session IDs are involved when deadlock conditions occur.
You want information about each participant in the deadlock. What should you do?

Your department is responsible for creating a list of cusMikeer e-mail addresses from data in a SQL Server 2005 database. The list must include the date on which each cusMikeer was last contacted.
The data in the result set must have column names. The list must be ordered by date of last contact, with the most recent contact first. The LastContact column is stored as a datetime. The date should be displayed as MM/DD/YYYY. A coworker develops the following query.

SELECT email_address AS EmailAddress,
CONVERT(nvarchar, lastcontact, 101) AS LastContact FROM Contact
ORDER BY LastContact DESC
You test this query and find that the data is displayed in the following order.

EmailAddress LastContact
andrew@contoso.com 01/24/2003
marc@contoso.com 06/12/2005
stefan@contoso.com 07/13/2004

You need to modify the query so that the data is listed in the proper order. You want to achieve this goal without negatively affecting performance. What should you do?

You use a SQL Server 2005 database named DB1, which is located on a server named SQL1.

DB1 is in use 24?hours a day, 7?days a week. A recent copy of DB1 exists on a second server named SQLtest that

also runs SQL Server 2005.

You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed. A

workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists.

You need to analyze the workload file by using DTA. You must ensure maximum performance on SQL1 during

analysis. You must also ensure availability during the implementation of any recommendations suggested by the

DTA.

What should you do?

You use an order application named App1, which connects to a SQL Server 2005 database named DB1.

Because some actions take more than three seconds, users report slow performance when they use App1. You

want to identify those actions by using the Tuning template in SQL Server Profiler to trace the application.

You need to capture only those App1 actions in DB1 that have a response time of two seconds or more.

What should you do?

A new employee needs access to a SQL Server 2005 database that is located on a server named SQL1. You

create a login named ajones by using the following Transact-SQL statement.

CREATE LOGIN ajones WITH PASSWORD = ‘SQLServer$1’

The new employee reports that when he logs in, he receives the following error message:

"Login failed. The user is not associated with a trusted SQL Server connection."

You need to resolve the error and allow the new employee to gain access to SQL1.

What should you do?

TestKing.com uses SQL Server 2005. A user reports that an order processing application stopped responding during a transaction. You investigate and discover that the user’s session, which is session ID 85, is being blocked by an errant process. The session ID of the errant process is 101. You need to solve this blocking situation quickly so that the user can continue using the application without losing any work. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Your department is responsible for creating a list of customer e-mail addresses from data in a SQL Server 2005 database. The list must include the date on which each customer was last contacted. The data in the result set must have column names. The list must be ordered by date of last contact, with the most recent contact first. The LastContact column is stored as a datetime. The date should be displayed as MM/DD/YYYY. A coworker develops the following query. SELECT email_address AS EmailAddress, CONVERT(nvarchar, lastcontact, 101) AS LastContact FROM Contact ORDER BY LastContact DESC You test this query and find that the data is displayed in the following order. EmailAddress LastContact andrew@contoso.com 01/24/2003 marc@contoso.com 06/12/2005 stefan@contoso.com 07/13/2004 You need to modify the query so that the data is listed in the proper order. You want to achieve this goal without negatively affecting performance. What should you do?