Skip Navigation Links
 

Microsoft - 70-762: Developing SQL Databases

Sample Questions

Question: 141
Measured Skill: Manage database concurrency (25–30%)

You are a database developer for a company. You are developing a stored procedure with the following requirements:
  • Accepts an integer as input and inserts the value into a table.
  • Ensures new transactions are committed as part of the outer transactions.
  • Preserves existing transactions if the transaction in the procedure fails.
  • If the transaction in the procedure fails, rollback the transaction.
How should you complete the procedure?

(To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: @@TRANCOUNT = 1
P2: XACT-STATE() = -1
P3: XACT-STATE() = 0
B P1: @@TRANCOUNT = 1
P2: @TranCount = 0
P3: XACT_STATE() <> -1
C P1: XACT_STATE() = 0
P2: @@TRANCOUNT = 1
P3: @TranCount > 0
D P1: XACT-STATE() = -1
P2: XACT-STATE() = -1
P3: XACT-STATE() = 0
E P1: @TranCount > 0
P2: @TranCount = 0
P3: XACT_STATE() <> -1
F P1: @TranCount > 0
P2: XACT_STATE() = 0
P3: @@TRANCOUNT = 1

Correct answer: E

Explanation:

The SAVE TRANSACTION SavePoint statement defines a location to which a transaction can return if part of the transaction is conditionally canceled. We can roll back to this location using the ROLLBACK TRANSACTION SavePoint statement.

The @@TRANCOUNT variable returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

XACT_STATE is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

XACT_STATE returns the following values:
  • 1 The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

  • 0 There is no active user transaction for the current request.

  • -1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

    When the outermost batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back.
References:

SAVE TRANSACTION (Transact-SQL)

@@TRANCOUNT (Transact-SQL)

XACT_STATE (Transact-SQL)

Question: 142
Measured Skill: Design and implement database objects (25–30%)

You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non-business hours.

You run the following Transact-SQL statement:

SELECT ProductStandardCost, 
((ProductStandardCost / TotalProductCost) * 100) AS StandardCostRatio, 
ProductKey
FROM Sales
ORDER BY ProductStandardCost DESC, ProductKey


You analyze the execution plan for the statement as shown in the following exhibit.



You need to add an index that optimizes performance.

How should you complete the Transact SQL statement?

(To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: ProductStandardCost DESC
P2: INCLUDE (TotalProductCost)
B P1: ProductStandardCost DESC
P2: WITH (ONLINE = ON)
C P1: ProductStandardCost ASC, ProductKey ASC
P2: WITH (SORT_IN_TEMPDB = ON)
D P1: ProductStandardCost ASC, ProductKey DESC
P2: INCLUDE (TotalProductCost)
E P1: ProductStandardCost DESC, ProductKey DESC
P2: WITH (SORT_IN_TEMPDB = ON)
F P1: ProductStandardCost DESC, ProductKey DESC
P2: WITH (SORT_IN_TEMPDB = OFF)

Correct answer: D

Explanation:

When defining indexes, you should consider whether the data for the index key column should be stored in ascending or descending order. Ascending is the default and maintains compatibility with earlier versions of SQL Server. The syntax of the CREATE INDEX, CREATE TABLE, and ALTER TABLE statements supports the keywords ASC (ascending) and DESC (descending) on individual columns in indexes and constraints.

Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient.

If an index is created with key columns that match those in the ORDER BY clause in the query, the SORT operator can be eliminated in the query plan and the query plan is more efficient.

An index defined as ProductStandardCost ASC, ProductKey DESC can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed.

Reference: SQL Server Index Design Guide

Question: 143
Measured Skill: Optimize database objects and SQL infrastructure (20–25%)

You are a database developer for a company. You have a Microsoft Azure SQL database.

Some queries take a long time to execute.

You need to identify whether any of the long-running queries prevent users from updating data in the database.

Which query should you execute?

(To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: sys.dm_os_dispatchers
P2: sys.dm_os_latch_stats
B P1: sys.dm_os_stacks
P2: sys.dm_exec_sql_text(plan_handle)
C P1: sys.dm_os_stacks
P2: sys.dm_os_waiting_tasks
D P1: sys.dm_tran_commit_table
P2: sys.dm_exec_sql_text(plan_handle)
E P1: sys.dm_tran_locks
P2: sys.dm_tran_database_transactions
F P1: sys.dm_tran_locks
P2: sys.dm_os_waiting_tasks

Correct answer: F

Explanation:

sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

The lock_owner_address column of the sys.dm_tran_locks management view is the memory address of the internal data structure that is used to track this request. This column can be joined with the resource_address column from the sys.dm_os_waiting_tasks management view.

References: sys.dm_tran_locks (Transact-SQL)

Question: 144
Measured Skill: Design and implement database objects (25–30%)

You are a database developer for a company. You run the following Transact-SQL statements:

CREATE TABLE Customers (
CustomerID INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerName NVARCHAR(100) UNIQUE NOT NULL
)

CREATE TABLE Orders (
OrderID INT NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
CustomerID INT NOT NULL REFERENCES Customers(CustomerID)
OrderDate DATE NOT NULL
)

CREATE VIEW v_CustomerOrder
AS
SELECT b.CustomerName, a.OrderID, a.OrderDate,
(SELECT COUNT(*) FROM Orders c WHERE c.CustomerID = a.CustomerID) AS CustomerOrderCount
FROM Orders a
INNER JOIN Customers b ON a.CustomerID = b.CustomerID


Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created.

You need to ensure that you can insert rows into the Orders table by using the view.

What should you do?

AAdd the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.
B Create an INSTEAD OF trigger on the view.
C Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.
D Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

Correct answer: D

Explanation:

Views must meet specific requirements to support update and insert data from the underlying table. The view must e.g. contain all columns of the tables for which no default values are configured. In addition, subqueries and derived columns are not allowed in the view.

We need to remove the subquery and to create an INSTEAD OFF trigger that checks if the customer name exists in the Customers table. If the Customer exists the order information can be added to the Orders table. If the Customer not exists, the customer needs to be created first, before the order information can be inserted.

Question: 145
Measured Skill: Implement programmability objects (20–25%)

You are performing a code review for Transact-SQL statements.

Which action does the constraint in each statement perform?

(To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.)

www.cert2brain.com

AP1: Enforces unique values for columns.
P2: Enforces a reference to a primary key.
P3: Enforces unique values for columns.
P4: Ensures values are acceptable for columns based on an evaluation.
B P1: Ensures unique values within a column.
P2: Enforces unique values for columns.
P3: Ensures unique values within a column.
P4: Ensures values are acceptable for columns based on an evaluation.
C P1: Enforces unique values for columns.
P2: Enforces a reference to a primary key.
P3: Ensures unique values within a column.
P4: Ensures values are acceptable for columns based on an evaluation.
D P1: Ensures unique values within a column.
P2: Enforces a reference to a primary key.
P3: Ensures unique values within a column.
P4: Ensures values are acceptable for columns based on an evaluation.

Correct answer: C

Explanation:

P1 - The primary key constraint enforces unique values for the combination of the values of the PersonID and LastName columns.

P2 - The foreign key constraint implements a reference to the PersonID column of the Persons table.

P3 - The UNIQUE constraint ensures that no values occur multiple times in the PersonID column.

P4 - The CHECK constraint ensures that the Age column contains only integer values greater than 17.





 
Tags: exam, examcollection, exam simulation, exam questions, questions & answers, training course, study guide, vce, braindumps, practice test
 
 

© Copyright 2014 - 2019 by cert2brain.com