Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 101:

  Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some questions sets might have more than one correct solution,

  while others might not have a correct solution.

  After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  You have a 3-TB database. The database server has 64 CPU cores.

  You plan to migrate the database to Microsoft Azure SQL Database.

  You need to select the service tier for the Azure SQL database. The solution must meet or exceed the current processing capacity.

  Solution: You select the Premium service tier.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Premium service is required for 3 TB of storage. Single database DTU and storage limits

  

  References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu

• Question 102:

  Case Study

  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:

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

  Exhibit

  

  Both of the stored procedures experience blocking issues. UspB must not abort if UspA commits changes to a row before UspB commits changes to the same row. UspA must not abort if UspB commits changes to a row before UspA commits changes to the same row.

  You need to specify the transaction isolation levels to enable row versioning.

  How should you complete the Transact-SQL statements? To answer, drag the 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 bar between panes or scroll to view content.

  Select and Place:

  

  

  ---

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

• Question 103:

  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 and application to track customer sales.

  You need to return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.

  You need to create a database object.

  What should you create?

  A. extended procedure
  B. CLR procedure
  C. user-defined procedure
  D. DML trigger
  E. scalar-valued function
  F. table-valued function
  F. table-valued function

  ---

  User-defined scalar functions return a single data value of the type defined in the RETURNS clause.

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

• Question 104:

  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 DML trigger
  B. a schema-bound view
  C. a stored procedure
  D. a DDL trigger
  A. a DML trigger

  ---

  When you create a view, you must obey the following conditions in order to make the view updateable:

  The columns being modified in the view have to directly reference the underlying table columns. As a consequence, the view's columns being modified cannot be the result of an aggregate function or a computed column.

  The only way to make data changes on a non-updateable view is by using INSTEAD OF DML triggers. This way you can use procedural code to overcome the limitation.

  References:

  https://www.mssqltips.com/sqlservertip/5984/sql-server-trigger-on-view-example/

• Question 105:

  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:

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

  Exhibit

  

  You view the Deadlock Graph as shown in the exhibit. (Click the Exhibit button.)

  Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

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

• Question 106:

  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
  B. No

  ---

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

  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:

  1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

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

  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.

  Select and Place:

  

  

  ---

  Box 1: Clustered Index

  With the same size of keys, the nonclustered indexes need more space than clustered indexes.

  Box 2, Box 3:

  Include the CustomerStatus column in the index, and only when CustomerStatusnot equal to 1 (the active customers).

  References:

  http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/

• Question 108:

  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.
  B. Create a clustered index on TBL1.Create a nonclustered index on TBL2 and add the most frequently queried column as included columns.

  ---

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

• Question 109:

  You have two database tables. Table1 is a partioned table and Table 2 is a nonpartioned table.

  Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.

  You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.

  Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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.

  Select and Place:

  

  

  ---

  Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different optionsavailable: TABLE AUTO DISABLE Box 1: Table1, Auto The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level –even when the table is partitioned. If you have your table partitioned, and you want to have aPartition Level Lock Escalation (because you have tested your data access pattern, and you don't cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level. Box 2: Table 2, DISABLE With the option DISABLE you can completely disable the Lock Escalation for that specific table. For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks tothe HoBT level instead of the table or to disable lock escalation.

  References: http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/

• Question 110:

  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:

  

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

  

  You need to add an index that optimizes performance.

  Hot Area:

  

  

  ---

  Explanation:

  Box 1:

  Specifying the order in which key values are stored in an index is useful when queries referencing the table have ORDER BY clauses that specify different directions for the key column or columns in that index. In these cases, the index can remove the need for a SORT operator in the query plan; therefore, this makes the query more efficient.

  The Database Engine can move equally efficiently in either direction. An index defined as (RejectedQty DESC, ProductID ASC) can still be used for a query in which the sort direction of the columns in the ORDER BY clause are reversed. For example, a query with the ORDER BY clause ORDER BY RejectedQty ASC, ProductID DESC can use the index.

  Box 2:

  You can include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 16 key columns and a maximum index key size of 900 bytes. The Database Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

  References: https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx