Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

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

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

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

  Prevent the view from being published as part of Microsoft SQL Server replication.

  Sales.Female.Customers must meet the following requirements:

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

  Prevent changes to the columns that the view references.

  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:

  

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

  When you start uspUpdateCustomerSummary, there are no active transactions. The procedure fails at the second update statement due to a CHECK constraint violation on the TotalDepositAccountCount column.

  What is the impact of the stored procedure on the CustomerDetails table?

  A. A. The value of the TotalAccountCount column decreased.
  B. The value of the TotalDepositAccountCount column is not changed.
  C. The statement that modifies TotalDepositAccountCount is excluded from the transaction.
  D. The value of the TotalAccountCount column is not changed.
  E. The value of the TotalDepositAccountCount column is increased.
  F. The statement that modifies TotalAccountCount column is excluded from the transaction.
  G. The value of the TotalDepositAcountCount column is decreased.
  D. The value of the TotalAccountCount column is not changed.

• 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 drop the nonclustered index. Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  We see Actual Number of Row is 3571454, while Estimated Number of Rows is 0. This indicates that the statistics are old, and need to be updated.

• Question 3:

  You are designing a stored procedure for a database named DB1.

  The following requirements must be met during the entire execution of the stored procedure:

  The stored procedure must only read changes that are persisted to the database.

  SELECT statements within the stored procedure should only show changes to the data that are made by the stored procedure.

  You need to configure the transaction isolation level for the stored procedure.

  Which Transact-SQL statement or statements should you run?

  A. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT ON
  B. SET TRANSACTION ISOLATION LEVEL READ COMMITED ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT OFF
  C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
  D. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED ALTER DATABASE SET READ_COMMITED_SNAPSHOT OFF
  B. SET TRANSACTION ISOLATION LEVEL READ COMMITED ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT OFF

  ---

  READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the

  current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

  Incorrect Answers:

  A, D: READ UNCOMMITTED specifies that statements can read rows that have been modified by other transactions but not yet committed.

  References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

• Question 4:

  DRAG DROP

  You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, and DELETE statements.

  You have the following requirements:

  You must run all the stored procedures within the same transaction.

  You must automatically start a transaction when stored procedures include DML statements.

  You must not automatically start a transaction when stored procedures include DDL statements.

  You need to run all three stored procedures.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.

  Select and Place:

  

  

  ---

  Step 1: SET IMPLICIT_TRANSACTIONS ON BEGIN TRAN When ON, the system is in implicit transaction mode. This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION being executed first:

  

  Step 2:

  Run the procedure within the same transaction

  Step 3:

  Commit the transaction

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/set-implicit-transactions-transact-sql

• Question 5:

  DRAG DROP

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

  

  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.

  Select and Place:

  

  

  ---

  Session 1: Event Files

  Event files use to write event session output from complete memory buffers to disk. This is an asynchronous target.

  Session 2: Histogram

  Histogram use to count the number of times that a specified event occurs, based on a specified event column or action. This is an asynchronous target.

  Incorrect Answers:

  ETW:

  The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) to correlate the event data with operating system or application event data.

  Event Counter:

  An event counter counts all specified events that occur during an Extended Events session. Use to obtain information about workload characteristics without adding the overhead of full event collection. This is a synchronous target.

  References:

  https://docs.microsoft.com/en-us/sql/database-engine/sql-server-extended-events-targets?

• Question 6:

  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 Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

  

  The customer table includes a column that stores the data 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 create triggers that meet the following requirements:

  In the table below, identify the trigger types that meet the requirements.

  NOTE: Make only selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement.

  AFTER specifies that the DML trigger is fired only when all operationsspecified in the triggering SQL statement have executed successfully.

  References:

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

• Question 7:

  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? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  From question: Finance department users mustbe able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column isabove a certain threshold.

  CREATE VIEW (Transact-SQL) creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.

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

  References:

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

• Question 8:

  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.

  You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

  Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a

  disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

  You need to improve the application response time.

  Solution: You update the application to use implicit transactions when connecting to the database.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  References: http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs-explicit.html

• Question 9:

  You run the following Transact-SQL following statement:

  

  Customer records may be inserted individually or in bulk from an application.

  You observe that the application attempts to insert duplicate records.

  You must ensure that duplicate records are not inserted and bulk insert operations continue without notifications.

  Which Transact-SQL statement should you run?

  A. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = OFF)
  B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)
  C. CREATE UNIQUE INDEX IX Customer Code ON Customer (Code) WITH (IGNORE DUP KEY =OFF)
  D. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code)
  E. CREATE UNIQUE NONCLUSTERED INDEX IX_Customer_Code ON Customer (Code) WITH (ONLINE = ON)
  B. CREATE UNIQUE INDEX IX_CUSTOMER_Code O Customer (Code) WITH (IGNORE_DUP_KEY = ON)

  ---

  IGNORE_DUP_KEY = { ON | OFF } specifies the error response when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created

  or rebuilt. The option has no effect when executing CREATE INDEX, ALTER INDEX, or UPDATE. The default is OFF.

  Incorrect Answers:

  ONLINE = { ON | OFF } specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

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

• Question 10:

  You are reviewing the execution plans in the query plan cache. You observe the following:

  - There are a large number of single use plans.

  - There are a large number of simple execution plans that use multiple CPU cores.

  You need to configure the server to optimize query plan execution.

  Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.

  Hot Area:

  

  

  ---

  * Optimize for ad hoc workloads The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

  * Cost Threshold for Parallelism Use the cost thresholdfor parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration. 5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoftfrom some time in the 1990s. There's no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.