Free Microsoft 70-762 Exam Dumps Questions & Answers

You are designing a data warehouse that will be clustered across four servers. Several of the tables in the data warehouse contain transient data that you can rebuild as needed.
The schema for the data changes periodically. You must minimize the work required to make schema changes and deploy those changes across the server farm. Administrators must be able to make the following schema changes:
* Adding columns to tables
* Removing columns from tables
* Adding tables
* Removing tables
Changes to the transient data are done as singleton operations. You must make the data available on all the servers in the server farm. Data movement between servers must occur in real time or near real time.
You must be able to run SELECT statements against the data from any server at any time.
You need to configure the environment.
Which technologies should you implement? To answer, select the appropriate technologies in the answer area.
NOTE: Each correct selection is worth one point.

Correct Answer:

Explanation

Box 1: Durable in-Memory OLTP
Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with Always On Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.
Box 2: Always on Availability groups
The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.
An availability group fails over at the level of an availability replica.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memo
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups

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?

• A.CLR procedure
• B.table-valued function
• C.DDL trigger
• D.user-defined procedure
• E.extended procedure
• F.scalar-valued function
• G.DML trigger

You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data.
What should you do?

• A.Set SET ALLOW SNAPSHOT ISOLATION to ON.
• B.Set SET XACTJVBORT to ON.
• C.Execute the alter table ti set (Lock_ESCALATION = auto); statement.
• D.Use the max worker threads Option.
• E.Use the NOLOCK option.
• F.Use a table-valued parameter.
• G.Use the output parameters.
• H.Execute the DBCC UPDATEUSAGE statement.

You have multiple stored procedures inside a transaction.
You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.
What should you do?

• A.Use the max worker threads Option.
• B.Use the NOLOCK option.
• C.Use a table-valued parameter.
• D.Use the output parameters.
• E.Set SET XACT ABORT to ON.
• F.Execute the DBCC UPDATEUSAGE statement.
• G.Set SET ALLOW SNAPSHOT ISOLATION to ON,
• H.Execute the alter table T1 set (lock escalation = auto); statement.

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.

Correct Answer:

Explanation