Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 71:

  You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.

  The environment is configured with the following properties:

  The database is configured with the default isolation setting.

  The application and process use the default transaction handling.

  You observe the application cannot access any rows that are in use by the process.

  You have the following requirements:

  Ensure the application is not blocked by the process.

  Ensure the application has a consistent view of the data

  Ensure the application does not read dirty data.

  You need to resolve the issue and meet the requirements with the least amount of administrative effort.

  What should you do?

  A. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.
  B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.
  C. Enable the application for the WITH (NOLOCK) hint.
  D. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.
  B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.

  ---

  Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).

  READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still

  modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.

  Incorrect Answers:

  A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.

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

• Question 72:

  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. The Account table was created using the following Transact-SQL statement:

  

  There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

  You frequently run the following Transact-SQL SELECT statements:

  

  You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  Create a clustered index on theAccountNumber column as it is unique. Create a nonclustered index that includes the ProductCode column.

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

• Question 73:

  You have a Microsoft Azure SQL Database named MyDb that uses server version V12.

  You plan to use Query Performance Insight to troubleshoot performance problems. The database query store is not enabled.

  You need to enable the database query store to meet the following requirements for the database:

  You connect to the database by using SQL Server Managements Studio.

  How should you complete the Transact-SQL statements? To answer, drag the appropriate 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.

  NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select. Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  

  OR

  

  Both answers are correct.

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

• Question 74:

  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.

  Your company has employees in different regions around the world.

  You need to create a database table that stores the following employee attendance information:

  Employee ID date and time employee checked in to work date and time employee checked out of work

  Date and time information must be time zone aware and must not store fractional seconds. Solution: You run the following Transact-SQL statement: Does the solution meet the goal?

  

  A. Yes
  B. No
  B. No

  ---

  Datetime2 stores fractional seconds.

  Datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and

  optional user-specified precision.

  References: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-2017

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

• Question 75:

  You have a Microsoft Azure SQL Database. You enable Query Store for the database and configure the store to use the following settings:

  SIZE_BASED_CLEANUP_MODE = OFF

  STALE_QUERY_THRESHOLD_DAYS = 60

  MAX_STORAGE_SIZE_MB = 100

  QUERY_CAPTURE_MODE = ALL

  You use Azure Query Performance Insight to review queries. You observe that new queries are not displayed after 15 days and that the Query Store is set to read-only mode.

  If the Query Store runs low on data space, the store must prioritize queries that run regularly or queries that consume applicant resources.

  You must set the Query Store to read_write mode and determine the performance of queries from the past 60 days.

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

  NOTE: Each correct selection is worth one point.

  A. Set the value of the CLEANUP_POLICY setting to (STALE_QUERY_THRESHOLD_DAYS = 75)
  B. Set the value of the QUERY_CAPTURE_MODE setting to AUTO
  C. Increase the value for the MAX_STORAGE_SIZE_MB setting
  D. Set the value of the SIZE_BASED_CLEANUP_MODE setting to AUTO
  E. In the Azure portal, navigate to Query Performance Insight. Use the Custom tab to select a period of 2 months.
  B. Set the value of the QUERY_CAPTURE_MODE setting to AUTO
  C. Increase the value for the MAX_STORAGE_SIZE_MB setting
  D. Set the value of the SIZE_BASED_CLEANUP_MODE setting to AUTO

  ---

  B: Capture mode:

  All ?Captures all queries. This is the default option.

  Auto ?Infrequent queries and queries with insignificant cost are ignored. (Ad hoc recommended)

  None ?Query Store stops capturing new queries.

  C: Max Size (MB): Specifies the limit for the data space that Query Store can consume within the database. This is the most important setting that directly affects operation mode of the Query Store.

  While Query Store collects queries, execution plans and statistics, its size in the database grows until this limit is reached. When that happens, Query Store automatically changes the operation mode to read-only and stops collecting new

  data. You should monitor this closely to make sure you have sized the store appropriately to contain the full history you'd like to retain.

  D: Size Based Cleanup Mode: Specifies whether automatic data cleanup will take place when Query Store data size approaches the limit.

  It is strongly recommended to activate size-based cleanup to makes sure that Query Store always runs in read-write mode and collects the latest data.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

• Question 76:

  DRAG DROP

  You need to implement triggers to automate responses to the following events:

  SQL Server logons

  Database schema changes

  Database updates

  Which trigger types should you use? To answer, drag the appropriate trigger types to the appropriate scenarios. Each trigger type 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.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

  ---

  Box 1: LOGON

  Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server.

  Box 2: INSTEAD OF INSERT

  An "INSTEAD of trigger" is executed instead of the original operation, and not combining with the operation. INSTEAD OF triggers override the standard actions of the triggering statement. It can be used to bypass the statement and execute

  a whole different statement, or just help us check and examine the data before the action is done.

  Box 3: DDL

  DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

  Note:

  You can write triggers that fire whenever one of the following operations occurs:

  DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user

  DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

  Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

  References: https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm https://social.technet.microsoft.com/wiki/contents/articles/28152.t-sql-instead-of-triggers.aspx https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017

• Question 77:

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

  You need to create a database object that meets the following requirements:

  Launch when table data is modified.

  Evaluate the state a table before and after a data modification and take action based on the difference.

  Prevent malicious or incorrect table data operations.

  Prevent changes that violate referential integrity by cancelling the attempted data modification.

  Run managed code packaged in an assembly that is created in the Microsoft.NET Framework and located into Microsoft SQL Server.

  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
  B. CLR procedure

  ---

  You can create a database object inside SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programmingmodel provided

  by the CLR include DML triggers, DDL triggers, stored procedures, functions, aggregate functions, and types.

  Creating a CLR trigger (DML or DDL) in SQL Server involves the following steps:

  Define the trigger as a class in a .NETFramework-supported language. For more information about how to program triggers in the CLR, see CLR Triggers. Then, compile the class to build an assembly in the .NET Framework using the

  appropriate language compiler.

  Register the assembly in SQL Server using the CREATE ASSEMBLY statement. For more information about assemblies in SQL Server, see Assemblies (Database Engine).

  Create the trigger that references the registered assembly.

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

• Question 78:

  You are developing an ETL process to cleanse and consolidate incoming data. The ETL process will use a reference table to identify which data must be cleansed in the target table. The server that hosts the table restarts daily.

  You need to minimize the amount of time it takes to execute the query and the amount of time it takes to populate the reference table.

  What should you do?

  A. Convert the target table to a memory-optimized table. Create a natively compiled stored procedure to cleanse and consolidate the data.
  B. Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_AND_DATA.
  C. Create a native compiled stored procedure to implement the ETL process for both tables.
  D. Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_ONLY.
  D. Convert the reference table to a memory-optimized table. Set the DURABILITY option to SCHEMA_ONLY.

  ---

  If you use temporary tables, table variables, or table-valued parameters, consider conversions of them to leverage memory-optimized tables and table variables to improve performance.

  In-Memory OLTP provides the following objects that can be used for memory-optimizing temp tables and table variables: Memory-optimized tables

  Durability = SCHEMA_ONLY Memory-optimized table variables

  Must be declared in two steps (rather than inline): CREATE TYPE my_type AS TABLE ...; , then DECLARE @mytablevariable my_type;.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/faster-temp-table-and-table-variable-by-using-memory-optimization?view=sql-server-2017

• Question 79:

  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 databse 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 modify the design of the Orders table.

  What should you create?

  A. a stored procedure with the RETURN statement
  B. a FOR UPDATE trigger
  C. an AFTER UPDATE trigger
  D. a user defined function
  D. a user defined function

  ---

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

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

• Question 80:

  DRAG DROP

  You have two databases with the following settings:

  

  You run the following Transact SQL statements:

  

  You need to select data from DiskTable and insert the data into MemTable. You must complete the insertion operation into MemTable as an explicit transaction without immediate durability.

  Which four Transact-SQL segments should you use? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Box 1: BEGIN TRANSACTION Box 2: UPDATE #Disktable Box 3: IFSELECT INTO Disktable Box 4: .. DELAYED_DURABILITY = ON The COMMIT syntax is extended so you can force delayed transaction durability. If DELAYED_DURABILITY is DISABLED or FORCED at the database level (see above) this COMMIT option is ignored. Syntax:

  COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] References: https://docs.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-2017