Developing Microsoft SQL Server Databases (070-464) Free Practice Test

Question 1

You need to modify usp_GetOrdersAndItems to ensure that an order is NOT retrieved by usp_GetOrdersAndItems while the order is being updated.
What should you add to usp_GetOrdersAndItems?

A. Add SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to line 03.

B. Add (READPAST) to the end of line 06.

C. Add (UPDLOCK) to the end of line 06.

D. Add SET TRANSACTION ISOLATION LEVEL SNAPSHOT to line 03.

Correct Answer: B

Question 2

You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
* Permit insertions into a table variable.
* Support structured exception handling.
* Prevent changes to the definition of referenced objects.
* Support the use of the APPLY operator on the output of the object.
Which type of object should you use?

A. A scalar user-defined function

B. A stored procedure

C. An inline table-valued function

D. A multi-statement table-valued function

Correct Answer: A

Question 3

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 the query. You must achieve this goal as quickly as possible.
What should you do?

A. Update statistics.

B. Reorganize the index.

C. Rebuild the index.

D. Create an index on LastName.

Correct Answer: D

Question 4

You plan to execute the following code:

You need to identify how many rows will be in dbo.Table1 after you execute the code.
How many rows should you identify?

A. 3

B. 0

C. 1

D. 2

Correct Answer: B

Question 5

You need to implement a solution that addresses the page split issues.
Which statement should you execute?

A. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR=50, DROP_EXISTING = ON);

B. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=OFF, DROP_EXISTING = ON);

C. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (FILLFACTOR = 0, DROP_EXISTING = ON);

D. ALTER INDEX IX_Orders_ShipDate ON Orders
REBUILD WITH (PAD_INDEX=ON, DROP_EXISTING = ON);

Correct Answer: A

Question 6

You have a table named Table1 that contains one million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type. You have a certificate named Cert1.
You must add a column named Column2 that contains an encrypted version of the data from Column1. You must use two-way encryption. You plan to remove Column1 after you create Column2.
Which five Transact-SQL statements should you run in sequence before you remove Column1? To answer, move the appropriate Transact-SQL statements from the list of Transact-SQL statements to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any correct orders you select.

Correct Answer:

Explanation:
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/t-sql/statements/close-symmetric-key-transact-sql?view=sql-server-2017

Question 7

You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
Favor physical memory when physical memory is available.
Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?

A. CREATE VIEW Table...

B. CREATE TABLE ##Table...

C. CREATE TABLE Table...

D. CREATE PARTITION SCHEME Table...

E. DECLARE TABLE @ Table...

Correct Answer: B

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Question 8

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data.
Each order has a sales total that can only be discovered by querying multiple tables.
You need to ensure that the value of the sales total is returned by executing a query on Orders.
What should you create?

A. A calculated column that uses a table-valued function

B. A calculated column that uses a scalar function

C. A trigger that uses a ranking function

D. A trigger that uses a table-valued function

Correct Answer: A

Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).

Welcome to TestSimulate

Microsoft Developing Microsoft SQL Server Databases (070-464) Free Practice Test