What is the exam about?

Developing SQL Server database solutions.

You have a table named Rooms that contains three columns. You run the following Transact-SQL statement: You discover the execution plan shown in the exhibit. (Click the Exhibit button.) You need to recommend a solution to reduce the amount of time it takes to execute the query. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

Correct Answer: Include the RoomName column and the Position column in the Room_IX index.

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 following requirements: ✑ Support real-time monitoring. ✑ Be enabled and disabled easily. ✑ Support querying of the monitored data. What should you implement? More than one answer choice may achieve the goal. Select the BEST answer.

Correct Answer: A SQL Server Profiler template

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateEmployeeName. What should you include in the recommendation?

Correct Answer: Change data capture

You execute IndexManagement.sql and you receive the following error message: "Msg 512, Level 16, State 1, Line 12 Subquery returned more than 1 value. This is not permitted when the subquery follows =, != ,, > = or when the subquery is used as an expression." You need to ensure that IndexManagement.sql executes properly. Which WHILE statement should you use at line 18?

Correct Answer: WHILE COUNT(@RowNumber) < (SELECT @counter FROM @indextable)

You need to recommend a solution to ensure that SQL1 supports the auditing requirements of usp_UpdateSpeakerName. What should you include in the recommendation?

Correct Answer: The Distributed Transaction Coordinator (DTC)

You need to implement a change to usp_ExportOpenings that meets the integration requirements. What should you modify in usp_ExportOpenings? (Each correct answer presents part of the solution. Choose all that apply?)

Correct Answer: To line 10, add FOR XML RAW.

You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements: ✑ Have a default value of false. ✑ Minimize the amount of disk space used. Which code block should you use?

Correct Answer: ALTER TABLE Attendees ADD Confirmed char(l) DEFAULT `1';

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 do?

Correct Answer: Materialize the view.

You are evaluating the index design. You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute. Which line of code should you use to replace line 08 of Indexes.sql?

Correct Answer: (LastName, EmployeeID);

Developers report that usp_UpdateSessionRoom periodically returns error 3960. You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows. What should you configure in Procedures.sql?

Correct Answer: Replace line 46 with the following code: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Developers report that usp_UpdateOffice periodically returns error 3960. You need to prevent the error from occurring. The solution must ensure that the stored procedure return the original values to all of the updated rows. What should you configure in StoredProcedures.sql?

Correct Answer: Move the SET statement at line 38 to line 45

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs. What should you do?

Correct Answer: Execute sp_recompile usp.GetOrdersByProduct'.

You discover that usp.SelectSpeakersByName executes slowly if usp_UpdateSpeakerName executes simultaneously. You need to minimize the execution time of usp.SelectSpeakersByName. The solution must not affect the performance of the other stored procedures. What should you update?

Correct Answer: Usp_UpdateSpeakerName to use the NOLOCK query hint

You need to add a new column named Confirmed to the Employees table. The Confirmed column has the following requirements: ✑ It must have a default value of TRUE. ✑ It must minimize the amount of disk space used. Which Transact-SQL statement should you run?

Correct Answer: ALTER TABLE Employees ADD Confirmed but DEFAULT 1;

You have a Microsoft SQL Azure database. You have the following stored procedure: You discover that the stored procedure periodically fails to update HR.Employees. You need to ensure that HR.Employees is always updated when up_employees executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held. What should you do?

Correct Answer: Add the following line of code to line 05: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

You need to convert the functionality of Dynamic.sql to use a stored procedure. Which Transact SQL statement should you add to the stored procedure contain?

Correct Answer: CREATE PROC Production.ProductsAfterDate( @sqlstring AS nvarchar(1000), @ProductID AS varchar(11), @CreationDate AS date) AS ...

You have a database named Database1. Database1 has two stored procedures named Proc1 and Proc2 and a table named Table1. Table1 has millions of rows. Proc1 updates data in Table1. Proc2 reads data from Table1. You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked. The block affects all rows, including those that are not being updated by Proc1. You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating. What should you change Proc1 to do? More than one answer choice may achieve the goal. Select the BEST answer.

Correct Answer: Wait for Proc2 to complete.

You are evaluating the index design for the database. You have the following requirements: ✑ minimize the amount of time it takes for usp_AttendeesReport to run ✑ minimize the amount of database fragmentation. You need to recommend a change to Indexes.sql Which line of code should you use to replace line 12 of Indexes.sql?

Correct Answer: (LastName, FirstName);

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated. What should you add to usp_getOrdersAndItems?

Correct Answer: Add WITH (UPDLOCK) to the end of line 42

You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU). You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits. The solution must minimize the amount of development effort required. What should you include in the recommendation?

Correct Answer: A FOREIGN KEY constraint

You have a database that uses the following management views: ✑ Sys.dm_os_volume_stats ✑ Sys.dm_db_partition_stats ✑ Sys.dm_db_file_space_usage ✑ Sys.fulltext_indexes You plan to migrate the database to Microsoft SQL Azure. You need to identify which view can be used in SQL Azure. Which view should you identify?

Correct Answer: sys.dm_db_partition_stats

You need to modify the Orders table to store the XML data used by the retailers. Which statement should you execute?

Correct Answer: ALTER Orders ADD originalOrder varchar(max);

You need to identify which long running transactions use an index. Which dynamic management view should you use?

Correct Answer: sys.dm_exec_plan_attributes

What is the exam about?

Developing SQL Server database solutions.

Welcome to awslagi.com – Sharing AWS, Google Cloud, Microsoft Azure IT Exams. It's free for all!

Developing Microsoft SQL Server 2012/2014 Databases

Develop SQL Server databases with professional application design.

4.8★★★★★(300 ratings)

16,606 studentsIntermediate

Gain hands-on database development skills for building reliable SQL Server solutions. The exam covers schema design, programmability, indexes, stored procedures, and data integrity, helping you create robust database applications. These skills matter for developers who need to design databases that perform well and scale safely. It also supports stronger collaboration between application teams and database teams.

What you'll learn

Design database schemas- Write stored procedures- Optimize indexes- Enforce data integrity- Create database objects

70-464

Lectures1

Quizzes0

Duration30 week

Students16,606

$20.00

📢 Unlock Full Access to Success!

"The content displayed here is a promotional preview and contains only 50% of the actual exam database. To access the complete set of updated questions, please upgrade to the Full Version."

👉 Click the button and follow the instructions to secure your full access immediately!