Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 81:

  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.

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

  What should you do?

  A. Create clustered indexes on TBL1 and TBL2.

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

  C. Create a nonclustered index on TBL2 only.

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

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

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

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

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

  Correct Answer: B

  References: http://www.sqlservergeeks.com/sql-server-indexing-for-aggregates-in-sql-server/

• Question 82:

  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.

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

  

  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?

  A. Create clustered indexes on TBL1 and TBL2.

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

  C. Create a nonclustered index on TBL2 only.

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

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

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

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

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

  Correct Answer: H

• Question 83:

  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.

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

  

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

  

  You need to minimize the amount of time required for the query to return data.

  A. Create clustered indexes on TBL1 and TBL2.

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

  C. Create a nonclustered index on TBL2 only.

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

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

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

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

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

  Correct Answer: D

  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.

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

• Question 84:

  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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetail tables in order.

  You need to ensure that the stored procedure:

  Runs within a single transaction.

  Commits updates to the Customer and CustomerInfo tables regardless of the status of updates to the OrderHeader and OrderDetail tables.

  Commits changes to all four tables when updates to all four tables are successful.

  Solution: You create a stored procedure that includes the following Transact-SQL code:

  

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. Can add a variable in the BEGIN TRY block, and test the variable in the BEGIN CATCH block.

  Note: XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed. XACT_STATE =1: the current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

  References: https://docs.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql

• Question 85:

  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 foreign key on the table.

  Does the solution meet the goal?

  A. Yes

  B. No

  Correct Answer: B

  A check constraint is needed.

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

• Question 86:

  You have a view that includes an aggregate.

  You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

  You need to ensure that you can update the view.

  What should you create?

  A. a nonclustered index

  B. a schema-bound view

  C. a stored procedure

  D. an INSTEAD OF trigger

  Correct Answer: B

  Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding.

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

• Question 87:

  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.

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

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

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

  D. Add a nonclustered index for CreditValidation on CustomerID.

  E. Drop the IX_invoices_CustomerId_Filter_IsCreditValidatedIndex.

  Correct Answer: ABE

  A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined 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 88:

  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?

  A. Create clustered indexes on TBL1 and TBL2.

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

  C. Create a nonclustered index on TBL2 only.

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

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

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

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

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

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

  respectively.

  References:

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

• Question 89:

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

  What should you do?

  A. Create clustered indexes on TBL1 and TBL2.

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

  C. Create a nonclustered index on TBL2 only.

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

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

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

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

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

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

  respectively.

  References:

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

• Question 90:

  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?

  A. SET STATISTICS PROFILE OFF

  B. SET SHOWPLAN_TEXT OFF

  C. SET SHOWPLAN_ALL ON

  D. SET STATISTICS PROFILE ON

  Correct 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