Microsoft 70-431 Real Exam Questions

This Microsoft Technical Specialist (TS) exam, Exam 70-431: TS: Microsoft SQL Server 2005 – Implementation and Maintenance, became available in January 2006. This exam may contain performance-based testing elements.

1: 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.)
A.In SQL Management Studio, right-click the CheckDB1 maintenance plan. On the context menu, click View History. See the record for the plan named CheckDB1.
B.In SQL Management Studio, right-click the Activity Monitor. On the context menu, click View Processes. Look for a record that has "CheckDB1" in the Application column.
C.In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the SQLAgent.out text file.
D.In Windows Explorer, navigate to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. View the text file that has a name that starts with "CheckDB1_" followed by the execution year, date, and time.
Correct Answers: A D

2: Your company Web site includes a page that customers use to send feedback about the company and its products. You use a SQL Server 2005 database to store the comments in the Comments column of a table named Feedback. You need to implement full-text searching so that you can run reports on the comments. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Create a nonclustered index on the Comments column.
B.Execute the USE Master Transact-SQL statement.
C.Create a full-text catalog.
D.Create a full-text index on the Comments column.
Correct Answers: C D

3: Your company uses a SQL Server 2005 database. Customer data from your trading partners is imported into the Customers table every night. You need to ensure that the customer record is updated if it already exists in the Customers table. If the record does not exist, the data needs to be inserted into the Customers table. What should you do?
A.Create a FOR trigger.
B.Create an INSTEAD OF trigger.
C.Create an AFTER trigger.
D.Create a DDL trigger.
Correct Answers: B

4: You are responsible for managing the database schema for an order entry application in a SQL Server 2005 database. After a design review, a member of the development staff asks you to add a new column named CommissionRate to the Product table. Because the actual commission rates for all products are not known at this time, each product has a default commission rate of 12 percent. The rate can be modified as necessary. You add the new column. You need to configure the table to assign the default value as efficiently as possible. What should you do?
A.Create an INSERT trigger to assign the default value to each item in the table.
B.Create a CHECK constraint to validate the data and to assign the default value to each item in the table.
C.Create an UPDATE trigger to update the default value for each new item in the table.
D.Create a DEFAULT constraint to assign the default value specifying the WITH VALUES argument.
Correct Answers: D

5: 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?
A.Create CHECK constraints on the tables.
B.Create a DDL trigger to roll back any changes to the tables if the changes affect the columns in the view.
C.Create the view, specifying the WITH SCHEMABINDING option.
D.Create the view, specifying the WITH CHECK option.
Correct Answers: C

6: You work with a SQL Server 2005 database that provides banking information for customers. You want customers to see banking reports that combine data that is retrieved from the database with real-time investment information that comes from a Web service provided by a third party. The investment information must be current when the reports are executed. You need to create the appropriate objects that support the reports. What should you do?
A.Publish the data in the database as an XML Web service by using the FOR XML AUTO clause.
B.Create a table to store the banking information for each customer. Create a trigger that fires when data is inserted into the table that joins with the data coming from the Web service.
C.Create a Transact-SQL stored procedure that uses a temporary table to store the banking information for each customer. Update the table with the values from the Web service.
D.Have a developer in your company create an assembly that calls the remote Web service. Create a CLR function by using the assembly. Call the CLR function and combine the results with banking information in the database.
Correct Answers: D

7: Your company uses a SQL Server 2005 database. This database contains a trigger named trg_InsertOrders, which fires when order data is inserted into the Orders table. The trigger is responsible for ensuring that a customer exists in the Customers table before data is inserted into the Orders table. You need to configure the trigger to prevent it from firing during the data import process. You must accomplish this goal while using the least amount of administrative effort. Which two Transact-SQL statements can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A.ALTER TABLE OrdersDISABLE TRIGGER trg_InsertOrders
B.DROP TRIGGER trg_InsertOrders
C.DISABLE TRIGGER trg_InsertOrders ON Orders
D.ALTER TRIGGER trg_InsertOrders  ON Orders NOT FOR REPLICATION
E.sp_settriggerorder@triggername= ‘trg_InsertOrders’, @order=’None’
Correct Answers: A C

8: Your company uses SQL Server 2005. You are implementing a series of views that are used in ad hoc queries. The views are used to enforce your companys security policy of abstracting data.  Some of these views perform slowly. You create indexes on those views to increase performance, while still maintaining the companys security policy. One of the views returns the current date as one of the columns. The view returns the current date by using the GETDATE() function. This view does not allow you to create an index. You need to create an index on the view. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Remove all deterministic function calls from within the view.
B.Remove all nondeterministic function calls from within the view.
C.Schema-bind all functions that are called from within the view.
D.Create the view and specify the WITH CHECK OPTION clause.
Correct Answers: B C

9: You are creating a SQL Server 2005 database for a mortgage company. The database will support a new Web-based application that will handle up to 1,000 simultaneous users. This application must quickly display the results of calculation-intensive operations, such as calculating mortgage payments and amortization schedules. You need to ensure that the database processes calculations as quickly and efficiently as possible. What should you do?
A.Implement parameterized Transact-SQL queries in the application.
B.Implement Transact-SQL stored procedures in the database.
C.Implement CLR stored procedures in the database.
D.Implement distributed Web services.
Correct Answers: C

10: 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.)
A.In Event Viewer, view the system log.
B.In Event Viewer, view the application log.
C.In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog.1 file.
D.In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ErrorLog file.
E.In Notepad, view the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAgent.out file.
Correct Answers: A B D

Download  |  Password: certificatexam.com