Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 31:

  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 OrderDetails 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 segment:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Need to handle the case where the first two updates (OrderHeader, OrderDetail) are successful, but either the 3rd or the 4th (OrderHeader, OrderDetail) fail. We add the @CustomerComplete variable in the BEGIN TRY block, and test it 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 32:

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

  You have a database named Sales that contains the following database tables. Customer, Order, and Products. The Products table and the order table shown in the following diagram.

  

  The Customer table includes a column that stores the date 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 begin to modify the table design to adhere to third normal form.

  Which column should you remove for each table? To answer? drag the appropriate column names to the correct locations. Each column name 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:

  

  

  ---

  In the Products table the SupplierName is dependant on the SupplierID, not on the ProductID.

  In the Orders table the ProductName is dependant on the ProductID, not on the OrderID.

  Note:

  A table is in third normal form when the following conditions are met:

  * It is in second normal form.

  * All nonprimary fields are dependent on the primarykey.

  Second normal form states that it should meet all the rules for First 1Normnal Form and there must be no partial dependences of any of the columns onthe primary key.

  First normal form (1NF) sets the very basic rules for an organized database:

  * Define the data items required, because they become the columns in a table. Place related data items in a table.

  * Ensure that there are no repeating groups ofdata.

  * Ensure that there is a primary key.

  References:https://www.tutorialspoint.com/sql/third-normal-form.htm

• Question 33:

  You create a database table named FactSales by running the following Transact-SQL statements:

  

  You must optimize the indexes without making changes to the ix_FactSales_EmployeeKey index.

  You need to implement a columnstore index for the table.

  How should you complete the Transact-SQL code? To answer, select the appropriate Transact-SQL segments in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

• Question 34:

  HOTSPOT

  You plan to create a stored procedure that uses a table parameter as an input parameter. The table value parameter may hold between 1 and 10,000 rows when you run the stored procedure.

  The stored procedure will use the rows within the table value parameter to filter the rows that will be returned by the SELECT statement.

  You need to create the stored procedure and ensure that it runs as quickly as possible.

  How should you complete the procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

  NOTE: Each correct answer selection is worth one point.

  Hot Area:

  

  

• Question 35:

  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 update statistics for the nonclustered index.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

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

  HOTSPOT

  You are designing a data warehouse that will be clustered across four servers. Several of the tables in the data warehouse contain transient data that you can rebuild as needed.

  The schema for the data changes periodically. You must minimize the work required to make schema changes and deploy those changes across the server farm. Administrators must be able to make the following schema changes:

  Adding columns to tables

  Removing columns from tables Adding tables Removing tables

  Changes to the transient data are done as singleton operations. You must make the data available on all the servers in the server farm. Data movement between servers must occur in real time or near real time.

  You must be able to run SELECT statements against the data from any server at any time.

  You need to configure the environment.

  Which technologies should you implement? To answer, select the appropriate technologies in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: Durable in-Memory OLTP Databases containing memory-optimized tables, with or without native compiled stored procedures, are fully supported with Always On Availability Groups. There is no difference in the configuration and support for databases which contain In-Memory OLTP objects as compared to those without.

  Box 2: Always on Availability groups The Always On availability groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Always On availability groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

  An availability group fails over at the level of an availability replica.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/high-availability-support-for-in-memory-oltp-databases

  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/always-on-availability-groups-sql-server

• Question 37:

  You have a nonpartitioned table that has a single dimension. The table is named dim.Products.Projections.

  The table is queried frequently by several line-of-business applications. The data is updated frequently throughout the day by two processes.

  Users report that when they query data from dim.Products.Projections, the responses are slower than expected. The issue occurs when a large number of rows are being updated.

  You need to prevent the updates from slowing down the queries.

  What should you do?

  A. Use the NOLOCK option.
  B. Execute the DBCC UPDATEUSAGE statement.
  C. Use the max worker threads option.
  D. Use a table-valued parameter.
  E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
  A. Use the NOLOCK option.

  ---

  The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes. This can improve query performance, but also introduces the possibility of dirty reads.

  References: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

• Question 38:

  HOTSPOT

  You have a database that contains both disk-based and memory-optimized tables.

  You need to create two modules. The modules must meet the requirements described in the following table.

  

  Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.

  Hot Area:

  

  

• Question 39:

  You have a database named Sales.

  You need to create a table named Customer that includes the columns described in the following table:

  

  How should you complete the Transact SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Box 1: MASKED WITH (FUNCTION ='default()')

  TheDefualt masking method provides full masking according to the data types of the designated fields.

  Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL

  Box 2: MASKED WITH (FUNCTION ='partial(3,"XXXXXX",0)')

  The Custom String Masking method exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix

  examples:

  PhoneNumber varchar(10) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)')

  Box 3: MASKED WITH (FUNCTION ='email()')

  The Email masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. [email protected].

  Example definition syntax: Email varchar(100) MASKEDWITH (FUNCTION = 'email()')

  NULL

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

• Question 40:

  You are developing an application that connects to a database. The application runs the following jobs:

  

  The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.

  If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.

  You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.

  Which trace event class in the Locks event category should you use?

  A. LockAcquired
  B. LockCancel
  C. LockDeadlock
  D. LockEscalation
  A. LockAcquired

  ---

  The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.

  The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention

  issues and should be investigated.