Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 171:

  HOTSPOT

  You have a Microsoft Azure SQL database.

  Some queries take a long time to execute.

  You need to identify whether any of the long-running queries prevent users from updating data in the database.

  Which query should you execute? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Explanation:

  Box 1: sys.dm_tran_locks sys.dm_tran_locks returns information about currently active lock manager resources in SQL Server 2017. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. Box 2: sys.dm_os_waiting_tasks

  sys.dm_tran_locks.lock_owner_address is the memory address of the internal data structure that is used to track this request. This column can be joined the with resource_address column in sys.dm_os_waiting_tasks.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017

• Question 172:

  You have a database with multiple tables. You must insert data into the tables by using views.

  Which of the following two statements best describes an updatable view?

  NOTE: Each correct selection is worth one point.

  A. The view must not include the primary key of the table.
  B. The view may include GROUP BY or HAVING statements.
  C. The view may include a composite field.
  D. Each view must reference columns from one table.
  E. The view must not include subqueries.
  B. The view may include GROUP BY or HAVING statements.
  D. Each view must reference columns from one table.

  ---

  B: The columns being modified must not be affected by GROUP BY, HAVING, or DISTINCT clauses.

  D: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. Incorrect Answers:

  A: Primary keys are allowed.

  C: The columns cannot be derived in any other way, such as through the following:

  A computation. The column cannot be computed from an expression that uses other columns.

  E: The restrictions apply to any subqueries in the FROM clause of the view, just as they apply to the view itself.

  References:

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

• Question 173:

  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 modify the environment to meet the requirements for the Orders table.

  What should you create?

  A. an AFTER UPDATE trigger
  B. a user-defined function
  C. a stored procedure with output parameters
  D. an INSTEAD OF INSERT trigger
  D. an INSTEAD OF INSERT trigger

  ---

  From Question: 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.

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

• Question 174:

  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 partition the largest tables.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 175:

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

  

  There are no foreign key relationships between TBL1 and TBL2.

  You need to minimize the amount of time required for queries that use data from TB1 and TBL2 to return data.

  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.
  G. Create CHECK constraints on both TBL1 and TBL2. Create a partitioned view that combines columns from TBL1 and TBL2.

  ---

  A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server

  servers, called federated database servers.

  Conditions for Creating Partitioned Views Include:

  The select list

  All columns in the member tables should be selected in the column list of the view definition.

  The columns in the same ordinal position of each select list should be of the same type, including collations. It is not sufficient for the columns to be implicitly convertible types, as is generally the case for UNION.

  Also, at least one column (for example ) must appear in all the select lists in the same ordinal position. This should be defined in a way that the member tables T1, ..., Tn have CHECK constraints C1, ..., Cn defined on ,

  respectively.

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql

• Question 176:

  HOTSPOT You are developing a stored procedure with the following requirements: Accepts an integer as input and inserts the value into a table. Ensures new transactions are committed as part of the outer transactions. Preserves existing transactions if the transaction in the procedure fails.

  If the transaction in the procedure fails, rollback the transaction. How should you complete the procedure? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Explanation:

  Box 1: @TranCount> 0 Procedure called when there is an active transaction. Create a savepoint to be able to roll back only the work done in the procedure if there is an error. Box 2: @TranCount = 0

  -- @TranCount = 0 means no transaction was started before the procedure was called. The procedure must commit the transaction it started.

  Box 3: XACT_STATE() <> -1 If the transaction is still valid, just roll back to the savepoint set at the start of the stored procedure.

  References: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/save-transaction-transact-sql?view=sql-server-2017

• Question 177:

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

  You need to create a baseline set of metrics to report how the computer running SQL Server operates under normal load. The baseline must include the resource usage associated with the server processes.

  What should you do?

  A. Create a sys.dm_os_waiting_tasks query.
  B. Create a sys.dm_exec_sessions query.
  C. Create a Performance 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 asys.dm_os_wait_stats query.
  H. Create an Extended Event.
  D. Create a sys.dm_os_memory_objects query.

  ---

  sys.dm_os_memory_objects returns memory objects that are currently allocated by SQL Server. You can usesys.dm_os_memory_objects to analyze memory use and to identify possible memory leaks.

  Example: The following example returns the amount of memory allocated by each memory object type. SELECT SUM (pages_in_bytes) as 'Bytes Used', type FROMsys.dm_os_memory_objects GROUP BY type ORDER BY 'Bytes Used' DESC; GO

• Question 178:

  You are experiencing performance issues with the database server.

  You need to evaluate schema locking issues, plan cache memory pressure points, and backup I/O problems.

  What should you create?

  A. a System Monitor report
  B. a sys.dm_tran_database_transaction dynamic management view query
  C. an Extended Events session that uses Query Editor
  D. a Microsoft SQL Profiler trace
  C. an Extended Events session that uses Query Editor

  ---

  Extended Events: considered as "the best way" by the SQL Server purists. You can configure Extended Events to find Locking Issues in SQL Server. Incorrect Answers:

  D: SQL Trace and SQL Server Profiler are deprecated. The Microsoft.SqlServer.Management.Trace namespace that contains the Microsoft SQL Server Trace and Replay objects are also deprecated.

  This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

  Use Extended Events instead.

  References:

  https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/

• Question 179:

  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:

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

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

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

  Sales.Female.Customers must meet the following requirements:

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

  2. Prevent changes to the columns that the view references.

  3. 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 spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

  You need to create Sales.FemaleCustomers.

  How should you complete the view definition? 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.

  Select and Place:

  

  

  ---

  Box 1:WITH SCHEMABINDING:

  SCHEMABINDING binds the viewto 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.

  Box 2:Box 2: WITH CHECK OPTION

  CHECK OPTION forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible

  through the view after the modification is committed.

  Note:Sales.Female.Customers must meet the following requirements:

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

  2. Prevent changes to the columns that theview references.

  3. Only allow updates through the views that adhere to the view filter.

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

• Question 180:

  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:

  

  Users frequently run the following query:

  

  Users report that the query takes a long time to return results.

  You need to minimize the amount of time requires for the query to return data.

  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.
  H. Create an indexed view that combines columns from TBL1 and TBL2.