Microsoft 70-464 Exam Practice Questions (P. 3)

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?

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.

You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
✑ Either BOTH statements succeed or BOTH statements fail as a batch.
✑ If an error occurs on the first statement, SQL should not attempt to run the second statement.
✑ Error information should be returned to the client.
What should you do?

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?

You have a database that contains a table named Department. Department contains the names and locations of each department.
You have a table-valued function named ProjectList() that returns a list of all the projects assigned to a department. The name of the department is passed as an argument to the ProjectList() function.
You need to create a query that returns a list of all the department names and the project names. The solution must return only departments that are associated to projects.
What should you add to the query?