Microsoft Designing Database Solutions for Microsoft SQL Server (070-465) Free Practice Test
Question 1
You are designing your maintenance plan.
Which command should you use only during the monthly maintenance window?
Which command should you use only during the monthly maintenance window?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 2
You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics
setting on the database named DB1 has been turned off. You see no evidence that any
maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could
have been useful if they existed. What should you do?
You conduct an assessment on the instance and determine that the auto create statistics
setting on the database named DB1 has been turned off. You see no evidence that any
maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could
have been useful if they existed. What should you do?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 3
HOTSPOT
You use SQL Server 2014. You create a table within a database by using the following
DDL:
The following table illustrates a representative sample of data:
The system is expected to handle 50 million orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
You use SQL Server 2014. You create a table within a database by using the following
DDL:
The following table illustrates a representative sample of data:
The system is expected to handle 50 million orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
Correct Answer:
Question 4
Your network contains an Active Directory domain that has two groups named Group1 and Group2.
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
The domain contains two SQL Server instances named SQLDev and SQLProd. Each SQL Server instance has access to various storage media.
The SQL Server instances have a database that contains a table named Table1.
Table1 contains a column named Column1. The value for Column1 can be either Value1 or Value2.
You need to recommend a solution to ensure that users in Group1 can retrieve only rows from Column1 that contain the value of Value1.
What should you recommend?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 5
DRAG DROP
You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
- If the update fails, an error must be raised to the application and the update must be discarded. - The stored procedure must be designed to maximize concurrency.
What should you include in your design?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
- If the update fails, an error must be raised to the application and the update must be discarded. - The stored procedure must be designed to maximize concurrency.
What should you include in your design?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Question 6
You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 installed.
You plan to deploy an additional server that has SQL Server 2014 installed.
You need to design a high-availability solution for the databases that meets the following
requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.
You plan to deploy an additional server that has SQL Server 2014 installed.
You need to design a high-availability solution for the databases that meets the following
requirements:
If SQL1 fails, the databases must be available.
Users must be able to run reports against a secondary copy of the databases.
What should you include in the design?
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
DRAG DROP
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.
Table1 contains the indexes shown in the following table.
You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend?
To answer, drag the appropriate index storage type to the correct index in the answer area.
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.
Table1 contains the indexes shown in the following table.
You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.
Which type of index storage should you recommend?
To answer, drag the appropriate index storage type to the correct index in the answer area.
Correct Answer:
Question 8
You need to recommend a solution that resolves the missing data issue. The solution must minimize the amount of development effort.
What should you recommend?
What should you recommend?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 9
You are creating a database that will store usernames and passwords for an application.
You need to recommend a solution to store the passwords in the database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
You need to recommend a solution to store the passwords in the database.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 10
You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.
Each order is tracked by using one of the following statuses:
----
Fulfilled Shipped Ordered Received
You need to design the database to ensure that that you can retrieve the following information:
---
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
Each order is tracked by using one of the following statuses:
----
Fulfilled Shipped Ordered Received
You need to design the database to ensure that that you can retrieve the following information:
---
The current status of an order
The previous status of an order.
The date when the status changed.
The solution must minimize storage.
More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: B