Question 6 of 72

You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.

Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.

Schema1.Udf1 was defined by using the following code:

You need to write a query that will contain the following columns:

✑ Country

✑ CountryID

✑ CustomerName

The solution must meet the following requirements:

✑ Rows must be returned only if the function returns data.

✑ The amount of time it takes the query to execute must be minimized.

Which query should you use?

    Correct Answer: B

    To meet the requirements of returning rows only if the function returns data and minimizing the execution time, the best choice is to use the CROSS APPLY operator. CROSS APPLY allows for applying the function to each row of the outer table (Schema1.Table1) and returns rows only when the function returns data. INNER JOIN would not be as appropriate here because it could result in a Cartesian product, and LEFT JOIN could return rows even when the function returns no data, which does not meet the requirements. Therefore, the correct query is the one that uses CROSS APPLY.

Question 7 of 72

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 script. (Line numbers are included for reference only.)

You need to ensure that the view can update the data in the table, except for the data in Column1.

Which code segment should you add at line 06?

    Correct Answer: A

    To ensure that the view can update the data in the table except for the data in Column1, you should use 'WITH CHECK OPTION' in the view definition. This ensures that any updates or inserts through the view must meet the criteria specified in the WHERE clause. Therefore, the correct code segment to add at line 06 is 'WITH CHECK OPTION'.

Question 8 of 72

DRAG DROP -

You are planning two stored procedures named SProc1 and SProc2. You identify the following requirements:

✑ SProc1 must return a table.

✑ SProc2 must return a status code.

You need to identify which options must be implemented to meet each stored procedure requirement.

Which options should you identify?

To answer, drag the appropriate option to the correct requirement in the answer area. (Answer choices may be used once, more than once, or not at all.)

Select and Place:

    Correct Answer:

Question 9 of 72

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: D

    Adding the line of code 'WITH (UPDLOCK)' to line 08 ensures that the SELECT statement acquires an update lock on the row or rows being read. This prevents other transactions from modifying or acquiring update locks on the same row(s) until the transaction completes. This approach ensures that the read data is not stale, as the update lock prevents conflicting updates, and minimizes the duration of locks held by only locking the necessary rows for updates.

Question 10 of 72

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: D

    Using the ROWLOCK table hint ensures that only the individual rows being updated by Proc1 are locked, allowing Proc2 to read other rows in Table1 that are not being updated. This approach is more efficient and avoids the blocking experienced when updating large numbers of rows with table or page-level locks.