Microsoft 70-762 Online Practice Questions and Exam Preparation

Microsoft 70-762 Online Questions & Answers

• Question 61:

  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.

  You are developing a new application that uses a stored procedure. The stored procedure inserts thousands of records as a single batch into the Employees table.

  Users report that the application response time has worsened since the stored procedure was updated. You examine disk-related performance counters for the Microsoft SQL Server instance and observe several high values that include a

  disk performance issue. You examine wait statistics and observe an unusually high WRITELOG value.

  You need to improve the application response time.

  Solution: You replace the stored procedure with a user-defined function.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

  ---

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

• Question 62:

  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. The users report that the query takes a long time to return results.

  

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

  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.
  D. Create UNIQUE 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.

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

• Question 63:

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

  ---

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

• Question 64:

  You have a table that stores transactions partitioned by year. Users occasionally experience performance issues when they access the table. The table is locked exclusively when the records are updated. You need to prevent exclusive locks

  on the table and maintain data integrity.

  What should you do?

  A. Set the LOCK_EXCALATION property to DISABLE
  B. Set the DATA_COMPRESSION property to ROW at the partition level
  C. Set the LOCK_EXCALATION property to AUTO
  D. Set the LOCK_EXCALATION property to TABLE
  E. Set the DATA_COMPRESSION property to PAGE at the partition level
  C. Set the LOCK_EXCALATION property to AUTO

  ---

  The default lock escalation mode is called TABLE, it implements SQL Server's lock escalation on all types of tables whether partitioned or not partitioned.

  There are two more lock escalation modes: AUTO and DISABLE.

  The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.

  The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.

  References:

  https://www.mssqltips.com/sqlservertip/4359/altering-lock-escalation-for-sql-server-tables/

• Question 65:

  DRAG DROP

  You maintain a Microsoft Azure SQL Database instance.

  You grant User1 the SELECT and EXECUTE permissions for all objects in the dbo schema.

  You must create a stored procedure that allows User1 to view the following information:

  details for each connection to the database

  a list of all active user connections and internal tasks You need to create the stored procedure for User1 and ensure that User1 can run the stored procedure without any error. How should you complete the Transact-SQL statements? 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.

  NOTE: More than one combination of answer choices is correct. You will receive credit for any of the correct combinations you select.

  Select and Place:

  

  

  ---

  Box 1: Sys.dm_exec_connections

  Sys.dm_exec_connections returns information about the connections established to this instance of SQL Server and the details of each connection. Returns server wide connection information for SQL Server. Returns current database

  connection information for SQL Database.

  Box 2: sys.dm_exec_sessions

  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.

  Box 3: GRANT VIEW DATABASE STATE To User1

  SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database. VIEW DATABASE STATE cannot be granted in the master database.

  Incorrect Answers:

  Sys.dm_exec_requests returns information about each request that is executing within SQL Server.

  References:

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

• Question 66:

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

  CREATE NONCLUSTERED INDEX IX_Account_ProductCode ON Account(ProductCode);

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

  ---

  References: https://msdn.microsoft.com/en-za/library/ms189280.aspx

• Question 67:

  You are maintaining statistics for a database table named tblTransaction. The table contains more than 10 million records. You need to create a stored procedure that meets the following requirements:

  - On weekdays, update statistics for a sample of the total number of records in the table.

  - On weekends, update statistics by sampling all rows in the table.

  A maintenance task will call this stored procedure daily.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: UPDATE STATISTICS Box 2: SAMPLE 20 PERCENT UPDATE STATISTICS tablenameSAMPLE number { PERCENT | ROWS } Specifies the approximate percentage or number of rows in the table or indexed view for the query optimizer to use when it updates statistics. For PERCENT, number can be from 0 through 100 and for ROWS, number can be from0 to the total number of rows. Box 3: UPDATE STATISTICS Box 4: WITH FULLSCAN FULLSCAN computes statistics by scanning all rows in the table or indexed view. FULLSCAN and SAMPLE 100 PERCENT have the same results. FULLSCAN cannot be used with the SAMPLE option. References:https://msdn.microsoft.com/en-us/library/ms187348.aspx

• Question 68:

  HOTSPOT

  You have a table named Person.Address that includes the following columns:

  AddressID

  AddressLine1

  AddressLine2

  City

  StateProvinceID

  PostakCode

  RowGuid

  ModifiedDate

  You need to create a nonclustered index on PostalCode named IX_Address_PostalCode that uses the following included columns:

  AddressLine1

  AddressLine2

  City

  StateProvinceID

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

  Hot Area:

  

  

  ---

  Explanation: Box 1: INDEX Box 2: ON Box 3: INCLUDE

  INCLUDE (column [ ,... n ] ) specifies the non-key columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or non-unique.

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

• Question 69:

  You use Microsoft SQL Server Profile to evaluate a query named Query1. The Profiler report indicates the following issues:

  At each level of the query plan, a low total number of rows are processed.

  The query uses many operations. This results in a high overall cost for the query.

  You need to identify the information that will be useful for the optimizer.

  What should you do?

  A. Start a SQL Server Profiler trace for the event class Auto Stats in the Performance event category.
  B. Create one Extended Events session with the sqlserver.missing_column_statistics event added.
  C. Start a SQL Server Profiler trace for the event class Soft Warnings in the Errors and Warnings event category.
  D. Create one Extended Events session with the sqlserver.missing_join_predicate event added.
  D. Create one Extended Events session with the sqlserver.missing_join_predicate event added.

  ---

  The Missing Join Predicate event class indicates that a query is being executed that has no join predicate. This could result in a long-running query.

• Question 70:

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

  ---

  Datetimeoffset, not datetimeofset, defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hourclock.

  Syntaxis: datetimeoffset [ (fractional seconds precision) ]

  For the use "datetimeoffset", the Fractional seconds precision is 7.

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