Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 91:

  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 increase the number of log files for the database. You store the log files across multiple disks.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Instead, create a file group for the indexes and a file group for the data files.

• Question 92:

  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;

  Select and Place:

  

  

  ---

  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

• Question 93:

  HOTSPOT

  This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.

  To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.

  At the end of this case study, a review scree will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.

  To start the case study

  To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements. Existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.

  Background

  You have a database named Sales.

  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.

  Tables

  

  You need to modify the database design to meet the following requirements:

  Rows in the Orders table must always have a valid value for the ProductID column.

  Rows in the Products table must not be deleted if they are part of any rows in the Orders table.

  All rows in both tables must be unique.

  In the table below, identify the constraint that must be configured for each table.

  NOTE: Make only one selection in each column.

  Hot Area:

  

  

• Question 94:

  You have a database that is experiencing deadlock issues when users run queries.

  You need to ensure that all deadlocks are recorded in XML format.

  What should you do?

  A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
  B. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).
  C. Enable trace flag 1222 in the startup options for Microsoft SQL Server.
  D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.
  C. Enable trace flag 1222 in the startup options for Microsoft SQL Server.

  ---

  When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

  The output format for Trace Flag 1222 only returns information in an XML-like format.

  References: https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

• Question 95:

  HOTSPOT

  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 enable referential integrity for the ProductReview table.

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

  Hot Area:

  

  

  ---

  Box 1: WITH NOCHECK

  We should use WITH NOCHECK as existing records in the ProductReview table must not be validated with the Product table.

  Box 2: ON DELETE NO ACTION ON DELETE NO CASCADE

  Deletes should not be allowed, so we use ON DELETE NO ACTION.

  Updates should be allowed, so we use ON DELETE NO CASCADE

  NO ACTION: the Database Engine raises an error, and the update action on the row in the parent table is rolled back.

  CASCADE: corresponding rows are updated in the referencing table when that row is updated in the parent table.

  Note: ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

  Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.

  ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

  Specifies what action happens to rows in the table altered when those rows have a referential relationship and the referenced row is updated in the parent table. The default is NO ACTION.

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

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

• Question 96:

  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 an object that meets to following requirements:

  Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.

  Run written a transaction and roll back if a failure occurs.

  Run when a table is created or modified.

  What should you create?

  A. extended procedure
  B. CLR procedure
  C. user-defined procedure
  D. DML trigger
  E. DDL trigger
  F. scalar-valued function
  G. table-valued function
  B. CLR procedure

  ---

  The common language runtime (CLR) is the heart of the Microsoft .NET Framework and provides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/introduction-to-sql-server-clr-integration

• Question 97:

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

  You need to create an object that meet the following requirements:

  - Run managed code packaged in an assembly that was created in the Microsoft.NET Framework and uploaded in Microsoft SQL Server.

  -

  Run written a transaction and roll back if a future occurs.

  -

  Run when a table is created or modified.

  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

  ---

  The common language runtime (CLR) is the heart of the Microsoft .NET Framework andprovides the execution environment for all .NET Framework code. Code that runs within the CLR is referred to as managed code. With the CLR hosted in Microsoft SQL Server (called CLR integration), you can author stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

• Question 98:

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

  ---

  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.

• Question 99:

  DRAG DROP

  You manage a database with tables named Invoice and InvoiceDetails. Each invoice may have multiple records.

  Users update the InvoiceDetails table by using a .NET web application. The application retrieves records from both tables and updates the tables by running an inline update statement.

  Users experience slow performance when updating records in the application. The solution must meet the following requirements:

  Must use a stored procedure.

  Must not use inline update statements

  Must use a table-valued parameter.

  Must call the stored procedure to update all records.

  You need to optimize performance.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Box 1: Create a user-defined table type

  Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a

  temporary table or many parameters.

  Box 2: ..read-only input parameter.

  Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines.

  Box 3:

  Example

  The following example uses Transact-SQL and shows you how to create a table-valued parameter type, declare a variable to reference it, fill the parameter list, and then pass the values to a stored procedure.

  USE AdventureWorks2012;

  /* Create a table type. */

  CREATE TYPE LocationTableType AS TABLE

  ( LocationName VARCHAR(50)

  , CostRate INT );

  GO

  /* Create a procedure to receive data for the table-valued parameter. */ CREATE PROCEDURE dbo. usp_InsertProductionLocation @TVP LocationTableType READONLY Etc.

  /* Declare a variable that references the type. */ DECLARE @LocationTVP AS LocationTableType;

  /* Add data to the table variable. */

  INSERT INTO @LocationTVP (LocationName, CostRate) SELECT Name, 0.00 FROM AdventureWorks2012.Person.StateProvince;

  /* Pass the table variable data to a stored procedure. */ EXEC usp_InsertProductionLocation @LocationTVP; GO

  References: https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

• Question 100:

  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 database named DB1. There is no memory-optimized filegroup in the database.

  You have a table and a stored procedure that were created by running the following Transact-SQL statements:

  

  The Employee table is persisted on disk. You add 2,000 records to the Employee table.

  You need to create an index that meets the following requirements:

  Optimizes the performance of the stored procedure.

  Covers all the columns required from the Employee table.

  Uses FirstName and LastName as included columns.

  Minimizes index storage size and index key size.

  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 columnstore index on the table.
  F. Create a hash index on the table.
  B. Create a nonclustered index on the table.

  ---

  References: https://technet.microsoft.com/en-us/library/jj835095(v=sql.110).aspx