You administer a Microsoft SQL Server database named Orders. Orders is highly active OLTP system used for e-commerce.

Performance on the database has degraded over the past few months as the volume of data has increased, and now users report the application is unusable.

You need to identify the cause of the performance problem.

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution

DRAG DROP

You need to create a cursor that meets the following requirements:

– Executes as quickly as possible.

– Reflects all data changes made to the table while scrolling.

Which five Transact-SQL statements should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer are and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Step 1: Declare variables.

Step 2: Open the cursor

Step 3: Fetch the first instance.

Step 4: Loop

Step 5: Close and deallocate the cursor References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-cursor-transact­

sql?view=sql-server-2017

DRAG DROP

You administer a Microsoft Azure SQL Database instance.

You are troubleshooting a number of stored procedures that use transactions.

-p_ModifyCustomer modifies customer records in the database. Processes that uses these records must receive a copy of the record as it exists at the beginning of the transaction, and the procedure must not block these processes.

-p_GetOrders is used to retrieve orders for a customer. While the transaction is running, no other process should be able to read the same data, and no other transaction should be able to modify the data until the transaction completes.

-p_ShipOrders is run once per day to batch orders into shipping criteria. While this transaction is running, no other transaction should be allowed to insert data into the range of orders being modified.

You need to choose the appropriate transaction isolation level for each stored procedure. The transaction must meet the need while providing the highest level of concurrency and performance.

Which isolation levels should you use? To answer, drag the appropriate isolation levels to correct stored procedures. Each isolation level 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.

Reveal Solution Hide Solution

Correct Answer:

Explanation:

Box 1: READ_COMMITTED

READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option

Box 2: REPEATABLE_READ:

REPEATABLE_READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.

Box 3: SERIALIZABLE

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level­transact-sql?view=sql-server-2017

DRAG DROP

You administer a Microsoft SQL Server database that contains a table named Customer defined by the following Transact-SQL statement:

The SalesRep column contains the SQL Login name of the user designated as the customer’s sales rep.

You need to create at trigger that meets the following requirements:

– A customer’s CreditLimit can only be changed by the customer’s SalesRep.

– CreditLimit cannot be increased by more than 50 percent in any single update.

If an UPDATE statement causes either of these business rules to be violated, the entire UPDATE statement should be rolled back.

In addition, the trigger must handle single-row and multi-row update statements and should execute in the most efficient manner possible.

How should you complete the trigger? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.

Reveal Solution Hide Solution

Correct Answer:

You develop a Microsoft SQL Server database that contains tables as shown in the exhibit. (Click the Exhibit button.)

You need to retrieve a list of clients for whom there is no corresponding information in the Projects table.

Which Transact-SQL statements should you run?

A)

B)

C)

D)

Reveal Solution Hide Solution

Note: This question is part of series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.

You administer a Microsoft SQL Server database.

The database contains a table named Employee.

Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

Unless stated above, no columns in the Employee table reference other tables.

Confidential information about the employees is stored in a separate table named EmployeeData.

One record exists within EmployeeData for each record in the Employee table.

You need to assign the appropriate constraints and table properties to ensure data integrity and

visibility.

Which column in the Employee table should be referenced in a one-to-one relationship by the EmployeeData table?

Reveal Solution Hide Solution

You use a Microsoft SQL Server database.

You want to create a table to store files.

You need to ensure that the following requirements are met:

– The files must include information about the directory structure.

– The files must be accessible in SQL Server.

– The files must be in a folder that is accessible directly by using Windows Explorer.

Which Transact-SQL statement should you run?

A)

B)

C)

D)

Reveal Solution Hide Solution

DRAG DROP

You administer a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Suppliers and an indexed view named VWLocalSuppliers, both of which were created by using the following Transact-SQL statement: You need to change the data type of the Code column in the Suppliers table to nvarchar(50).

Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

Reveal Solution Hide Solution

Correct Answer:

You develop a Microsoft SQL Server database named ContosoDb. ContosoDb contains a table named Employess that was created by using the following Transact-SQL statement:

You need to create a view that allows the insertion of new records into the Employees table by using the view.

Reveal Solution Hide Solution

The Agent table of a Microsoft SQL Server database contains several million rows. The database uses the SQL_Latin1_General_Cp1_CS_AS collation.

You need to ensure that the following requirements are met:

– The values of the StateRefID column conform to the pattern of 3 uppercase letters followed by 5 numeric digits, such as “ABC12345”.

– The StateRefID values are unique within the Agent table.

– The values of all records that will be inserted or updated in the Agent table are correctly formatted.

– Exisitng rows are ignored

Which Transact-SQL statement should you run?

Reveal Solution Hide Solution