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

Exact of 70-431 free practice questions materials and ebook for Microsoft certification for IT examinee, Real Success Guaranteed with Updated 70-431 pdf dumps vce Materials. 100% PASS Microsoft SQL Server 2005 Implementation & Maintenance exam Today!

2016 Apr 70-431 Study Guide Questions:

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


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

Answer: C


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


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

Answer: D


Q5. 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 practice

Abreast of the times 70-431 exam prep:

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


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


Q8. You use Service Broker to manage data requests between SQL Server 2005 databases. You need to make changes to objects for an existing service. You want to ensure that no messages can be received by the service until the changes are complete. What should you do?

A. Create the queue with ACTIVATION STATUS OFF. Set it to ON when the service is ready.

B. Create the queue with STATUS OFF. Set it to ON when the service is ready.

C. Create the queue with only the queue_name argument. Set the other parameters when the service is ready.

D. Create the queue with max_queue_readers = 0. Set it to an optimum value when the service is ready.

Answer: B


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

Answer: C


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


70-431 study guide

Actual 70-431 samples:

Q11. 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. Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2000ApplicationNameLikeApp1DatabaseNameLikeDB1

B. Add the ApplicationName column and create the following column filters.ColumnOperatorValueDurationGreater than or equal2ApplicationNameLikeApp1DatabaseNameLikeDB1

C. Add the CPU column and create the following column filters.ColumnOperatorValueCPUGreater than or equal2000TextDataLikeApp1DatabaseNameLikeDB1

D. Add the ApplicationName and CPU columns and create the following column filters.ColumnOperatorValueCPUGreater than or equal2ApplicationNameLikeApp1TextDataLikeDB1

Answer: A


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


Q13. Your SQL Server 2005 database contains a table that has 500 million rows of data. Some of the data is historical and some is current. You need to partition the data on a single server to increase performance and optimize maintenance. What should you do?

A. Implement horizontal partitioning.

B. Implement vertical partitioning.

C. Implement a raw partition.

D. Implement distributed partitioning.

Answer: A


Q14. You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do?

A. Specify StateProvince, Surname, and FirstName in the ORDER hint in the bcp out command.

B. Create a format file for the export operation.

C. Specify StateProvince, Surname, and FirstName in the ORDER BY clause in the bcp queryout command.

D. Copy the data into a new table that has a clustered index on StateProvince, Surname, and FirstName. Export the data.

Answer: C


Q15. You are creating a SQL Server 2005 application that will store and manage legal documents. The original documents are stored as XML documents on a file server. The application will be used to insert the documents into the database. Later, the documents must be retrieved from the database. These documents must be identical to the originals. You need to design a table to store the document data. What should you do?

A. Store the XML in a column that has a data type of nvarchar(max).

B. Shred the XML and store it in a relational structure.

C. Store the XML in a column that has a data type of XML.

D. Store the XML in a column that has a data type of varchar(8000).

Answer: A


Q16. 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?

A. Change the AUTHENTICATION property to KERBEROS.

B. Specify BATCHES = ENABLED.

C. Specify STATE = Started.

D. Specify WSDL = 'pr_GetPersonData'.

Answer: C