70-762 | Microsoft 70-762 Free Practice Questions 2019

are updated and are verified by experts. Once you have completely prepared with our you will be ready for the real 70-762 exam without a problem. We have . PASSED First attempt! Here What I Did.

Check 70-762 free dumps before getting the full version:

NEW QUESTION 1
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 2
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 and optimize the SQL Server to maximize throughput, response time, and overall SQL performance.
You need to identify previous situations where a modification has prevented queries from selecting data in tables.
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 a sys.dm_os_wait_stats query.
  • H. Create an Extended Event.

Answer: G

Explanation: sys.dm_os_wait_stats returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

NEW QUESTION 3
You are developing a database reporting solution for a table that contains 900 million rows and is 103 GB. The table is updated thousands of times a day, but data is not deleted.
The SELECT statements vary in the number of columns used and the amount of rows retrieved.
You need to reduce the amount of time it takes to retrieve data from the table. The must prevent data duplication.
Which indexing strategy should you use?

  • A. a nonclustered index for each column in the table
  • B. a clustered columnstore index for the table
  • C. a hash index for the table
  • D. a clustered index for the table and nonclustered indexes for nonkey columns

Answer: B

Explanation: Columnstore indexes are the standard for storing and querying large data warehousing fact tables. It uses column-based data storage and query processing to achieve up to 10x query performance gains in your data warehouse over traditional row-oriented storage.
A clustered columnstore index is the physical storage for the entire table.
Generally, you should define the clustered index key with as few columns as possible.
A nonclustered index contains the index key values and row locators that point to the storage location of the table data. You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview?view=sql-serv

NEW QUESTION 4
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:
70-762 dumps exhibit
The following image displays the execution plan the query optimizer generates for this query:
70-762 dumps exhibit
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 5
You are developing queries and stored procedures to support a line-of-business application. You need to use the appropriate isolation level based on the scenario.
Which isolation levels should you implement? To answer, select the appropriate isolation level for each scenario in the answer area. Each isolation level may be used only once.
NOTE: Each correct selection is worth one point.
70-762 dumps exhibit

    Answer:

    Explanation: Box 1: READ UNCOMMITTED
    Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction.
    Box 2: READ COMMITTED
    READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions.
    SERIALIZABLE specifies that statements cannot read data that has been modified but not yet committed by other transactions.
    Box 3: REPEATABLE READ
    REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
    Box 4: SNAPSHOT
    If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement.
    References:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server

    NEW QUESTION 6
    Note: This question is part of a series of questions that use the same answer choices. An answer choice may be correct for more than one question on 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 work on an OLTP database that has no memory-optimized file group defined.
    You have a table named tblTransaction that is persisted on disk and contains the information described in the following table:
    70-762 dumps exhibit
    Users report that the following query takes a long time to complete.
    70-762 dumps exhibit
    You need to create an index that:
    -improves the query performance
    -does not impact the existing index
    -minimizes storage size of the table (inclusive of index pages). 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.

    Answer: C

    Explanation: A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed
    filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.

    NEW QUESTION 7
    You are reviewing the execution plans in the query plan cache. You observe the following:
    - There are a large number of single use plans.
    - There are a large number of simple execution plans that use multiple CPU cores. You need to configure the server to optimize query plan execution.
    Which two setting should you modify on the properties page for the Microsoft SQL Server instance? To answer, select the appropriate settings in the answer area.
    70-762 dumps exhibit

      Answer:

      Explanation: * Optimize for ad hoc workloads
      The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.
      * Cost Threshold for Parallelism
      Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.
      5 means 5 seconds, but is is 5 seconds on a machine internal to Microsoft from some time in the 1990s. There's no way to relate it to execution time on your current machine, so we treat it as a pure number now. Raising it to 50 is a common suggestion nowadays, so that more of your simpler queries run on a single thread.

      NEW QUESTION 8
      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 that contains a table named Employees. The table stores information about the employees of your company.
      You need to implement and enforce the following business rules:
      - Limit the values that are accepted by the Salary column.
      - Prevent salaries less than $15,000 and greater than $300,000 from being entered.
      - Determine valid values by using logical expressions.
      - Do not validate data integrity when running DELETE statements. Solution: You implement a FOR UPDATE trigger on the table. Does the solution meet the goal?

      • A. Yes
      • B. No

      Answer: B

      Explanation: References:
      http://stackoverflow.com/questions/16081582/difference-between-for-update-of-and-for-update

      NEW QUESTION 9
      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 create a file group for the indexes and a file group for the data files. You store the files for each file group on separate disks.
      Does this meet the goal?

      • A. Yes
      • B. No

      Answer: A

      Explanation: Consider creating two additional File Groups: Tables and Indexes. It is best not to put your stuff in PRIMARY as that is where SQL SERVER stores all of its data and meta-data about your objects. You create your Table and Clustered Index (as that is the data for the table) on [Tables] and all Non-Clustered indexes on [Indexes].

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

      Answer: B

      Explanation: 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 programming model 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

      NEW QUESTION 11
      You need to build a function that meets the following requirements:
      *Returns multiple rows
      *Optimizes the performance of the query within the function
      How should you complete the Transact-SQL statement? 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.
      70-762 dumps exhibit

        Answer:

        Explanation: 70-762 dumps exhibit

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

        Answer: B

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

        NEW QUESTION 13
        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_exec_query_stats dynamic management view query
        • C. a sys.dm_exec_session_wait_stats dynamicmanagement view query
        • D. an Activity Monitor session in Microsoft SQL Management Studio.

        Answer: C

        Explanation: sys.dm_exec_session_wait_stats returns information about all the waits encountered by threads that executed for each session. You can use this view to diagnose performance issues with the SQL Server session and also with specific queries and batches.
        Note: SQL Server wait stats are, at their highest conceptual level, grouped into two broad categories: signal waits and resource waits. A signal wait is accumulated by processes running on SQL Server which are waiting for a CPU to become available (so called because the process has “signaled” that it is ready for processing). A resource wait is accumulated by processes running on SQL Server which are waiting fora specific resource to become available, such as waiting for the release of a lock on a specific record.

        NEW QUESTION 14
        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 tables 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 tabl
        • B. Create a natively compiled stored procedure to cleanse and consolidate the data.
        • C. Convert the reference table to a memory-optimized tabl
        • D. Set the durability option toSCHEMA_AND_DATA>
        • E. Create a native compiled stored procedure to implement the ETL process for both tables.
        • F. Convert the reference table to a memory-optimized tabl
        • G. Set the durability option to SCHEMA_ONLY.

        Answer: D

        NEW QUESTION 15
        Background
        You have a database named HR1 that includes a table named Employee.
        You have several read-only, historical reports that contain regularly changing totals. The reports use multiple queries to estimate payroll expenses. The queries run concurrently. Users report that the payroll estimate reports do not always run. You must monitor the database to identify issues that prevent the reports from running.
        You plan to deploy the application to a database server that supports other applications. You must minimize the amount of storage that the database requires.
        Employee Table
        You use the following Transact-SQL statements to create, configure, and populate the Employee table:
        70-762 dumps exhibit
        Application
        You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:
        - UspA: This stored procedure updates only the EmployeeStatus column.
        - UspB: This stored procedure updates only the EmployeePayRate column.
        The application uses views to control access to data. Views must meet the following requirements:
        - Allow user access to all columns in the tables that the view accesses.
        - Restrict updates to only the rows that the view returns. Exhibit
        70-762 dumps exhibit
        You view the Deadlock Graph as shown in the exhibit. (Click the Exhibit button.)
        Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.
        NOTE: Each correct selection is worth one point.
        70-762 dumps exhibit

          Answer:

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

          NEW QUESTION 16
          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:
          70-762 dumps exhibit
          You need to resolve the performance issue.
          Solution: You update statistics for the nonclustered index. Does the solution meet the goal?

          • A. Yes
          • B. No

          Answer: A

          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.

          Thanks for reading the newest 70-762 exam dumps! We recommend you to try the PREMIUM Certleader 70-762 dumps in VCE and PDF here: https://www.certleader.com/70-762-dumps.html (151 Q&As Dumps)