Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 121:

  You need to create a view that can be indexed. You write the following statement.

  

  What should you add at line 02?

  A. with check_option
  B. with recompile
  C. with view_metadata
  D. with schemabinding
  D. with schemabinding

  ---

  The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

  1.

  Verify the SET options are correct for all existing tables that will be referenced in the view.

  2.

  Verify that the SET options for the session are set correctly before you create any tables and the view.

  3.

  Verify that the view definition is deterministic.

  4.

  Create the view by using the WITH SCHEMABINDING option.

  5.

  Create the unique clustered index on the view.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-2017

• Question 122:

  You are developing an app that allows users to query historical company financial data.

  You are reviewing email messages from the various stakeholders for a project.

  The message from the security officer is shown in the Security Officer Email exhibit below.

  TO: Database developer

  From: Security Officer

  Subject: SQL object requirements

  We need to simplify the security settings for the SQL objects. Having a assign permissions at every object in SQL is tedious and leads to a problem. Documentation is also more difficult when we have to assign permissions at multiple levels.

  We need to assign the required permissions at one object, even though that object may be obtaining from other objects.

  The message from the sales manager is shown in the Sales Manager Email exhibit below.

  TO: Database developer

  From: Sales Manager

  Subject: Needed SQL objects

  When creating objects for our use, they need to be flexible. We will be changing the base infrastructure frequently. We need components in SQL that will provide backward compatibility to our front end applications as the environments change so that do not need to modify the front end applications. We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.

  This is an example of the types of data we need to be able to have queries against without having to change the front end applications.

  

  The message from the web developer is shown in the Web Developer Email exhibit below. TO: Database developer From: Web Developer Subject: SQL Object component Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced in this interface. We have been using this for a long time, and we cannot change this from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.

  

  You need to create one or more objects that meet the needs of the security officer, the sales manager and the web developer. For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  Hot Area:

  

  

  ---

  * Stored procedure: Yes

  A stored procecure to implement the following:

  Whatever you will be configuring to provide access to data in SQL, it needs to connect using the items referenced inthis interface. We have been using this for a long time, and we cannot changethis from end easily. Whatever objects are going to be used in SQL they must work using object types this interface references.

  * Trigger: No

  No requirements are related to actions taken when changing the data.

  * View: Yes

  Because: We need objects that can provide a filtered set of the data. The data may be coming from multiple tables and we need an object that can provide access to all of the data through a single object reference.

• Question 123:

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

• Question 124:

  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
  C. the SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON option for the database

  ---

  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.

  Incorrect Answers:

  B: Accessing memory optimized tables using the READ COMMITTED isolation level is supported only for autocommit transactions. It is not supported for explicit or implicit transactions. References: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/transactions-with-memory-optimized-tables?view=sql-server-2017

• Question 125:

  You have multiple queries that take a long time to complete.

  You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis.

  Which Transact-SQL statement should you run?

  A. SET STATISTICS IO ON
  B. SET SHOWPLAN_TEXT ON
  C. SET STATISTICS XML ON
  D. SET STATISTICS PROFILE ON
  B. SET SHOWPLAN_TEXT ON

  ---

  SET SHOWPLAN_TEXT ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed. Incorrect Answers:

  C: SET STATISTICS XML ON causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.

  D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution.

  The additional result set contains the SHOWPLAN_ALL columns for the query and these additional columns.

  Column name, Description Rows: Actual number of rows produced by each operator Executes: Number of times the operator has been executed

  References: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-text-transact-sql

• Question 126:

  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 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 are developing an application to track customer sales. You create tables to support the application. You need to create a database object that meets the following data entry requirements: 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
  C. user-defined procedure

  ---

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

• Question 127:

  HOTSPOT

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

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

• Question 128:

  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 create a query that includes data from both tables and minimizes the amount of time required 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.
  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 129:

  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:

  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.

  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:

  1. Create new rows in the table without granting INSERT permissions to the table.

  2. 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 update the SalesHistory table

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

  Hot Area:

  

  

  ---

  Box 1:

  SaleID must be the primary key, as a constraint on the SaleID column that allows the field to be used as a record identifier is required.

  Box2:

  A constraint that limits the SalePrice column to values greater than four.

  Box 3: UNIQUE

  A constraint on the CategoryID column that allows one row with a null value in the column.

  Box 4:

  A foreign keyconstraint must be put on the productID referencingthe ProductTypes table, as a constraint that uses the ProductID column to reference the Product column of the ProductTypes table is required.

  Note: Requirements are:

  You must add the following constraints to the SalesHistory table:

  - a constraint on theSaleID column that allows the field to be used as a record identifier

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

• Question 130:

  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
  B. a clustered columnstore index for the table

  ---

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