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?

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?

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?

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

You are implementing transaction log shipping for a database named DB1 from a server named SQL1 to a server named SQL2. Because DB1 is 100 GB in size, it is too big to transfer over the network in a reasonable amount of time. You need to minimize the impact on the network while you initialize the secondary database. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

You are creating an online catalog application that will display product information on the company Web site. The product data is stored in a SQL Server 2005 database. The data is stored as relational data but must be passed to the application as an XML document by using FOR XML. You test your application and notice that not all of the items matching your query appear in the XML document. Only those products that have values for all elements in the schema appear. You need to modify your Transact-SQL statement so that all products matching your query appear in the XML document. What should you do?

You are creating a stored procedure that will delete data from the Contact table in a SQL Server 2005 database. The stored procedure includes the following Transact-SQL statement to handle any errors that occur. BEGIN TRY BEGIN TRANSACTION DELETE FROM
Person.Contact WHERE ContactID = @ContactID COMMIT TRANSACTIONEND TRYBEGIN
CATCH DECLARE @ErrorMessage nvarchar(2000) DECLARE @ErrorSeverity int DECLARE @ErrorState int SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH; You test the stored procedure and discover that it leaves open transactions. You need to modify the stored procedure so that it properly handles the open transactions. What should you do?

You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your SQL Server 2005 computer is named SQL1. You create a stored
procedure named dbo.usp_GetPersonData to retrieve the data in the AdventureWorks database. You
create the endpoint by using the following code. CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = ‘/AWpersons’, AUTHENTICATION = (INTEGRATED), PORTS = (CLEAR), SITE =
‘SQL1’) FOR SOAP (WEBMETHOD ‘PersonData’
(NAME=’AdventureWorks.dbo.usp_GetPersonData’), BATCHES = DISABLED, WSDL = DEFAULT,
DATABASE = ‘AdventureWorks’, NAMESPACE = ‘http://Adventure-Works/Persons’) The first users
to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and discover that it is not responding. You need to modify the endpoint so that data is returned as expected.

What should you do?

You are responsible for importing data into SQL Server 2005 databases.

Your department is starting to receive text files that contain sales transactions from stores across the country. Columns in the data are separated by semicolons. You need to import the files into the sales database. What should you do?