Microsoft 70-762 Exam Questions and Answers 2021

NEW 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 line 09 due to a CHECK constraint violation on the TotalDepositAccountCount column.
What is the impact of the stored procedure on the CustomerDetails table?

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

Answer: D

NEW QUESTION 2
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, Production. 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 column store index.
• B. Create a check constraint.
• C. Create a unique constraint.
• D. Create a rule and bind it to the ProductID column.

Answer: D

NEW QUESTION 3
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 implement a stored procedure that deletes a discontinued product from the Products table. You identify the following requirements:
* If an open order includes a discontinued product, the records for the product must not be deleted.
* The stored procedure must return a custom error message if a product record cannot be deleted. The message must identify the OrderID for the open order.
What should you do? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: Using TRY...CATCH in Transact-SQL
Errors in Transact-SQL code can be processed by using a TRY…CATCH construct. TRY…CATCH can use the following error function to capture error information:
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
References: https://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

NEW QUESTION 4
You have a database named MyDatabase. You must monitor all the execution plans in XML format by using Microsoft SQL Trace. The trace must meet the following requirements:
- Capture execution plans only for queries that run the MyDatabase database.
- Filter out plans with event duration of less than or equal to 100 microseconds.
- Save trace results to a disk on the server. You need to create the trace.
In which order should you arrange the Transact-SQL segments to develop the solution? To answer, move all Transact-SQL segments to the answer area and arrange them in the correct order.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.
DECLARE @traceEventId int = 122;
DECLARE @traceColumnIdForTextData int = 1; DECLARE @durationFilter bigint = 100 DECLARE @databaseID int;
SELECT @databaseId = DB_ID(‘MyDatabase’);

Answer:

Explanation: The following system stored procedures are used to define and manage traces:
* sp_trace_create is used to define a trace and specify an output file location as well asother options that I’ll cover in the coming pages. This stored procedure returns a handle to the created trace, in the form of an integer trace ID.
* sp_trace_setevent is used to add event/column combinations to traces based on the trace ID, as well as toremove them, if necessary, from traces in which they have already been defined.
* sp_trace_setfilter is used to define event filters based on trace columns.
* sp_trace_setstatus is called to turn on a trace, to stop a trace, and to delete a trace definitiononce you’re done with it. Traces can be started and stopped multiple times over their lifespan.
References: https://msdn.microsoft.com/en-us/library/cc293613.aspx

NEW QUESTION 5
You are planning a set of stored procedures that must be able to access memory-optimized tables. You need to optimize the performance of the stored procedures. Which statement should you include in the stored procedure definitions?

• A. WITH RECOMPILE
• B. WITH NATIV£_COMPILATION
• C. WITH EXECUTE AS SELF
• D. WITM NO INFOMSG5

Answer: D

NEW 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 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:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* 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:
* Create new rows in the table without granting INSERT permissions to the table.
* 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 requirements:
* 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 the Sales Order table
How should you complete the table definition? To answer? select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: Box 1: NONCLUSTERED HASHWITH (BUCKET_COUNT = 10000000)
Hash index is preferable over a nonclustered index when queries test the indexed columns by use of a WHERE clause with an exact equality on all index key columns. We should use a bucket count of 10 million.
Box 2: SCHEMA_ONLY
Durability: The value of SCHEMA_AND_DATA indicates that the table is durable, meaning that changes are persisted on disk and survive restart or failover. SCHEMA_AND_DATA is the default value.
The value of SCHEMA_ONLY indicates that the table is non-durable. The table schema is persisted but any data updates are not persisted upon a restart or failover of the database. DURABILITY=SCHEMA_ONLY is only allowed with MEMORY_OPTIMIZED=ON.
References: https://msdn.microsoft.com/en-us/library/mt670614.aspx

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

Answer:

Explanation:

NEW QUESTION 8
You have several real-time applications that constantly update data in a database. The applications run more than 400 transactions per second that insert and update new metrics from sensors.
A new web dashboard is released to present the data from the sensors. Engineers report that the applications take longer than expected to commit updates.
You need to change the dashboard queries to improve concurrency and to support reading uncommitted data. 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 XACTJVBORT to ON.
• G. Execute the alter table ti set (Lock_ESCALATION = auto); statement.
• H. Use the output parameters.

Answer: C

NEW QUESTION 9
You are creating the following two stored procedures:
A natively-compiled stored procedure
An interpreted stored procedure that accesses both disk-based and memory-optimized tables
Both stored procedures run within transactions.
You need to ensure that cross-container transactions are possible. Which setting or option should you use?

• A. the SET TRANSACTION_READ_COMMITTED isolation level for the connection
• B. the SERIALIZABLE table hint on disk-based tables
• C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database
• D. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=OFF option for the database

Answer: C

Explanation: Provide a supported isolation level for the memory-optimized table using a table hint, such as WITH (SNAPSHOT). The need for the WITH (SNAPSHOT) hint can be avoided through the use of the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. When this option is set to ON, access to a memory-optimized table under a lower isolation level is automatically elevated to SNAPSHOT isolation.

NEW QUESTION 10
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 independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are a database developer for a company. The company has a server that has multiple physical disks. The disks are not part of a RAID array. The server hosts three Microsoft SQL Server instances. There are many SQL jobs that run during off-peak hours.
You must monitor the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.
What should you do?

• A. Create asys.dm_os_waiting_tasks query.
• B. Create a sys.dm_exec_sessions query.
• C. Create aPerformance Monitor Data Collector Set.
• D. Create a sys.dm_os_memory_objects query.
• E. Create a sp_configure ‘max server memory’ query.
• F. Create a SQL Profiler trace.
• G. Create a sys.dm_os_wait_stats query.
• H. Create an Extended Event.

Answer: B

Explanation: sys.dm_exec_sessions returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.
Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.

NEW QUESTION 11
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 database named DB1 that includes a table named sales .orders. You grant a user named User1 select permissions on the Sales schema.
You need to ensure that User1 can select data from the sales .orders table without specifying the schema name in any Transact -SQL statements.
Solution: You set the default database for User1 to DB1. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 12
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:
* The table must reference the ProductID column in the Product table
* Existing records in the ProductReview table must not be validated with the Product table.
* Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.
* 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:
* Create new rows in the table without granting INSERT permissions to the table.
* 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 requirements:
* 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 a stored procedure named spDeleteCategory to delete records in the database. The stored procedure must meet the following requirements:
* Delete records in both the BlogEntry and BlogCategory tables where CategoryId equals parameter
@CategoryId.
* Avoid locking the entire table when deleting records from the BlogCategory table.
* If an error occurs during a delete operation on either table, all changes must be rolled back, otherwise all changes should be committed.
How should you complete the procedure? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: Box 1: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
You can minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications by using either of the following:
* The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set ON.
* The SNAPSHOT isolation level.
With ROWLOCK we should use READ COMMITTED Box 2: ROWLOCK
Requirement: Avoid locking the entire table when deleting records from the BlogCategory table
ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken. When specified in transactions operating at the SNAPSHOT isolation level, row locks are not taken unless ROWLOCK is combined with other table hints that require locks, such as UPDLOCK and HOLDLOCK.
Box 3: COMMIT
Box 4: ROLLBACK

NEW QUESTION 13
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 defragment both indexes. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

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

NEW QUESTION 14
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

Answer: B

Explanation: 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, andoptional user-specified precision.
References: https://msdn.microsoft.com/en-us/library/bb677335.aspx

NEW QUESTION 15
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 os independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have a database named DB1. There is no memory-optimized filegroup in the database. You run the following query:

The following image displays the execution plan the query optimizer generates for this query:

Users frequently run the same query with different values for the local variable @lastName. The table named Person is persisted on disk.
You need to create an index on the Person.Person table that meets the following requirements:
- All users must be able to benefit from the index.
- FirstName must be added to the index as an included column. What should you do?

• A. Create a clustered index on the table.
• B. Create a nonclustered index on the table.
• C. Create a nonclustered filtered index on the table.
• D. Create a clustered columnstore index on the table.
• E. Create a nonclustered columnstoreindex on the table.
• F. Create a hash index on the table.

Answer: B

Explanation: By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkeycolumns have the following benefits:
They can be data typesnot allowed as index key columns.
They are not considered by the Database Engine when calculating the number of index key columns or index key size.

NEW QUESTION 16
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

Answer: B

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

NEW QUESTION 17
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 database that is 130 GB and contains 500 million rows of data.
Granular transactions and mass batch data imports change the database frequently throughout the day. Microsoft SQL Server Reporting Services (SSRS) uses the database to generate various reports by using several filters.
You discover that some reports time out before they complete. You need to reduce the likelihood that the reports will time out.
Solution: You change the transaction log file size to expand dynamically in increments of 200 MB. Does this meet the goal?

• A. Yes
• B. No

Answer: B