Microsoft Developing Microsoft SQL Server Databases (70-464) Free Practice Test
Question 1
You have a SQL Server 2012 database named DB1. You have a backup device named Device1.
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
You discover that the log file for the database is full.
You need to ensure that DB1 can complete transactions. The solution must not affect the chain of log sequence numbers (LSNs).
Which code segment should you execute?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 2
You are evaluating the index design.
You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute.
Which line of code should you use to replace line 08 of Indexes.sql?
You need to recommend a change to Indexes.sql that minimizes the amount of time it takes for usp_SelectEmployessByName to execute.
Which line of code should you use to replace line 08 of Indexes.sql?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 3
You have a SQL Server 2012 database named database1.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Users report that queries that usually take less than one second to execute, take more than 30 seconds to execute.
You need to view the server resource consumption when the queries are executed.
What should you do?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation:
Note:
* (step1, step 2) Both the Profiler trace and the Performance Monitor logs should be started and stopped at about the same time.
* (step 3, step 4) Once you have completed capturing the data for both tools, you are ready to perform the correlation analysis.
* (step 5) How to Correlate SQL Server Profiler Data with Performance Monitor Data Correlating Performance Monitor and Profiler data is a straightforward process that simply involves importing both sets of data into Profiler. Start Profiler and load the trace file you want to correlate.
From the main menu of Profiler, select File | Import Performance Data,
* With SQL Server Profiler, we have the tools to identify the causes of such spikes. We can import Performance Monitor log data and compare it directly with Profiler activity. If we see a spike in CPU utilization, we can identify which statement or statements were running at the same time, and diagnose potential problems.
Question 4
You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 5
You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
* Use only tables that are not persistent in the database.
* Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
The modification must support the following guidelines:
* Use only tables that are not persistent in the database.
* Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
Correct Answer: A
Question 6
You use SQL Server 2014 to maintain the data used by applications at your company.
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client.
What should you do?
You need to run two separate SQL statements.
You must guarantee that the following three things happen:
1. Either BOTH statements succeed or BOTH statements fail as a batch.
2. If an error occurs on the first statement, SQL should not attempt to run the second statement.
3. Error information should be returned to the client.
What should you do?
Correct Answer: C
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 7
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?
What should you add to usp.GetOrdersAndItems?
Correct Answer: B
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 8
You plan to create a new table that will contain a column named Salary. Salary will contain highly sensitive data.
Salary must meet the following requirements:
Contain numeric data.
Contain only encrypted data that remains encrypted in memory.
You need to identify which encryption type and data type must be used for Salary.
Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.
Salary must meet the following requirements:
Contain numeric data.
Contain only encrypted data that remains encrypted in memory.
You need to identify which encryption type and data type must be used for Salary.
Which encryption type and data type should you identity?
To answer, drag the appropriate encryption type and data type to the correct identifier in the answer area.
Correct Answer:
Question 9
You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements:
Have a default value of TRUE.
Minimize the amount of disk space used.
Which code segment should you use?
Have a default value of TRUE.
Minimize the amount of disk space used.
Which code segment should you use?
Correct Answer: A
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 10
You execute the following code.
After populating the Employees table with 10,000 rows, you execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
After populating the Employees table with 10,000 rows, you execute the following query:
You need to reduce the amount of time it takes to execute the query.
What should you do?
Correct Answer: A
Question 11
You have a SQL Server 2012 instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: B
Question 12
You need to create a function that will use a SELECT statement in ProductsByProductType.sql.
Which code segment should you use to complete the function?
Which code segment should you use to complete the function?
Correct Answer: D
Explanation: Only visible for TestSimulate members. You can sign-up / login (it's free).
Question 13
You need to implement a solution that solves the performance issues of
usp_GetOrdersAndItems.
Which statements should you execute?
usp_GetOrdersAndItems.
Which statements should you execute?
Correct Answer: B
Question 14
You create a view by using the following code:
Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?
Several months after you create the view, users report that the view has started to return unexpected results.
You discover that the design of Table2 was modified since you created the view.
You need to ensure that the view returns the correct results.
Which code segment should you run?
Correct Answer: A
Question 15
You have a database named database1. Each table in database1 has one index per column.
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
Identify unused indexes.
Identify indexes that need to be defragmented.
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Users often report that creating items takes a long time.
You need to perform the following maintenance tasks:
Identify unused indexes.
Identify indexes that need to be defragmented.
What should you use?
To answer, drag the appropriate function to the correct management task in the answer area. (Answer choices may be used once, more than once, or not at all.)
Correct Answer:
Explanation:
Note:
* sys.dm_db_index_usage_stats
Returns counts of different types of index operations and the time each type of operation was last performed.
* sys.dm_db_index_physical_stats
Returns size and fragmentation information for the data and indexes of the specified table or view.