[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 91-100

Get Full Version of the Exam
http://www.EnsurePass.com/70-762.html

Question No.91

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 Premium service tier. Does this meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

Premium service is required for 3 TB of storage. Single database DTU and storage limits

image

References:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

Question No.92

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 database that is 130 GB and contains 500 million rows of data.

Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.

You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out.

Solution: You create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.

Does this meet the goal?

  1. Yes

  2. No

Correct Answer: A

Explanation:

Consider creating two additional File Groups: Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta-data about your objects. You create your Table and Clustered Index (as that is the data for the table) on [Tables] and all Non-Clustered indexes on [Indexes].

Question No.93

You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB.

The table is updated thousands of times a day, but data is not deleted.

The SELECT statements vary in the number of columns used and the amount of rows retrieved.

You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.

Which indexing strategy should you use?

  1. a nonclustered index for each column in the table

  2. a clustered columnstore index for the table

  3. a hash index for the table

  4. a clustered index for the table and nonclustered indexes for nonkey columns

Correct Answer: B

Explanation:

Columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage.

A clustered columnstore index is the physical storage for the entire table.

Generally, you should define the clustered index key with as few columns as possible.

A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes- overview?view=sql-server-2017

Question No.94

You manage a database with tables named Invoice and InvoiceDetails. Each invoice may have multiple records.

Users update the InvoiceDetails table by using a .NET web application. The application retrieves records from both tables and updates the tables by running an inline update statement.

Users experience slow performance when updating records in the application. The solution must meet the following requirements:

image

Must use a stored procedure.

image

image

Must not use inline update statements Must use a table-valued parameter.

image

Must call the stored procedure to update all records. You need to optimize performance.

Which three actions should you perform in sequence?

To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.95

You use Query Store to optimize a query in a database. The query has two execution plans:

image

image

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.

image

For each of the following statements, select Yes if the statement is true. Otherwise, select No. Plan 2 Execution Plan

Plan 10 Execution Plan

image

Tracked Queries

image

image

Correct Answer:

image

Question No.96

You run the following Transact-SQL following statement:

image

Customer records may be inserted individually or in bulk from an application. You observe that the application attempts to insert duplicate records.

You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.

Which Transact-SQL statement should you run?

  1. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH

    (ONLINE = OFF)

  2. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)

  3. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY

    =OFF)

  4. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)

  5. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)

Correct Answer: B

Explanation:

IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

Question No.97

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 database that is 130 GB and contains 500 million rows of data.

Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.

You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out. Solution: You partition the largest tables.

Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Question No.98

You have the following stored procedure that is called by other stored procedures and applications:

image

You need to modify the stored procedure to meet the following requirements:

image

image

Always return a value to the caller. Return 0 if @Status is NULL.

image

Callers must be able to use @Status as a variable.

Which two actions should you perform? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

  1. Replace NULL values with 0. Add a PRINT statement to return @Status.

  2. Add a RETURN statement.

  3. Replace NULL values with 0. Add an output parameter to return @Status.

  4. Replace NULL values with 0. Add a SELECT statement to return @Status.

  5. Add a PRINT statement.

  6. Add a SELECT statement to return @Status.

  7. Add an output parameter to return @Status.

Correct Answer: BC

Explanation:

There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a- stored-procedure?view=sql-server-2017

Question No.99

You are creating the following two stored procedures:

image

A natively-compiled stored procedure

image

An interpreted stored procedure that accesses both disk-based and memory-optimized tables Both stored procedures run within transactions.

You need to ensure that cross-container transactions are possible. Which setting or option should you use?

  1. the SET TRANSACTION_READ_COMMITTED isolation level for the connection

  2. the SERIALIZABLE table hint on disk-based tables

  3. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database

  4. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

Correct Answer: C

Explanation:

Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.

Question No.100

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 a database object that meets the following requirements:

image

image

image

Return a value of 0 if data is inserted successfully into the Customers table. Return a value of 1 if data is not inserted successfully into the Customers table. Support logic that is written by using managed code.

image

Support TRY…CATCH error handling. What should you create?

  1. extended procedure

  2. CLR procedure

  3. user-defined procedure

  4. DML trigger

  5. scalar-valued function

  6. table-valued function

Correct Answer: D

Explanation:

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

References:

https://msdn.microsoft.com/en-us/library/ms178110.aspx

Get Full Version of the Exam
70-762 Dumps
70-762 VCE and PDF

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.