Microsoft 70-762 Exam Practice Test Instant Access

Question 1

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:

* The table must reference the ProductID column in the Product table

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

* 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:

* 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:

* 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 modify the environment to meet the requirements for the Orders table.

What should you create?

Aa stored procedure with the RETURN statement

Ba FOR UPDATE trigger

Can AFTER UPDATE trigger

Da user defined function

Answer : D

Question 2

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 table that has a clustered index and a nonclustered index. The indexes use different columns from the table. You have a query named Query1 that uses the nonclustered index.

Users report that Query1 takes a long time to report results. You run Query1 and review the following statistics for an index seek operation:

You need to resolve the performance issue.

Solution: You rebuild the clustered index.

Does the solution meet the goal?

AYes

BNo

Answer : B

The query uses the nonclustered index, so improving the clustered index will not help.

We should update statistics for the nonclustered index.

Question 3

You have multiple queries that take a long time to complete.

You need to identify the cause by using detailed information about the Transact-SQL statements in the

queries. The Transact-SQL statements must not run as part of the analysis.

Which Transact-SQL statement should you run?

ASET STATISTICS PROFILE OFF

BSET SHOWPLAN_TEXT OFF

CSET SHOWPLAN_ALL ON

DSET STATISTICS PROFILE ON

Answer : C

SET SHOWPLAN_ALL ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed and provides estimates of the resource requirements for the statements.

Incorrect Answers:

D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution.

Displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures.

References:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql

Question 4

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:

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?

ACreate clustered indexes on TBL1 and TBL2.

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

CCreate a nonclustered index on TBL2 only.

DCreate UNIQUE constraints on both TBL1 and TBL2.
Create a partitioned view that combines columns from TBL1 and TBL2.

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

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

GCreate CHECK constraints on both TBL1 and TBL2.
Create a partitioned view that combines columns from TBL1 and TBL2.

HCreate an indexed view that combines columns from TBL1 and TBL2.

Answer : G

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 <col>) must appear in all the select lists in the same ordinal position. This <col> should be defined in a way that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on <col>, respectively.

References:

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

Question 5

You are optimizing the performance of a batch update process. You have tables and indexes that were created by running the following Transact-SQL statements:

The following query runs nightly to update the isCreditValidated field:

You review the database and make the following observations:

* Most of the IsCreditValidated values in the Invoices table are set to a value of 1.

* There are many unique InvoiceDate values.

* The CreditValidation table does not have an index.

* Statistics for the index IX_invoices_CustomerID_Filter_IsCreditValidated indicate there are no individual seeks but multiple individual updates.

You need to ensure that any indexes added can be used by the update query. If the IX_invoices_CustomerId_Filter_IsCreditValidated index cannot be used by the query, it must be removed. Otherwise, the query must be modified to use with the index.

Which three actions should you perform? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

AAdd a filtered nonclustered index to Invoices on InvoiceDate that selects where IsCreditNote= 1 and
IsCreditValidated = 0.

BRewrite the update query so that the condition for IsCreditValidated = 0 precedes the condition for
IsCreditNote = 1.

CCreate a nonclustered index for invoices in IsCreditValidated, InvoiceDate with an include statement using
IsCreditNote and CustomerID.

DAdd a nonclustered index for CreditValidation on CustomerID.

EDrop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.

Answer : A, B, E

A filtered index is an optimized nonclustered index especially suited to cover queries that select from a welldefined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes

Question 6

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?

ACREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus, UserName);

BCREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserStatus) INCLUDE (UserName);

CCREATE NONCLUSTERED INDEX IX_User ON Users (CountryCode, UserName);

DCREATE NONCLUSTERED INDEX IX_User ON Users (UserStatus, CountryCode) INCLUDE (UserName);
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

Answer : B

Question 7

You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a raid array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.

You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

You need to examine delays in executed threads, including errors with specific queries and batches.

ACreate a sys.dm_os_waiting^tasks query.

BCreate a sys.dm_exec_sessions query.

CCreate a Performance Monitor Data Collector Set.

DCreate a ays .dm_os_memory_objeccs query.

ECreate a sp_configure 'max server memory' query.

Answer : B

Microsoft 70-762 Developing SQL Databases Exam Practice Test