You need to build a function that meets the following requirements:
*Returns multiple rows
*Optimizes the performance of the query within the function
How should you complete the Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment 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.
Solution:
Does this meet the goal?
Correct Answer:
A
You use Query Store to optimize a query in a database. The query has two execution plans:
Plan 2 is shown in the Plan 2 Execution Plan exhibit.
Plan 10 is shown in the Plan 10 Execution Plan exhibit.
You create an index at 22:24 based on the missing index suggestion in Plan 2.
The average duration statistics for the query is shown in the Tracked Queries exhibit. (Click the Exhibit button.)
You need to analyze the operators in the two execution plans.
For each of the following statements, select Yes if the statement is true. Otherwise, select No. Plan 2 Execution Plan
Plan 10 Execution Plan
Tracked Queries
Solution:
Does this meet the goal?
Correct Answer:
A
You have a database that contains both disk-based and memory-optimized tables.
You need to create two modules. The modules must meet the requirements described in the following table.
Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.
Solution:
Returning Data by Using OUTPUT Parameters
If you specify the OUTPUT keyword for a parameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.
SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION option to disguise the contents of a particular procedure or function from discovery.
Native Compilation of Tables and Stored Procedures
In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that access memory-optimized tables. SQL Server is also able to natively compile
memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.
References: https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx https://msdn.microsoft.com/en-us/library/dn249342.aspx
Does this meet the goal?
Correct Answer:
A
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.
Solution:
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/
Does this meet the goal?
Correct Answer:
A
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a 3-TB database. The database server has 64 CPU cores. You plan to migrate the database to Microsoft Azure SQL Database.
You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.
Solution: You select the Basic service tier. Does this meet the goal?
Correct Answer:
B
Premium service is required for 3 TB of storage. Single database DTU and storage limits
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu
