QUESTION 63You are writing a set of queries against a FILESTREAM-enabled database. You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLEB. SET XACT_ABORT ONC. SET IMPLICIT TRANSACTIONS OFFD. SET XACT_ABORT OFF

QUESTION 64Which index type gives high performance gains for analytic queries that scan large amounts of data, especially on large tables?

A. RowstoreB. Columnstore

Answer: BExplanation:Use columnstore indexes on data warehousing and analytics workloads, especially on fact tables, since they tend to require full table scans rather than table seeks.

QUESTION 65You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. Which clause rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output?

A. PIVOTB. UNPIVOT

Answer: A

QUESTION 66A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issued against a table or view. Which of the following is TRUE regarding INSTEAD OF triggers?

A. All of theseB. INSTEAD OF triggers fire in place of the triggering action and before constraints are processedC. If there are AFTER triggers on the table, they will fire after constraint processing.D. If the constraints are violated, the AFTER trigger is not executed.

Answer: A

QUESTION 67Which Transact-SQL clause is described below?Generates totals that appear as additional summary columns at the end of the result set. When used with BY, the ___ clause generates control-breaks and subtotals in the result set.

A. AVGB. COMPUTEC. None of theseD. SUM

Answer: C

QUESTION 68The transaction isolation level controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. Which of the following transaction isolation levels is the default in SQL Server 2016?

A. READ COMMITTEDB. READ UNCOMMITTEDC. REPEATABLE READD. SERIALIZABLE

Answer: A

QUESTION 69Study the JSON string shown in the image.

Which Path parameter would NOT result in an error when used by JSON_VALUE in strict mode?

A. $.info.”address”B. $.info.address.townC. $D. $.info.type[0]

Answer: BExplanation:JSON_VALUE extracts a scalar value from a JSON string. In strict mode it will throw an error if the return result is a nested value/object. If you want to extract a nested value you must use JSON_QUERY instead.

QUESTION 70As part of a global e-commerce business you are developing a Microsoft SQL Server database that supports the company’s online website. The application contains a table that has the following definition:CREATE TABLE Inventory(ItemID int NOT NULL PRIMARY KEY,ProductsInStore int NOT NULL,ProductsInWarehouse int NOT NULL)You need to create a computed column that returns the sum total of the ProductsInStore and ProductsInWarehouse values for each row. Which T-SQL statement should you use?

QUESTION 71You are developing a Microsoft SQL Server database. You create a view that performs the following tasks:Joins 10 tables that contain up to 400,000 records each.Performs aggregations on 4 fields.The view is frequently used in several detailed reports. You need to improve the performance of the reports. What should you do?

A. Convert the view into a table-valued functionB. Convert the view into an indexed viewC. Convert the view into a stored procedure and retrieve the result from the stored procedure into a temporary tableD. Convert the view into a Common Table Expression (CTE)

Answer: BExplanation:The first index created on a view must be a unique clustered index. After the unique clustered index has been created, you can create more nonclustered indexes. Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.

QUESTION 72Is the following statement TRUE or FALSE?JSON is not a built-in data type in SQL Server 2016, and SQL Server 2016 does not have custom JSON indexes?

A. TRUEB. FALSE

Answer: A

QUESTION 73The !> operator is a comparison operator that compares two expressions. However, if ANSI_NULLS is set to ON and one of the operands is NULL, what will the result be?expression !> expression (NULL)

A. FALSEB. 0C. TRUED. NULL

Answer: DExplanation:If either or both operands are NULL and SET ANSI_NULLS is set to ON, the result is NULL. If SET ANSI_NULLS is set to OFF, the result is FALSE if one of the operands is NULL, and TRUE if both operands are NULL.