[Free] EnsurePass Microsoft 70-762 Real Exam Dumps Questions 121-130

February 7, 2020   |   by admin

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

Question No.121

You are developing an ETL process to cleanse and consolidate incoming data. The ETL process will use a reference table to identify which data must be cleansed in the target table. The server that hosts the tables restarts daily.

You need to minimize the amount of time it takes to execute the query and the amount of time it takes to populate the reference table.

What should you do?

  1. Convert the target table to a memory-optimized table. Create a natively compiled stored procedure to cleanse and consolidate the data.

  2. Convert the reference table to a memory-optimized table. Set the durability option to SCHEMA_AND_DATAgt;

  3. Create a native compiled stored procedure to implement the ETL process for both tables.

  4. Convert the reference table to a memory-optimized table. Set the durability option to SCHEMA_ONLY.

Correct Answer: D

Question No.122

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 have a database that contains a table named Employees. The table stores information about the employees of your company.

You need to implement and enforce the following business rules:

image

Limit the values that are accepted by the Salary column.

image

image

Prevent salaries less than $15,000 and greater than $300,000 from being entered. Determine valid values by using logical expressions.

image

Do not validate data integrity when running DELETE statements. Solution: You implement a foreign key on the table.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

Explanation:

A check constraint is needed.

References: https://en.wikipedia.org/wiki/Check_constraint

Question No.123

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.

  1. The view must not include the primary key of the table.

  2. The view must include GROUP By or HAVING statements.

  3. Each view must reference columns for one table.

  4. The view may include a composite filed.

  5. The view must not include subqueries.

Correct Answer: CD

Question No.124

HOTSPOT

You have a Microsoft SQL Server database that has a table named Sales. The table is used for retrieving data and is updated during non business hours.

You run the following Transact-SQL statement:

image

You analyze the execution plan for the statement. (Click the Exhibit button).

image

You need to add an index that optimizes performance. How should you complete the Transact-SQL statement?

image

Correct Answer:

image

Question No.125

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 s5les data to the corporate office. The data must be inserted into the summary table that includes the StorelD, ProductID, 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?

  1. Create a unique constraint.

  2. Create a foreign key constraint to the storeID column in each of the store tables.

  3. Create a rule and bind it to the storeID column.

  4. Create a check constraint.

Correct Answer: A

Explanation:

You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

Question No.126

HOTSPOT

You are performing a code review for Transact-SQL statements.

What action does the constraint in each statement perform? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

image

Correct Answer:

image

Question No.127

Database users report that SELECT statements take a long time to return results. You run the following Transact-SQL statement:

You need to create one nonclustered covering index that contains all of the columns in the above table. You must minimize index key size.

Which Transact-SQL statement should you run?

  1. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);

  2. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);

  3. CREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);

  4. CREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);

Correct Answer: D

Explanation:

Use the UserStatus as the first column in the index, as it is an in_equality column.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included- columns

Question No.128

DRAG DROP

You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The ChillerSensorNumber column stores the identifier for the IoT devices.

You need to create an indexed view that meets the following requirements:

image

image

image

Persists the data on disk to reduce the amount of I/O Provides the number of ChillerSensorNumber items Creates only a set of summary rows

How should you complete the view definition? 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 bat between panes or scroll to view content.

image

Correct Answer:

image

Question No.129

You have a table stores transactions partitions by year.

Users occasionally experience performance issues when they access the table. The table is located exclusively when the records are updated.

You need to prevent exclusive locks on the table and maintain data integrity. What should you do?

  1. Set the LOCK_EXCALARTION property to AUTO.

  2. Set the LOCK_EXCALATION property to TABLE.

  3. Set the DATA COMPRESSION property to RW at the partition level.

  4. Set the DATA_COMPRESSION property to PAGE at the partition level.

  5. Set the LOCK_EXCALATION property to DISABLE.

Correct Answer: D

Question No.130

Note: This question is part of a series of questions that use the same or similar answer choices. As 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.

image

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

image

Users frequently run the following query:

Users report that the query takes a long time to return results.

You need to minimize the amount of time requires 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

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

Leave Your Comment