Exhibit:

You use a SQL Server 2005 database that contains a table named Customers. The Customers table contains the columns displayed in the exhibit.

You create a view named v_Customer by using the following Transact-SQL statement.
CREATE VIEW v_CustomerASSELECT CustomerID, CompanyName, FirstName, LastName, PictureFROM Customers You want to create an index on the v_Customer view. Prior to creating the index, you need to modify the Transact-SQL statement. Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)

Exhibit:

TestKing.com is storing its customer data in a data warehouse.It includes three separate SQL Server 2005 tables for storing customer data. Each table is used to horizontally partition data that is migrated from a SQL Server 2000 installation to SQL Server 2005.
Data is partitioned as shown in the table displayed in the exhibit.

The database does not allow customers to opt-out of e-mail marketing offers. You need to add a new OptOutFlag column to each of the customer tables. You must ensure that this column is added to either all three customer tables or none of them. You must also ensure that the database is always in a consistent state. Which Transact-SQL batch should you use?

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?

TestKing.com 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.
exhibit 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?

Exhibit:

TestKing.com has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named TestKing4. TestKing4 is replicated to a database named DB1Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against TestKing4 in DB1Repl are very slow. You investigate and discover that only the clustered index of TestKing4 is replicated. All other indexes in DB1Repl are missing. You examine the TestKing4 article properties. The current TestKing4 article properties are shown in the exhibit.

You need to change the article properties so that all indexes of TestKing4 in DB1 are replicated when the subscription is reinitialized. Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.)

Leading the way in IT testing and certification tools, www.testking.com

Your company has two SQL Server 2005 computers named SQL1 and SQL2. A database named DB1 is located on SQL1. DB1 contains a table named Table1. Table1 is replicated to a database named DB1 Repl, which is located on SQL2. Full-Text Search is not being used. Users report that the queries they run against Table1 in DB1 Repl are very slow. You investigate and discover that only the clustered index of Table1 is replicated. All other indexes in DB1 Repl are missing. You examine the Table1 article properties. The current Table1 article properties are shown in the exhibit. (Click the Exhibit button.)
You need to change the article properties so that all indexes of Table1 in DB1 are replicated when the subscription is reinitialized.
Which two article properties should you change? (Each correct answer presents part of the solution. Choose two.)
Exhibit:

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?