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

Question No.151

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:

image

Application

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

image

image

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:

image

image

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

image

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 all FROM clauses of the DML statements to use the IGNORE_CONSTRAINTS table hint.

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

    queries.

  3. Apply a nonclustered index to all tables used in the report queries.

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

Correct Answer: D

Explanation:

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. This is the least restrictive of the isolation levels.

References:

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

Question No.152

DRAG DROP

You must troubleshoot performance issues that users report. You identify the following representative user sessions:

image

You need to configure the appropriate Extended Events.

Which target should you use for each session? To answer, drag the appropriate Extended Event targets to the correct sessions. Each Extended Event target may be used once, or not at all. You may need to drag the split bar between panes or scroll to view content.

image

Correct Answer:

image

Question No.153

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 by using the following Transact-SQL statement:

image

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:

image

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:

image

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

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.154

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:

image

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

image

The table must reference the ProductID column in the Product table

image

image

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

ProductReview table.

image

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:

image

image

Create new rows in the table without granting INSERT permissions to the table. 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:

image

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

image

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

image

image

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:

image

The table must hold 10 million unique sales orders.

image

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

image

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.

image

Correct Answer:

image

Question No.155

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. DDL trigger

  6. scalar-valued function

  7. table-valued function

Correct Answer: B

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.

A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.

References:

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

Question No.156

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 named DB1 that includes a table named Sales-Orders. You grant a user named User1 select permissions on the sales schema.

You need to ensure that User1 can select data from the sales .orders table without specifying the schema name in any Transact-SQL statements.

Solution: You create a stored procedure in the sales schema that selects the data from the sales. orders table.

Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: A

Question No.157

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 tables described in the following table:

image

There are no foreign key relationships between TBL1 and TBL2.

You need to minimize the amount of time required for queries that use data from TBL1 and TBL2 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 noncluster 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.

Correct Answer: B

Question No.158

You manage a database that supports an internal of Things (IOT) solution. The database records metrics from over 100 million devices every minute. The database requires 99.995 uptime.

The database uses a table named checkins that is 100 gigabytes (GB) in size. The checkins table stores metrics from the devices. The database also has a table named Archive that stores four terabytes (TB) of data. You use store produce for all access to the tables.

You observe that the wait type PAGELATCH_ to causes large amount of blocking. You need to resolve the blocking issues while minimizing downtime for the database.

Which two actions should you perform? Each correct answer present part of the solution.

  1. Convert the checkins table to an in memory OLTP table.

  2. Convert all tables to clustered columnstore indexes.

  3. Convert the checkins table to a cluster clounmstore indexes.

  4. Convert all stored procedure that accesses the checkins table to natively compile procedures.

Correct Answer: D

Question No.159

You have a database with multiple tables. You must insert data into the tables by using views. Which of the following two statements best describes an updatable view?

NOTE: Each correct selection is worth one point.

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

  2. The view may include GROUP BY or HAVING statements.

  3. The view may include a composite field.

  4. Each view must reference columns from one table.

  5. The view must not include subqueries.

Correct Answer: BD

Explanation:

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

Question No.160

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 named DB1 that includes a table named sales .orders. You grant a user

named User1 select permissions on the Sales schema.

You need to ensure that User1 can select data from the sales .orders table without specifying the schema name in any Transact -SQL statements.

Solution: You set the default database for User1 to DB1. Does the solution meet the goal?

  1. Yes

  2. No

Correct Answer: B

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.