Microsoft sql server exam 70-431

The last update to Pricelist occurred one week ago. You need to recover the Pricelist table. You want to achieve this goal by using the minimum amount of administrative effort. You must also minimize the amount of data that is lost. This database is updated with data that is exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp utility. This operation should be minimally logged. However, users report that they receive error messages indicating that the transaction log is full.

You investigate and discover that the errors occur when the data is being imported. You need to prevent this problem from occurring. You use a SQL Server database that contains both current and historical information about order transactions. You store the current transactions in a table named Orders and the historical information in a table named History.

You need to create a partitioned table to gain a performance advantage when executing your queries. Which Transact-SQL statement should you execute first? 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? Both servers run SQL Server There are no mirroring endpoints on either server. SQL1 contains a database named DB1. You need to create the database mirroring endpoints and configure security on all participating server instances.

Because some actions take more than three seconds, users report slow performance when they use App1. You need to capture only those App1 actions in DB1 that have a response time of two seconds or more.

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.

You need to provide maximum query performance. Table1 must remain available to users. You are creating a SQL Server 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. You are employed as a database administrator at Hi-tech Company. The analysts make use of this view to combine the total sales by clients per month. You want to increase the performance of the view. The Timesheet database is used by the TimeEntry application.

You need to minimize the amount of time that the TimeEntry application is unavailable while you move the database. Which of the following statements enables you to create a database certificate? 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. 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 The sales department records all sales activities in a database named DB1. DB1 contains 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. You work for a company that sells books. You are creating a report for a SQL Server database. The report will list sales representatives and their total sales for the current month. The report must include only those sales representatives who met their sales quota for the current month.

The date parameters are passed in variables named FromDate and ToDate. You need to create the report so that it meets these requirements. Which SQL query should you use? Users report that report execution is slow.

You investigate and discover that some queries do not use optimal execution plans. You also notice that some optimizer statistics are missing and others are out of date.

You need to correct the problem so that reports execute more quickly. Which two Transact-SQL statements should you use? The average data growth of all databases combined is MB a month. Users state that report execution times are increasing.

You want to assess whether more RAM is needed. Which performance object should you add to the counter log? You are implementing a table partitioning scheme in your SQL Server database.

You execute a query that does not return the results that you expect for one of the partitions. You need to find out which partition is affected by the query. Which Transact-SQL statement or function should you use? Because DB1 is 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. Because the reindexing of DB1 is not performed on a regular basis, a schedule has not been created for the job. You need to find out when ReIndexDB1 last ran. 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 identity the most likely cause of the connection issues. DB1 contains a table named Table1. Full-Text Search is not being used. 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? Recently, users who work with DB1 report that they experience long query execution times. You want to reproduce and analyze this problem in a test environment that holds all copies of the production databases. You need to record the workload for an entire day on SQL1 for later execution on the test server. You must ensure that all events are captured without skipping any, even if SQL1 is under stress. Application developers in your company create an assembly that contains a CLR function.

Which permission set should you use? Users report that the time required to run their queries on SQL1 is steadily increasing. You must minimize the impact of this operation on SQL1 performance. Which of the following are valid commands to use within a function?

You discover that the msdb database on a SQL Server 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. Which Transact-SQL statement should you use? Your company has a SQL Server computer.

You have been assigned the task of retrieving information about a user who is currently logged in. You need to create a function that returns scalar information about the activity time for a particular user.

You are responsible for implementing maintenance jobs on a SQL Server 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. 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.

DB1 is in use 24 hours a day, 7 days a week. You detect a high number of full scans on SQL1 and conclude that additional indexes in DB1 are needed.

You need to. You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 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. You are responsible for managing the database schema for an order entry application in a SQL Server 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. Create a CHECK constraint to validate the data and to assign the default value to each item in the table. You are a database administrator for your company. The only non-system database on the server is AdventureWorks.

A new written company policy states that nested trigger execution is not allowed. You need to ensure that this policy is implemented by modifying the appropriate server and database settings in the database properties dialog box, or both of these dialog boxes, in SQL Server Management Studio SSMS.

Perform the appropriate actions in the simulation. When you are finished, click Done. You are a database developer for your company. Users report that quenes that are executed against some views are returning incorrect results. You investigate and discover that the problem is being caused by one or more views.

You want to ensure that each of these views returns the results described in the following table. You need to examine the query in each of the four views and then delete the view or views that return incorrect results.

You must not delete views that return correct results. You work as a database administrator for your company. A new company security policy is being implemented. In preparation for implementation of the policy, you must remove the Tom Login. The new policy states that users associated with SQL Server Logins must be removed, and the ownership of anything that is owned by these users must be transferred to dbo.

You must perform a backup that contains all of the changes since the last transaction log backup. You must ensure that no changes can be applied to the database after this backup completes. The backup file must be named AW.

You need to perform the backup by using the Back Up Database dialog box. You need to create a maintenance plan to rebuild indexes in the Adventureworks database. The indexes must be rebuilt using a fill factor of The fill factor must be applied to both the leaf level and the balanced tree of the index. While the maintenance plan is running, all tables must be available for queries to be run. You are a database administrator at your company. You need to add Server2 as a linked server on Server1.

All the logins should be able to access Server2 through Server1. A login named App2 with the password password must be used for the linked server connection to Server2. You must apply the fewest possible settings. You need to create the linked server by using the New Linked Server dialog box on Server1.

Users report that queries that are executed against some views are returning incorrect results. You need to examine the query in each f the four views and then delete the view or views that return incorrect results. Car license plate data is no longer required to be stored in the AdventureWorks database.

You need to identify the tables that contain columns referencing the user-defined data type named LicensePlate, and you must remove those columns.

You should not remove the actual LicensePlate user-defined data type. You need to back up all the changes that have occurred in the AdventureWorks database since the last full database backup. The size of the backup file must be kept to a minimum. The database is currently using log shipping, which must not be disrupted. Only the login named App1 should be able to access Server2 through Server1.

A login named App2 with the password password must be used for the linked server connections to Server2. You need to create a new database named Sales. The new database must meet the following requirements. The available volumes are shown in the following table. You need to create the Sales database by using the New Database dialog box.

In preparation for implementation of the policy, you must remove the Tom login. The new policy states that users associated with SQL Server logins must be removed, and the ownership of anything that is owned by these users must be transferred to dbo. Users report that they receive an error message when they execute a CLR user-defined function named Production. The error is occurring because the function is being denied access to the local file system.

You must ensure that the least permissions possible to solve the problem are applied. You must apply the minimum number of possible changes. After studying all these free questions you can be confident on Microsoft practice test questions and answers from Exam-Labs.

Apart from these online questions you can also study Microsoft exam practice test questions and answers in VCE file format which can be opened with Avanset VCE exam simulator. You will get access to your products immediately after we receive your payment. Please check your mailbox for a message from support exam-labs.

Microsoft Exam Practice Test Questions. Billed every 3 months. Billed every 6 months. Billed every 12 months. Subscription options Please select your preferred subscription below: Pay Quarterly. All functions must be deterministic. For example, you cannot use getdate because every time it is executed, it returns a different date result.

Show correct answer. Implement parameterized Transact-SQL queries in the application. Update the view to use an outer join between the Customers and Sales tables. Explanation: As a view is nothing more than a virtual table containing data from one or more tables you have the option to create a clustered index on the view in order to obtain an indexed view and obtain performance benefits.

View the BlockedBy column for session Open the Locks by Process page. View the Request Mode column for session Explanation: The Process Info page shows the properties of current processes.

Blocking indicates whether this process is blocking others. The sys. Question 6 - Topic 1 You are creating a Web-based application to manage data aggregation for reports. Create a conversation between the databases by using the following statement.

Create a contract between the services by using the following statement. Create services for each database by using the following statement. Implement horizontal partitioning.

Explanation: Table and index partitioning - intended to improve performance of operations performed on large tables. The most common method of splitting data is horizontal partitioning, in which rows of a table matching mutually exclusive criteria such as, range of dates or letters in alphabet, for datetime and character data, respectively are placed in designated partitions.

A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

Question 9 - Topic 1 Your company has multiple servers in a distributed environment. Configure SQL2 as a linked server to impersonate the remote login. Explanation: You need to define a linked server for each external data source you want to access and then configure the security context under which your distributed queries will run. You can configure the linked server to use one of the following security modes: Self-mapping When a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to the external data source using the current users login credentials.

The same login and password must exist on the remote server. This is the default behavior. Delegation This mode impersonates the Windows local credentials; the connection forwards the credentials of an authenticated Windows user to the linked server. The Windows user account and password must exist on the linked server. Remote Credentials This mode lets you map local logins to remote logins on the external data source.

Question 10 - Topic 1 Which role membership level is the minimum necessary to subscribe to a publication? Remove all deterministic function calls from within the view. Remove all nondeterministic function calls from within the view. Drop the foreign key. Import the data by using the script. Re-create the foreign key. If this is used properly it will write a record missing the ProductCode to an alternative destination and not stop the import.

Question 14 - Topic 1 Which of the following sentences is true for Database Mail? A Database Mail account is a collection of Database Mail profiles.

A Database Mail profile is a collection of Database Mail accounts. Explanation: To configure mirroring you have to make sure that the databases are set to use the Full recovery model. Database mirroring maintains both the primary and mirror databases as exact duplicates.

This includes synchronizing all internal structures such as Log Sequence Numbers LSNs , the Simple and Bulk-Logged recovery models are incompatible with database mirroring.

This will guarantee that the starting state of the mirror reflects the state of the principal database, including the LSNs. Thereafter you have to produce a communication channel for the database mirroring to use and this is done by creating an endpoint for data mirroring on all participating servers.

Question 16 - Topic 1 You are creating an HTTP endpoint that will be used to provide customer data to external applications. Search related threads. Remove From My Forums. Answered by:. Archived Forums. SQL Server Documentation. Sign in to vote. Could anyone please let me know: 1. What is the duration of the exam.

How many sections are there. How many questions in each section. What is the passing marks. Thanks pvn. Wednesday, April 2, PM. I had 35 questions in one word and 12 questions on simulation. I suggest you to study both the book from Sybex and the one from Microsoft Press instead. I confirm my exam contained 52 questions, don't know if this number is fixed or not.

Good luck for your exam! Hi Steve I am preparing for the Exam can you pls help me with the simulation where I can some practice simulation. Can you please guide me how to go about it? Thanks in advance. How many simulations quiestions did you find? How long did it take you to study for this exam? What scares me about the exam is the huge amount of complicated T-SQL commands.

Look at the syntax of any T-SQL command with all its options; many many options, how can I remember them. Do I need to remember all these instructions for the exam. Please let me know. Hey, Guy's I'm a college freshman that loves T Sql and Sql Server I've been practicing for 70 for 3 mnths but almost every exam and book tells me i need real world experiences.

First of all, congratulations! Thank you for the advices. I'm planning to take this exam too next month. I've been studying for this exam for Well, 7 years of practice starting with SQL Server 7. KBA: Well, real world experiences would certainly help a lot with your exam because constant practice on "real" cases would make you remember the shortest path to get things done.

And some exam simulations ask you just to do that. Please guys please sharing ur experience I wataing for ur reply Take care. Thanx Tins I'm preparing for also, please can you send some simulator question and other practice questions to me: taiwo yahoo.

Also if its possible, please mail neel. I had my MCTS exam yesterday. In order to pass the exam you have to pass in both the sections. Can anyone please help me how to prepare for simulation question that be really helpful.

I want to write my MCTS exam, is it difficult? And, I have Pass-Guaranteed Questions and answers , will that help?