70-459 Exam Questions

You have an application that uses a view to access data from multiple tables. You need to ensure that you can insert rows into the underlying tables by using the view. What should...

You plan to design an application that temporarily stores data in a SQL Azure database. You need to identify which types of database objects can be used to store data for the appli...

You create a view by using the following code: Several months after you create the view, users report that the view has started to return unexpected results. You discover that the...

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the r...

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that the index consumes more physical disk space...

You have a SQL Server 2012 database named Database1. You execute the following code: You insert 3 million rows into Sales. You need to reduce the amount of time it takes to execute...

You run the following code: You need to ensure that the root node of the XML data stored in the Details column is <Order_Details>. What should you implement? More than one answer c...

Your company has a SQL Azure subscription. You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1...

You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database. What should you rec...

You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script: Each order is tracked by using one of...

You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you d...

You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly avai...

You have a server named Server1 that has 16 processors. You plan to deploy multiple instances of SQL Server 2012 to Server1. You need to recommend a method to allocate processors t...

You have a SQL Azure database. You need to identify which keyword must be used to create a view that will be indexed. Which keyword should you identify?

You have a database hosted on SQL Azure. You are developing a script to create a view that will be used to update the data in a table. The following is the relevant portion of the...

You have a text file that contains an XML Schema Definition (XSD). You have a table named Schema1.Table1. You have a stored procedure named Schema1.Proc1 that accepts an XML parame...

You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP). You discover that many page splits occur when records are inse...

You have a SQL Azure database. You execute the following script: You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2. You plan...

You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a table that is used for auditing is updated. What should you use?...

You have a database named database1. Database developers report that there are many deadlocks. You need to implement a solution to monitor the deadlocks. The solution must meet the...

You execute the following code. You need to reduce the amount of time it takes to execute the query. What should you do?

You have a SQL Server 2012 database named DB1. You have a backup device named Device1. You discover that the log file for the database is full. You need to ensure that DB1 can comp...

You have a server that has SQL Server 2012 installed. You need to identify which parallel execution plans are running in serial. Which tool should you use?

You are creating a table to support an application that will cache data outside of SQL Server. The application will detect whether cached values were changed before it updates the...

You use SQL Server 2012 to maintain the data used by the applications at your company. You plan to create a table named Table1 by using the following statement. (Line numbers are i...

You are creating a database that will store usernames and credit card numbers for an application. You need to recommend a solution to store the credit card numbers in the database....

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media. You plan to synchronize SQLDev and SQLProd. You need to recommend a solution t...

You have a database named DB1. You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each tab...

You need to recommend a solution for the planned changes to the customer classifications. What should you recommend? (Each correct answer presents part of the solution. Choose all...

You need to recommend a change to sp3 to ensure that the procedure completes only if all of the UPDATE statements complete. Which change should you recommend?

You need to recommend a solution to meet the security requirements of the junior database administrators. What should you include in the recommendation?

You need to recommend a solution to minimize the amount of time it takes to execute sp1. With what should you recommend replacing Table1?

You need to recommend a disaster recovery strategy for the Inventory database. What should you include in the recommendation?

You need to recommend a solution to ensure that sp4 adheres to the security requirements. What should you include in the recommendation?

You need to recommend a change to sp3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails. Which change should you recommend?

You need to recommend a solution that addresses the concurrency requirement. What should you recommend?

You need to recommend a solution that addresses the backup issue. The solution must minimize the amount of development effort. What should you include in the recommendation?

You need to recommend a solution that addresses the index fragmentation and index width issue. What should you include in the recommendation? (Each correct answer presents part of...

You need to recommend a solution that meets the data recovery requirement. What should you include in the recommendation?

You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application. What sh...

You need to recommend an isolation level for usp_UpdateOrderDetails. Which isolation level should recommend?

You need to recommend a solution for Application 1 that meets the security requirements. What should you include in the recommendation?

You need to recommend a solution to improve the performance of usp_UpdateInventory. The solution must minimize the amount of development effort. What should you include in the reco...

You need to recommend a disk monitoring solution that meets the business requirements. What should you include in the recommendation?

You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements. What should you r...

You need to recommend a solution for the deployment of SQL Server 2012. The solution must meet the business requirements. What should you include in the recommendation?

You need to recommend a solution to synchronize Database2 to App1_Db1. What should you recommend?

You need to provide referential integrity between the Offices table and Employees table. Which code segment or segments should you add at line 27 of Tables.sql? (Each correct answe...

You execute usp_SelectEmployeesByName multiple times, passing strings of varying lengths to @LastName. You discover that usp_SelectEmployeesByName uses inefficient execution plans....

You need to create the object used by the parameter of usp_UpdateEmployeeName. Which code segment should you use?