DRAG DROP
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.
The new hire's login credentials are as follows:
Login name: JFree
Password: Jx672$qse
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.

You need to monitor the health of your tables and indexes in order to implement the required index maintenance strategy.
What should you do?

• A. Create a SQL Agent alert when the File Table: Avg time per file I/O request value is increasing.
• B. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value decreases.
• C. Query system DMVs to monitor avg_chain_length and max_chain_length. Create alerts to notify you when these values converge.
• D. Query system DMVs to monitor total_bucket_count. Create alerts to notify you when this value increases.

You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?

• A. a recursive common table expression
• B. an OFFSET-FETCH clause
• C. the ROWNUMBER keyword
• D. a table variable

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.

• A. Query the sys.dm_exec_sessions dynamic management view.
• B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
• C. Query the sys.dm_exec_requests dynamic management view.
• D. Enable trace flag 1222, and then view the SQL Server error log.

You use SQL Server 2012 to maintain the data used by the applications at your company.
You plan to create a disk-based table named Tablel by using the following statement. (Line numbers are included for reference only.)

You need to ensure that Tablel 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?
---

• A. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
• B. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,
• C. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
• D. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,