Microsoft Microsoft Certifications 70-762 Questions & Answers

• Question 71:

  You have multiple stored procedures inside a transaction.

  You need to ensure that all the data modified by the transaction is rolled back if a stored procedure causes a deadlock or times out.

  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.

  F. Set SET XACT_ABORT to ON.

  G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.

  H. Use the OUTPUT parameters.

  Correct Answer: F

  When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017

• Question 72:

  You run the following Transact-SQL statements:

  

  Records must only be added to the Orders table by using the view. If a customer name does not exist, then a new customer name must be created. You need to ensure that you can insert rows into the Orders table by using the view.

  A. Add the CustomerID column from the Orders table and the WITH CHECK OPTION statement to the view.

  B. Create an INSTEAD OF trigger on the view.

  C. Add the WITH SCHEMABINDING statement to the view statement and create a clustered index on the view.

  D. Remove the subquery from the view, add the WITH SCHEMABINDING statement, and add a trigger to the Orders table to perform the required logic.

  Correct Answer: A

  The WITH CHECK OPTION clause forces all data-modification statements executed against the view to adhere to the criteria set within the WHERE clause of the SELECT statement defining the view. Rows cannot be modified in a way that causes them to vanish from the view.

  References: http://www.informit.com/articles/article.aspx?p=130855andseqNum=4

• Question 73:

  You run the following Transact-SQL statement:

  

  There are multiple unique OrderID values. Most of the UnitPrice values for the same OrderID are different.

  You need to create a single index seek query that does not use the following operators: Nested loop Sort Key lookup

  Which Transact-SQL statement should you run?

  A. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Description, Quantity)

  B. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice) INCLUDE (Quantity)

  C. CREATE INDEX IX_OrderLines_1 ON OrderLines (OrderID, UnitPrice, Quantity)

  D. CREATE INDEX IX_OrderLines_1 ON OrderLines (UnitPrice, OrderID) INCLUDE (Description, Quantity)

  Correct Answer: A

  An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. Performance gains are achieved because the query optimizer can locate all

  the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

  Note: All data types except text, ntext, and image can be used as nonkey columns.

  Incorrect Answers:

  C: Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns.

  D: The most unique column should be the first in the index.

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

• Question 74:

  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.

  Correct Answer: D

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

  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

  Correct Answer: B

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

  You are designing a solution for a company that operates retail stores. Each store has a database that tracks sales transactions. You create a summary table in the database at the corporate office. You plan to use the table to record the

  quantity of each product sold at each store on each day. Managers will use this data to identify reorder levels for products.

  Every evening stores, must transmit sales data to the corporate office. The data must be inserted into the summary table that includes the StoreID, ProductID, Qtysold, Totprodsales, and Datesold columns.

  You need to prevent duplicate rows in the summary table. Each row must uniquely identify the store that sold the product and the total amount sold for that store on a specific date.

  What should you include in your solution?

  A. Create a unique constraint.

  B. Create a foreign key constraint to the StoreID column in each of the store tables.

  C. Create a rule and bind it to the StoreID column.

  D. Create a check constraint.

  E. Create a table-valued user-defined function.

  Correct Answer: A

  You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

  Incorrect Answers:

  D: CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/unique-constraints-and-check-constraints?view=sql-server-2017

• Question 77:

  You have the following stored procedure:

  

  The Numbers table becomes unavailable when you run the stored procedure. The stored procedure obtains an exclusive lock on the table and does not release the lock.

  What are two possible ways to resolve the issue? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. Remove the implicit transaction and the SET ANSI_DEFAULTS ON statement.

  B. Set the ANSI_DEFAULT statement to OFF and add a COMMIT TRANSACTION statement after the INSERT statement.

  C. Add a COMMIT TRANSACTION statement after the INSERT statement.

  D. Remove the SET ANSI_DEFAULTS ON statement.

  Correct Answer: CD

  SET ANSI_DEFAULTS is a server-side setting that the client does not modify. When enabled (ON), this option enables SET IMPLICIT_TRANSACTIONS (and some other options).

  The SET IMPLICIT_TRANSACTIONS, 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: ALTER TABLE, FETCH, REVOKE, BEGIN

  TRANSACTION, GRANT, SELECT, CREATE, INSERT, TRUNCATE TABLE, DELETE, OPEN, UPDATE, DROP.

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

• Question 78:

  You have a database that users query frequently.

  The users report that during peak business hours, the queries take longer than expected to execute.

  A junior database administrator uses Microsoft SQL Server Profiler on the database server to trace the session activities.

  While performing the trace, the performance of the database server worsens, and the server crashes.

  You need to recommend a solution to collect the query run times. The solution must minimize the impact on the resources of the database server.

  What should you recommend?

  A. Increase the free space on the system drive of the database server, and then use SQL Server Profiler on the server to trace the session activities.

  B. Collect session activity data by using SQL Server Extended Events.

  C. Clean up tempdb, and then use SQL Server Profiler on the database server to trace the session activities.

  D. Collect performance data by using a Data Collector Set (DCS) in Performance Monitor.

  Correct Answer: B

  SQL Server Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem. Extended Events is a light weight performance monitoring system that uses very few performance resources. Extended Events provides two graphical user interfaces (New Session Wizard and New Session) to create, modify, display, and analyze your session data.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/extended-events?view=sql-server-2017

• Question 79:

  You have a relational data warehouse that contains 1 TB of data.

  You have a stored procedure named usp_sp1 that generated an HTML fragment. The HTML fragment contains color and font style.

  You need to return the HTML fragment.

  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.

  F. Set SET XACT_ABORT to ON.

  G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.

  H. Use the OUTPUT parameters.

  Correct Answer: G

  A SQL Server stored procedure that you can call is one that returns one or more OUT parameters, which are parameters that the stored procedure uses to return data back to the calling application. References: https://docs.microsoft.com/en-us/sql/connect/jdbc/using-a-stored-procedure-with-output-parameters?view=sql-server-2017

• Question 80:

  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:

  

  You frequently run the following queries:

  

  There are no foreign key relationships between TBL1 and TBL2.

  You need to minimize the amount of time required for the two queries to return records from the tables.

  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