[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 131-140

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

Question No.131

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 question 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 increase the number of log files for the database. You store the log files across multiple disks.

Does this meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

Instead, create a file group for the indexes and a file group for the data files.

Question No.132

You have a nonpartitioned table that has a single dimension. The table is named dim. Products.Projections.

The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.

Users report that when they query data from dim.Products.Projections, the responses are slower than expected. The issue occurs when a large number of rows are being updated.

You need to prevent the updates from slowing down the queries. What should you do?

  1. Use the nolock option.

  2. Execute the dbcc updateusage statement.

  3. Use the max worker threads Option.

  4. Use a table-valued parameter.

  5. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.

Correct Answer: A

Explanation:

The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes.

This can improve query performance, but also introduces the possibility of dirty reads. References:

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

Question No.133

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 this series. Information and details provided in a question apply only to that question.

You have a Microsoft SQL Server database named DB1 that contains the following tables:

image

Users frequently run the following query. The users report that the query takes a long time to return results.

image

You need to minimize the amount of time required for the query to return data. What should you do?

  1. Create clustered indexes on TBL1 and TBL2.

  2. Create a clustered index on TBL1. Create a nonclustered index on TBL2 and add the most frequently queried columns as included columns.

  3. Create a nonclustered index on TBL2 only.

  4. Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.

  5. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.

  6. Drop existing indexes on tbli and then create a clustered columnstore index. Create a nonclustered columnstore index on tbli. Make no changes to TBL2.

  7. Create CHECK constraints on both TBL1 and TBL2.. Create a partitioned view that combines columns fromTBL1 and TBL2.

  8. Create an indexed view that combines columns from TBL1 and TBL2.

Correct Answer: H

Question No.134

DRAG DROP

You create tables by using the following Transact-SQL statements:

image

Each customer may have multiple addresses but only one is the primary address. You must plan a solution that meets the following requirements:

image

image

Return both customers and address information. Return only the primary address of the customer.

image

Allow updates of all customer information and address details with the exception of the identity columns and the IsActive column.

Which three actions should you perform is 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.135

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 this series. Information and details provided in a question apply only to that question.

You have a Microsoft SQL Server database named DB1 that contains the following tables:

image

You frequently run the following queries:

image

There are no foreign key relationships between TBL1 and TBL2.

You need to minimize the amount of time required for the two queries to return records from the tables.

What should you do?

  1. Create clustered indexes on TBL1 and TBL2.

  2. Create a clustered index on TBL1 Create a nonclustered index on tbl2 and add the most frequently queried columns as included columns.

  3. Create a nonclustered index on tbl2 only.

  4. Create unique constraints on both TBL1 and TBL2. Create a partitioned view that combines

    columns from TBL1 and TBL2.

  5. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1. Create a nonclustered index on TBL2.

  6. Drop existing indexes on TBL1 and then create a cluwered columnstore index. Create a nonclustered columnstore index on TBL1. Make no changes to TBL2.

  7. Create check constraints on both TBL1 and tbl2. Create a partitioned view that combines columns from TBL1 and tbl2.

  8. Create an indexed view that combines columns from TBL1 and TBL2.

Correct Answer: F

Question No.136

HOTSPOT

You are developing a stored procedure with the following requirements:

image

Accepts an integer as input and inserts the value into a table.

image

image

Ensures new transactions are committed as part of the outer transactions. Preserves existing transactions if the transaction in the procedure fails.

image

If the transaction in the procedure fails, rollback the transaction.

How should you complete the procedure? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.137

DRAG DROP

You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer.

The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016.

You have performance issues with the stored procedure.

You need to resolve the performance issues and ensure the table statistics are updated automatically.

Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

image

Correct Answer:

image

Question No.138

HOTSPOT

You have a Microsoft Azure SQL database. Some queries take a long time to execute.

You need to identify whether any of the long-runffiing queries prevent users from updating data in the database.

Which query should you execute? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.139

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 this series.

Information and details provided in a question apply only to that question.

You have a Microsoft SQL Server database named DB1 that contains the following tables:

image

There are no foreign key relationships between TBL1 and TBL2.

You need to create a query that includes data from both tables and minimizes the amount of time required for the query to return data.

What should you do?

  1. Create clustered indexes on TBL1 and TBL2.

  2. Create a clustered index on TBL1.

    Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.

  3. Create a nonclustered index on TBL2 only.

  4. Create UNIQUE constraints on both TBL1 and TBL2.

    Create a partitioned view that combines columns from TBL1 and TBL2.

  5. Drop existing indexes on TBL1 and then create a clustered columnstore index.

    Create a nonclustered columnstore index on TBL1.Create a nonclustered index on TBL2.

  6. Drop existing indexes on TBL1 and then create a clustered columnstore index. Create a nonclustered columnstore index on TBL1.Make no changes to TBL2.

  7. Create CHECK constraints on both TBL1 and TBL2.

    Create a partitioned view that combines columns from TBL1 and TBL2.

  8. Create an indexed view that combines columns from TBL1 and TBL2.

Correct Answer: G

Explanation:

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

Conditions for Creating Partitioned Views Include: The select list

All columns in the member tables should be selected in the column list of the view definition.

The columns in the same ordinal position of each select list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

Also, at least one column (for example lt;colgt;) must appear in all the select lists in the same ordinal position. This lt;colgt; should be defined in a way that the member tables T1, …, Tn have CHECK constraints C1, …, Cn defined on lt;colgt;, respectively.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

Question No.140

HOTSPOT

You have tables with the following requirements:

image

image

Some records in the table named OrderLines do not exist in the table named Order. The column named OrderNumber must be a unique value in the Order table.

image

The OrderNumber column on the OrderLines table allows null values.

image

The OrderNumber column on the OrderLines table must be validated with the Order table.

image

Must not allow a new record in the OrderLines table that does not match a record in the Order table.

How should you complete the statements? To answer, select the appropriate transact-SQL segments from the drop-down menus in the answer area.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

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