070-465 by Microsoft Valid Free Exam Practice Test

Question 1

Which isolation level should you recommend?

A. Read committed B. Read uncommitted C. Repeatable read D. Serializable

Correct Answer: C

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

Question 2

You need to configure security on the Product table for customer support representatives.
Which two actions should you perform? Each correct answer presents part of the solution. Choose two.

A. GRANT SELECT on CustProduct TO OurDomain\CustomerSupport. B. GRANT SELECT on CustProduct TO public. C. GRANT ALL on CustProduct TO OurDomain\CustomerSupport. D. Create a view called CustProduct that includes columns ProductID. ProductName, Product Description, QuantityOnHand, ProductPrice, ProductCost, and ProductSupplierID. E. Create a view called CustProduct that includes columns ProductID, ProductName, Product Description, QuantityOnHand, and ProductPrice. F. Create a user-defined data type called CustProduct that includes columns ProductID, ProductName, Product Description, and ProductPrice.

Correct Answer: A,D

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

Question 3

During performance testing, you discover that database INSERT operations against the Inventory table are slow.
You need to recommend a solution to reduce the amount of time it takes to complete the INSERT operations.
What should you recommend?

A. Create a column store index.Master Data Services B. Partition the nonclustered index. C. Partition the Inventory table.snapshot replication D. Drop the clustered index.change data capture

Correct Answer: B

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

Question 4

DRAG DROP
You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
- Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. - Table2 will contain a column named Column2 which is the product of two other columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.

Correct Answer:

Question 5

You have a database named DB1.
You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next.
Occasionally, the identifying value must be reset to its initial value.
You need to design a mechanism to hold the identifying values for the stored procedure to use.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

A. Create a fourth table that holds the next value in the sequence. At the end each transaction, update the value by using the stored procedure. Reset the value as needed by using an UPDATE statement. B. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Increment the sequence object to the next value by using an ALTER SEQUENCE statement. Reset the value as needed by using a different ALTER SEQUENCE statement. C. Create an identity column in each of the three tables. Use the same seed and the same increment for each table. Insert new rows into the tables by using the stored procedure. Use the DBCC CHECKIDENT command to reset the columns as needed. D. Create a sequence object that holds the next value in the sequence. Retrieve the next value by using the stored procedure. Reset the value by using an ALTER SEQUENCE statement as needed.

Correct Answer: D

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

Question 6

You are designing a database named DB1.
Changes will be deployed to DB1 every Wednesday night.
You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements:
The strategy must not disrupt backup operations.
DB1 must be online while the changes are deployed.
You must be able to undo quickly any changes made to objects.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
---

A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database. B. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database. C. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot. D. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.

Correct Answer: D

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

Question 7

You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?

A. Master Data Services B. Change data capture C. Statistics D. A trigger

Correct Answer: B

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

Microsoft Designing Database Solutions for Microsoft SQL Server - 070-465 Exam Practice Test