Microsoft Developing SQL Databases - 070-762 Exam Practice Test
You are evaluating the performance of a database environment.
You must avoid unnecessary locks and ensure that lost updates do not occur.
You need to choose the transaction isolation level for each data scenario.
Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
You must avoid unnecessary locks and ensure that lost updates do not occur.
You need to choose the transaction isolation level for each data scenario.
Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Correct Answer:
Explanation
Box 1: Readcommitted
Read Committed: A transaction T1 executing under this isolation level can only access committed data.
Pros: Good compromise between concurrency and consistency.
Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.
Box 2: Read Uncommitted
Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s).
Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.
Cons: Data is not guaranteed to be transactionally consistent.
Box 3: Serializable
Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can't be acquired (i.e. no index on the predicate column) for the duration of the transaction.
Pros: Full data consistency including phantom protection.
Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.
References:
https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transaction-isolation-levels/
Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing an application to track customer sales.
You need to create an object that meets to following requirements:
* Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
* Run written a transaction and roll back if a failure occurs.
* Run when a table is created or modified.
What should you create?
You are developing an application to track customer sales.
You need to create an object that meets to following requirements:
* Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
* Run written a transaction and roll back if a failure occurs.
* Run when a table is created or modified.
What should you create?
Correct Answer: C
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You have a database with tables. You must insert data into the table by using views.
Which of the following two statements best describes an updatable view?
NOTE: Each correct selection is worth on point.
Which of the following two statements best describes an updatable view?
NOTE: Each correct selection is worth on point.
Correct Answer: A,C
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets the stated goals.
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_usage_stats dynamic management view.
Does the solution meet the goal?
You need to identify the indexes that are not being used so that you can remove them from the database.
Solution: You run the sys.dm_db_index_usage_stats dynamic management view.
Does the solution meet the goal?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
The Account table was created by using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);
Does the solution meet the goal?
The Account table was created by using the following Transact-SQL statement:
There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.
You frequently run the following Transact-SQL SELECT statements:
You must avoid table scans when you run the queries.
You need to create one or more indexes for the table.
Solution: You run the following Transact-SQL statement:
CREATE CLUSTERED INDEX PK_Account ON Account(ProductCode);
Does the solution meet the goal?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You are experiencing performance issues with the database server.
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?
You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.
What should you create?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You manage a Microsoft Azure SQL Database that has the Standard tier plan.
The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
* CPU utilization
* Disk storage
* Memory utilization
* Disk I/O
......................................
The database size has increased and users experience slow performance.
You need to identify usage for the following resources:
* CPU utilization
* Disk storage
* Memory utilization
* Disk I/O
......................................
Correct Answer: C,D
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
Note: this question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in the series. Information and details provided in a question apply only to that question.
You are developing and application to track customer sales.
You need to create an object that meets the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run written a transaction and roll back if a future occurs.
- Run when a table is created or modified.
What should you create?
You are developing and application to track customer sales.
You need to create an object that meets the following requirements:
- Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.
- Run written a transaction and roll back if a future occurs.
- Run when a table is created or modified.
What should you create?
Correct Answer: C
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
You run the following Transact-SQL statement:
There are multiple unique OrderlD values. Most of the UnitPrice values for the same OrderlD are different.
You need to create a single index seek query that does not use the following operators:
*Nested loop
*Sort
*Key lookup
There are multiple unique OrderlD values. Most of the UnitPrice values for the same OrderlD are different.
You need to create a single index seek query that does not use the following operators:
*Nested loop
*Sort
*Key lookup
Correct Answer: B
You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.
Every evening, stores must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the storeID, Production. Qtysold. Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?
Every evening, stores must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the storeID, Production. Qtysold. Totprodsales, and Datesold columns.
You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.
What should you include in your solution?
Correct Answer: B
Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Your company has employees in different regions around the world.
You need to create a database table that stores the following employee attendance information:
- Employee ID
- date and time employee checked in to work
- date and time employee checked out of work
Date and time information must be time zone aware and must not store fractional seconds.
Solution: You run the following Transact-SQL statement:
Does the solution meet the goal?
Correct Answer: B
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).
Note: The question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other question in the series. Information and details provided in a question apply only to that question.
You have a reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.
What should you do?
You have a reporting database that includes a non-partitioned fact table named Fact_Sales. The table is persisted on disk.
Users report that their queries take a long time to complete. The system administrator reports that the table takes too much space in the database. You observe that there are no indexes defined on the table, and many columns have repeating values.
You need to create the most efficient index on the table, minimize disk storage and improve reporting query performance.
What should you do?
Correct Answer: A
Explanation: Only visible for ExamsLabs members. You can sign-up / login (it's free).