16 tips on How to 70-431 Test Like a Badass [49 to 64]

Question No. 49

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?

A. Create DML triggers that query the inserted and deleted tables.

B. Create a stored procedure that calls the LTRIM and RTRIM built-in functions.

C. Create a Transact-SQL function that calls the LTRIM and RTRIM built-in functions.

D. Call the TRIM built-in function.

Answer: C


Question No. 50

You work in San Francisco at the main office of your company. You are responsible for managing a SQL Server 2005 database. The sales department wants a report that compares customer activity in the previous quarter between the main office in San Francisco and the branch office in Paris. They want the data sorted by surname and first name. You restore a recent backup of the Paris database onto your server. You write queries to build the report, ordering the data by the Surname and FirstName columns. You review the data and notice that the customer list from the Paris database is sorted differently. The sales department needs the revised data within 15 minutes for a presentation. You need to implement the fastest possible solution that ensures that the data from both databases is sorted identically. What should you do?

A. Use the Copy Database Wizard to copy the data in the Paris database to a new database with the same collation as the San Francisco database.

B. Use the SQL Server Import and Export Wizard to copy the data from the Paris database into new tables, specifying the same collation as the San Francisco database.

C. Modify the format file to specify the same collation as the San Francisco database. Import the table again.

D. Modify the query on the Paris database to use the COLLATE setting in the ORDER BY clause. In the query, specify the same collation as the San Francisco database.

Answer: D


Question No. 51

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?

A. Trace the Lock:Timeout event by using SQL Server Profiler.

B. Observe the SQLServer:Locks - Number of Deadlocks/sec counter by using System Monitor.

C. Trace the Lock:Deadlock event by using SQL Server Profiler.

D. Trace the Lock:Deadlock Chain event by using SQL Server Profiler.

Answer: D


Question No. 52

Your company has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?

A. Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.

B. Configure SQL2 as a remote server. Write the query on SQL1.

C. Configure SQL2 as a linked server to impersonate the remote login.

D. Configure SQL2 as a distributed server. Use pass-through authentication.

Answer: C


Question No. 53

Your company has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins. You need to write a distributed query that joins the data on SQL1 with the data on SQL2. What should you do?

A. Ensure that both SQL1 and SQL2 use the same login name as the security context for each server.

B. Configure SQL2 as a remote server. Write the query on SQL1.

C. Configure SQL2 as a linked server to impersonate the remote login.

D. Configure SQL2 as a distributed server. Use pass-through authentication.

Answer: C


70-431 exam guide

Question No. 54

The sales department and the finance department are located in different buildings. The two departments are connected by a 2?megabit network. Both departments use SQL Server 2005. The sales department records all sales activities in a database named DB1. DB1 contains 380 tables and has a size of 10 GB. The finance department has read-only access to four tables in DB1 for reporting purposes. The finance department runs queries throughout the day and needs the data to be as up to date as possible. Users in the finance department tell you that report execution is slow. To optimize network capacity, your department decides to provide copies of these four tables to the finance department. You need to provide copies of these tables. You want to achieve this goal by using the copy method that uses the least amount of network bandwidth. What should you do?

A. Mirror DB1 to the finance department. Configure mirroring for high-availability mode.

B. Establish daily transaction log shipping of DB1 to the finance department. Use the STANDBY option to make the shipped database accessible to reporting.

C. Establish daily snapshot replication to the finance department. Define a publication that contains the four tables as the only articles.

D. Establish transactional replication to the finance department. Define a publication that contains the four tables as the only articles.

Answer: D


Question No. 55

You are responsible for implementing maintenance jobs on a SQL Server 2005 database server. Certain jobs run every Sunday and other jobs run at the beginning of every month.

You need to schedule the jobs in the way that uses the least amount of administrative effort. What should you do?

A. Create a job schedule that runs every Sunday. Assign weekly tasks to this schedule. Create a second schedule that runs on the first day of every month. Assign monthly tasks to this schedule.

B. Create a job for each task that runs once a day. Use a Transact-SQL statement to check the date and day of the week. If the day is either a Sunday or the first day of the month, execute the code.

C. Create a job schedule that runs once a day. Assign jobs to this job schedule. If the day is either a Sunday or the first day of the month, execute the jobs.

D. Create a job for each task that runs once a week on Sunday. Add a second job schedule that runs the job on the first of the month.

Answer: A


Question No. 56

You discover that the msdb database on a SQL Server 2005 computer is corrupt and must be restored. Databases are backed up daily. The database backup files are written to a network share, but the file names do not clearly indicate which databases are in each file. You need to locate the correct backup file as quickly as possible. The first file in the list is named DB_Backup.bak.

Which Transact-SQL statement should you use?

A. RESTORE LABELONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

B. RESTORE HEADERONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

C. RESTORE VERIFYONLYFROM DISK = N\\Server1\Backup\DB_Backup.bak

D. RESTORE DATABASE MSDBFROM DISK = N\\Server1\Backup\DB_Backup.bak

Answer: B


Question No. 57

You manage a SQL Server 2005 database that contains a table with many indexes. You notice that data modification performance has degraded over time. You suspect that some of the indexes are unused. You need to identify which indexes were not used by any queries since the last time SQL Server 2005 started. Which dynamic management view should you use?

A. sys.dm_fts_index_population

B. sys.dm_exec_query_stats

C. sys.dm_db_index_usage_stats

D. sys.dm_db_index_physical_stats

Answer: C


Question No. 58

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?

A. Implement a stored procedure that writes data about schema changes to a log table.

B. Implement DDL AFTER triggers that write user and schema information to a log table.

C. Implement a DML INSTEAD OF trigger that writes data about schema changes to a log table.

D. Implement a DML AFTER trigger that writes data about schema changes to a log table.

Answer: B


70-431 simulations

Question No. 59

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?

A. Add a COMMIT TRANSACTION command to the CATCH block.

B. Remove the COMMIT TRANSACTION command from the TRY block.

C. Add a ROLLBACK TRANSACTION command to the CATCH block.

D. Add a ROLLBACK TRANSACTION command to the TRY block.

Answer: C


Question No. 60

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

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


A. Change the Picture column to an image data type.

B. Include the schema name with the table name.

C. Include the WITH SCHEMABINDING clause.

D. Select all columns by using the * notation.

Answer: BC


Question No. 61

You are a database developer for a company that manufactures bicycles.

You are creating a Web application that will retrieve data from a database named AdventureWorks. The application connects to a database server that is running SQL Server 2005. One of the functions of your application will allow shift supervisors to see how many bicycles were manufactured during the current shift. A user named Tom creates a stored procedure named usp_GetPersonData in the dbo schema. You are creating an HTTP endpoint that exposes a Web method named PersonData. This Web method allows client applications to access the stored procedure. You use the following statement to create the endpoint.

CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = '/AWpersons', AUTHENTICATION

= (INTEGRATED), PORTS = (CLEAR), SITE = 'SQL1') FOR SOAP (WEBMETHOD ''

(NAME=''), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = 'AdventureWorks',

NAMESPACE = 'http://Adventure-Works/Persons') You need to specify the correct WEBMETHOD and

NAME to create the endpoint. Which command should you use?

A. WEBMETHOD 'http://www.adventure-works.com /PersonData' NAME = 'AdventureWorks.dbo.usp_GetPersonData'

B. WEBMETHOD 'PersonData'NAME = 'AdventureWorks.dbo.usp_GetPersonData'

C. WEBMETHOD 'PersonData'NAME = 'AdventureWorks.Tom.usp_GetPersonData'

D. WEBMETHOD 'AdventureWorks.PersonData'NAME = 'AdventureWorks.Tom.usp_GetPersonData'

Answer: B


Question No. 62

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?

A. Change the ORDER BY clause as follows:ORDER BY CONVERT(nvarchar, lastcontact, 101) DESC

B. Remove the column alias from the LastContact column.

C. Change the ORDER BY clause as follows:ORDER BY CAST(lastcontact AS nvarchar(20)) DESC

D. Change the alias on the LastContact column.

Answer: D


Question No. 63

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings. Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance. You need to identify the most likely cause of the connection issues. What should you do first?

A. Ensure that port 1433 is open in your firewall.

B. Ensure that port 443 is open in your firewall.

C. Ensure that client computers connect by using Shared Memory protocol.

D. Ensure that the server is not paused.

Answer: A


Question No. 64

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?

A. Update all statistics on Table1 in DB1.

B. Use the CREATE STATISTICS statement in DB1 to create missing statistics on col3 of Table1.

C. Set the priority boost server option to 1.

D. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3) INCLUDE(col1,

col2) WITH (ONLINE = ON)GO

E. Execute the following statement.USE DB1GOCREATE INDEX index1 ON table1(col3, col2, col1)

Answer: D


Microsoft 70-431 Certification Sample Questions and Answers: https://www.braindumpsall.net/70-431-dumps/

P.S. New 70-431 dumps PDF: http://www.4easydumps.com/70-431-dumps-download.html