Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 151:

  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 names tblTransaction that is persisted on disk and contains the information described in the following table:

  

  Users report that the following query takes a long time to complete.

  

  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 aclustered 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 hashindex on the table.
  C. Create a nonclustered filtered index on the table.

  ---

  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.

• Question 152:

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

  ---

  Datetimeoffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

  Syntaxis: datetimeoffset [ (fractional seconds precision) ]

  Forthe use "datetimeoffset(0)", the Fractional seconds precision is 0, which is required here.

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

• Question 153:

  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 foreign key on the table.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  A check constraint is needed.

  References: https://en.wikipedia.org/wiki/Check_constraint

• Question 154:

  You have a data warehouse fact table that has a clustered columnstore index.

  You have multiple CSV files that contain a total of 3 million rows of data.

  You need to upload the data to the fact table. The solution must avoid the delta group when you import the data.

  Which solution will achieve the goal in the least amount of time?

  A. Load the source data to a staging table. Load the data to the fact table by using the INSERT_SELECT statement and specify the TABLOCK option on the staging table
  B. Create a Microsoft SQL Server Integration Services (SSIS) package. Use multiple data flow tasks to load the data in parallel.
  C. Load the source data to the fact table by running bcp.exe and specify the
  D. Load the source data to the fact table by using the BULK INSERT statement and specify the TABLOCK option
  A. Load the source data to a staging table. Load the data to the fact table by using the INSERT_SELECT statement and specify the TABLOCK option on the staging table

  ---

  If you are loading data only to stage it before running more transformations, loading the table to heap table will be much faster than loading the data to a clustered columnstore table. In addition, loading data to a [temporary table][Temporary]

  will also load much faster than loading a table to permanent storage.

  A common pattern for data load is to load the data into a staging table, do some transformation and then load it into the target table using the following command

  INSERT INTO

  SELECT FROM

  This command loads the data into the columnstore index in similar ways to BCP or Bulk Insert but in a single batch. If the number of rows in the staging table < 102400, the rows are loaded into a delta rowgroup otherwise the rows are directly

  loaded into compressed rowgroup. One key limitation was that this INSERT operation was single threaded. To load data in parallel, you could create multiple staging table or issue INSERT/SELECT with non-overlapping ranges of rows from

  the staging table. This limitation goes away with SQL Server 2016 (13.x). The command below loads the data from staging table in parallel but you will need to specify TABLOCK.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-data-loading-guidance?view=sql-server-2017#plan-bulk-load-sizes-to-minimize-delta-rowgroups

• Question 155:

  Note: The 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 question in the series. Information and details provided in a question apply only to that question.

  You have a database named DB1. The database does not use a memory-optimized filegroup. The database contains a table named Table1. The table must support the following workloads: You need to add the most efficient index to support the new OLTP workload, while not deteriorating the existing Reporting query performance. 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.
  C. Create a nonclustered filtered index on the table.

  ---

  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.

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

• Question 156:

  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. The Account table was created using the following Transact-SQL statement:

  

  There are more than 1 billion records in the Account table. The Account Number column uniquely identifies each account. The ProductCode column has 100 different values. The values are evenly distributed in the table. Table statistics are refreshed and up to date.

  You frequently run the following Transact-SQL SELECT statements:

  

  You must avoid table scans when you run the queries. You need to create one or more indexes for the table. Solution: You run the following Transact-SQL statement:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Create a clustered index on the AccountNumber column as it is unique, not a non nonclustered one. References:https://msdn.microsoft.com/en-us/library/ms190457.aspx

• Question 157:

  You have a database that contains both disk-based and memory-optimized tables.

  You need to create two modules. The modules must meet the requirements described in the following table.

  

  Which programming object should you use for each module? To answer, select the appropriate object types in the answer area.

  Hot Area:

  

  

  ---

  Returning Data by Using OUTPUT Parameters

  If you specify the OUTPUT keyword for aparameter in the procedure definition, the stored procedure can return the current value of the parameter to the calling program when the stored procedure exits.

  SQL Server stored procedures, views and functions are able to use the WITH ENCRYPTION optionto disguise the contents of a particularprocedure or function from discovery.

  Native Compilation of Tables and Stored Procedures

  In-Memory OLTP introduces the concept of native compilation. SQL Server can natively compile stored procedures that accessmemory-optimized tables. SQL Server is alsoable to nativelycompile memory-optimized tables. Native compilation allows faster data access and more efficient query execution than interpreted (traditional) Transact-SQL. Native compilation of tables and stored procedures produce DLLs.

  References:

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

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

• Question 158:

  DRAG DROP

  You have a memory-optimized table named Customer. The table is accessed by a stored procedure named ManageCustomer.

  The database was created in Microsoft SQL Server 2014. A backup and restore operation was used to move the database to SQL Server 2016.

  You have performance issues with the stored procedure.

  You need to resolve the performance issues and ensure the table statistics are updated automatically.

  Which three Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Step 1: ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL

  Sets certain database behaviors to be compatible with the specified version of SQL Server.

  Syntax:

  ALTER DATABASE database_name

  SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 }

  Step 2: UPDATE STATISTICS Customers

  UPDATE STATISTICS updates query optimization statistics on a table or indexed view.

  Step 3: EXEC sp_recompile N' 'Manage_customer'

  sp_recompile causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run

  References:

  https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level

  https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql

• Question 159:

  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 stored information about the employees of your company. You need to implement the following auditing rules for the Employees table:

  - Record any changes that are made to the data in the Employees table.

  -

  Customize the data recorded by the audit operations.

  Solution: You implement a check constraint on the Employees table.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

  Check constraints cannot be used to track changes in a table. References:https://msdn.microsoft.com/en-us/library/bb933994.aspx

• Question 160:

  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 cascading referential integrity constraints on the table.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

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