# [Free] 2019(Nov) EnsurePass Microsoft 70-762 Dumps with VCE and PDF 21-30

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

#### Question No.21

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

Your company has employees in different regions around the world.

You need to create a database table that stores the following employee attendance information:

Employee ID

date and time employee checked in to work date and time employee checked out of work

Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

1. Yes

2. No

# Explanation:

Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock. Syntaxis: datetimeoffset [ (fractional seconds precision) ] For the use quot;datetimeoffsetquot;, the Fractional seconds precision is 7.

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

#### Question No.22

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: Limit the values that are accepted by the Salary column.

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

Do not validate data integrity when running DELETE statements. Solution: You implement a FOR UPDATE trigger on the table.

Does the solution meet the goal?

1. Yes

2. No

# Explanation:

http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update

#### Question No.23

HOTSPOT

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following

Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

1. Create new rows in the table without granting INSERT permissions to the table.

2. Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirments:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging. Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to create an object that allows finance users to be able to retrieve the required data. The object must not have a negative performance impact.

How should you complete the Transact-SQL statements?

# Background

You have a database named HR1 that includes a table named Employee.

You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.

You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.

Employee Table

You use the following Transact-SQL statements to create, configure, and populate the Employee table:

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

UspA: This stored procedure updates only the EmployeeStatus column. UspB: This stored procedure updates only the EmployeePayRate column.

The application uses views to control access to data. Views must meet the following requirements:

Allow user access to all columns in the tables that the view accesses. Restrict updates to only the rows that the view returns.

You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.

You need to generate the reports more quickly. The database must not use additional resources. What should you do?

1. Update the transaction level of the report query session to READPAST.

2. Modify the report queries to use the UNION statement to combine the results of two or more queries.

3. Set the READ_COMMITTED_SNAPSHOT database option to ON.

4. Update the transaction level of the report query session to READ UNCOMMITTED.

# Explanation:

https://technet.microsoft.com/en-us/library/ms173763(v=sql.105).aspx

#### Question No.25

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 os independent of the other questions in this series. Information and details provided in a question apply only to that question.

You have a database named DB1. There is no memory-optimized filegroup in the database. You run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk.

You need to create an index on the Person.Person table that meets the following requirements:

1. All users must be able to benefit from the index.

2. FirstName must be added to the index as an included column. What should you do?

1. Create a clustered index on the table.

2. Create a nonclustered index on the table.

3. Create a nonclustered filtered index on the table.

4. Create a clustered columnstore index on the table.

5. Create a nonclustered columnstoreindex on the table.

6. Create a hash index on the table.

# Explanation:

By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:

They can be data typesnot allowed as index key columns.

They are not considered by the Database Engine when calculating the number of index key columns or index key size.

#### Question No.26

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table. You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

1. Create new rows in the table without granting INSERT permissions to the table.

2. Notify the sales person who places an order whether or not the order was completed. You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging. Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to enable referential integrity for the ProductReview table. How should you complete the relevant Transact-SQL statement?

To answer select the appropriate Transact-SQL segments in the answer area. Select two alternatives.

1. For the first selection select: WITH CHECK

2. For the first selection select: WITH NOCHECK

3. For the second selection select: ON DELETE NO ACTION ON UPDATE CASCADE

5. For the second selection select: ON DELETE NO ACTION ON UPDATE NO ACTION

6. For the second selection select: ON DELETE CASCADE ON UPDATE NO ACTION

# Explanation:

B: We should use WITH NOCHECK as existing records inthe ProductReview table must not be validated with the Product table.

C: Deletes should not be allowed, so we use ON DELETE NO ACTION. Updates should be allowed, so we use ON DELETE NO CASCADE NO ACTION: the Database Engine raises an error, and the updateaction on the row in the parent table is rolled back.

CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.

Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifieswhat

action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } Specifieswhat action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

Note: You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referencedby theProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table.

References:

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

#### Question No.27

HOTSPOT

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

1. Create new rows in the table without granting INSERT permissions to the table.

2. Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

a constraint on the SaleID column that allows the field to be used as a record identifier

a constant that uses the ProductID column to reference the Product column of the ProductTypes table

a constraint on the CategoryID column that allows one row with a null value in the column a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

The table must hold 10 million unique sales orders.

The table must use checkpoints to minimize I/O operations and must not use transaction logging. Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to update the SalesHistory table

How should you complete the Transact_SQL statement?

#### Question No.28

Note: This question is part of a series of questions that present the same scenario. Each question in this series contains a unique solution. Determine whether the solution meets the stated goals.

The Account table was created using the following Transact-SQL statement:

There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

You frequently run the following Transact-SQL SELECT statements:

You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

1. Yes

2. No

# Explanation:

Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one.

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

#### Question No.29

DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in the series.

You have a database named Sales that contains the following database tables. Customer, Order, and Products. The Products table and the order table shown in the following diagram.

The Customer table includes a column that stores the date for the last order that the customer placed.

You plan to create a table named Leads. The Leads table is expected to contain approximately 20,000 records. Storage requirements for the Leads table must be minimized.

You need to begin to modify the table design to adhere to third normal form. Which column should you remove for each table?

To answer drag the appropriate column names to the correct locations. Each column name 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.

#### Question No.30

DRAG DROP

Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.

You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers. Website.Customer must meet the following requirements:

1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

2. Allow changes to the columns that the view references. Modified data must be visible through the view.

3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

2. Prevent changes to the columns that the view references.

3. Only allow updates through the views that adhere to the view filter.

You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

The following table displays a selected columns and rows from the Customer table. Thr value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts.

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

Users report that the following SELECT statement statement takes a long time to complete:

You need to create an index that meets the following requirements:

1. Improve the performance of the SELECT statement.

2. requires minimum index key size.

3. Only contains active customers.

4. Makes no change to the existing primary key.

5. Contains all of the columns required by the SELECT statement.

Which three Transact_SQL segments should you use to develop the solution?

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