You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name)

You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.

Which three code segments should you execute?

To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.

Correct Answer:

QUESTION 163

You have a Microsoft SQL Azure database that contains a table named Employees.

You create a non-clustered index named EmployeeName on the name column.

You write the following query to retrieve all of the employees that have a name that starts with the letters JOH:

You discover that the query performs a table scan.

You need to ensure that the query uses EmployeeName.

What should you do?

A.

Recreate EmployeeName as a unique index

B.

Recreate EmployeeName as a clustered index

C.

Replace LEFT(name,3) = ‘JOH’ by using name like ‘JOH%’

D.

Replace LEFT(name,3) = ‘JOH’ by using substring(name, 1, 3) = ‘JOH’

Correct Answer: C

QUESTION 164

You have a SQL Server 2012 database that contains a table named Users. The Users table contains usernames and passwords. You need to ensure that all new records have a password. Which code segment should you use? More than one answer choice may achieve the goal. Select the BEST answer.

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Correct Answer: D

QUESTION 165

You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.

After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:

“Msg 916, Level 14, State 1, Line 1

The server principal “Account1” is not able to access the database “DB1″ under the current security context.”

You verify that there is a server login for Account1 on Server2.

You need to ensure that Account1 can connect to DB1.

What should you do?

More than one answer choice may achieve the goal. Select the BEST answer.

A.

Update the SID for Account1 on DB1.

B.

Add Account1 to the db_datareader role.

C.

Create a new database user on DB1.

D.

Implement Windows authentication.

Correct Answer: B

QUESTION 166

DRAG DROP

You have two existing tables, one named COUNTRY and the other named STATES.

The tables are defined as follows:

You need to set up a rule that every STATE.Country_Abbr must match an existing record in the COUNTRY table.

Develop the solution by selecting and arranging the required code blocks in the correct order.

You may not need all of the code blocks.

Correct Answer:

QUESTION 167

DRAG DROP

You plan to deploy two stored procedures name USP_1 and USP_2 that read data from a database.

Your company identifies the following requirements for each stored procedure:

USP_1 cannot allow dirty reads.

USP_2 must place range locks on the data to ensure read consistency.

You need to identify which isolation level you must set for each stored procedure. The solution must minimize the number of locks.

Which isolation level should you identify?

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

Correct Answer:

QUESTION 168

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:

1. Either BOTH statements succeed or BOTH statements fail as a batch.

2. If an error occurs on the first statement, SQL should not attempt to r
un the second statement.

3. Error information should be returned to the client.

What should you do?

A.

Option A

B.

Option B

C.

Option C

D.

Option D

Correct Answer: A

Explanation:

* SET XACT_ABORT

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.

span>

QUESTION 169

You have a database that is accessed by 300 concurrent users.

You need to log all of the queries that become deadlocked. The solution must meet the following requirements:

Analyze Deadlocks with SQL Server Profiler Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio.

QUESTION 170

You have the following query on a disk-based table:

You discover that the query takes a long time to complete.

The execution plan is shown in the Execution Plan exhibit. (Click the Exhibit button.)

The index usage is show in the Index Usage exhibit. (Click the Exhibit button.)

You need to reduce the amount of time it takes to complete th
e query. You must achieve this goal as quickly as possible. What should you do?