Microsoft Recertification for MCSE: Data Platform - 70-469 Exam Practice Test

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

DRAG DROP
You need to optimize the index and table structures for POSTransaction.
Which task should you use with each maintenance step? To answer, drag the appropriate tasks to the correct maintenance steps. Each task may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

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 included for reference only.)

---
You need to ensure that Table1 contains a column named UserName. The UserName column will:
Store string values in any language.
Accept a maximum of 200 characters.
Be case-insensitive and accent-insensitive.
Which code segment should you add at line 03?

You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?

You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

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

DRAG DROP
You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
- Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. - Table2 will contain a column named Column2 which is the product of two other columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.

You need to ensure that if any of the statements in usp_UpdateSpeakerName return an error message, all of the changes executed by usp_UpdateSpeakerName are not committed to the database.
What should you do in Procedures.sql? (Each correct answer presents part of the solution. Choose all that apply.)