The Secret of 70-762 testing engine

70-762 Exam

The Secret of 70-762 testing engine

Exambible 70-762 Questions are updated and all 70-762 answers are verified by experts. Once you have completely prepared with our 70-762 exam prep kits you will be ready for the real 70-762 exam without a problem. We have Rebirth Microsoft 70-762 dumps study guide. PASSED 70-762 First attempt! Here What I Did.

Q11. HOTSPOT

You are analyzing the performance of a database environment.

You need to find all unused indexes in the current database.

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

Answer:

Explanation:

Example: Following query helps you to find all unused indexes within database using sys.dm_db_index_usage_stats DMV.

-- Ensure a USE statement has been executed first. SELECT u.*

FROM [sys].[indexes] i

INNER JOIN[sys].[objects] o ON (i.OBJECT_ID = o.OBJECT_ID)

LEFT JOIN [sys].[dm_db_index_usage_stats] u ON (i.OBJECT_ID = u.OBJECT_ID) AND i.[index_id] = u.[index_id]

AND u.[database_id] = DB_ID() --returning the database ID of the current database WHERE o.[type] <>'S' --shouldn't be a system base table

AND i.[type_desc] <> 'HEAP' AND i.[name] NOT LIKE 'PK_%'

AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0 AND u.[last_system_scan] IS NOT NULL

ORDER BY 1 ASC

References:https://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/

Q12. 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 named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data 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.

You need to create triggers that meet the following requirements:

In the table below, identify the trigger types that meet the requirements.

NOTE: Make only selection in each column. Each correct selection is worth one point.

Answer:

Explanation:

INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement.

AFTER specifies that the DML trigger is fired only when all operationsspecified in the triggering SQL statement have executed successfully.

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

Q13. DRAG DROP

You have a database named DB1 that contains the following tables: Customer, CustomerToAccountBridge, and CustomerDetails. The three tables are part of the Sales schema. The database also contains a schema named Website. You create the Customer table by running the following Transact-SQL statement:

The value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts. The

following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.

Website.Customer must meet the following requirements:

1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

2. Allow changes to the columns that the view references. Modified data must be visible through the view.

3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

2. Prevent changes to the columns that the view references.

3. Only allow updates through the views that adhere to the view filter.

You have the following stored procedures: spDeleteCustAcctRelationship and spUpdateCustomerSummary. The spUpdateCustomerSummary stored procedure was created by running the following Transacr-SQL statement:

The following table displays a selected columns and rows from the Customer table. Thr value of the CustomerStatus column is equal to one for active customers. The value of the Account1Status and Account2Status columns are equal to one for active accounts.

You run the spUpdateCustomerSummary stored procedure to make changes to customer account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

Users report that the following SELECT statement statement takes a long time to complete:

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

1. Improve the performance of the SELECT statement.

2. requires minimum index key size.

3. Only contains active customers.

4. Makes no change to the existing primary key.

5. Contains all of the columns required by the SELECT statement.

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

Answer:

Explanation:

Box 1: Clustered Index

With the same size of keys, the nonclustered indexes need more space than clustered

indexes.

Box 2, Box 3:

Include the CustomerStatus column in the index, and only when CustomerStatusnot equal to 1 (the active customers).

References:http://www.sqlserverlogexplorer.com/overview-of-cluster-and-noncluster-index/

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

Answer: B

Explanation:

datetime2 defines adate that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, andoptional user-specified precision.

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

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

Application

You have an application that updates the Employees table. The application calls the following stored procedures simultaneously and asynchronously:

The application uses views to control access to data. Views must meet the following requirements:

Exhibit

You are analyzing the performance of the database environment. You discover that locks that are held for a long period of time as the reports are generated.

You need to generate the reports more quickly. The database must not use additional resources.

What should you do?

A. Update the transaction level of the report query session to READPAST.

B. Modify the report queries to use the UNION statement to combine the results of two or more queries.

C. Set the READ_COMMITTED_SNAPSHOT database option to ON.

D. Update the transaction level of the report query session to READ UNCOMMITTED.

Answer: C

Explanation:

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

Q16. HOTSPOT

You have a database named Sales that contains the following database tables: Customer, Order, and Products. The Products table and the Order table are shown in the following diagram.

The customer table includes a column that stores the data 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.

The Leads table must include the columns described in the following table.

The data types chosen must consume the least amount of storage possible. You need to select the appropriate data types for the Leads table.

In the table below, identify the data type that must be used for each table column. NOTE: Make only one selection in each column.

Answer:

Explanation:

Bit is aTransact-SQL integer data type that can take a value of 1, 0, or NULL.

Smallint is aTransact-SQL integer data type that can take a value in the range from -32,768 to 32,767.

int, bigint, smallint, and tinyint (Transact-SQL) Exact-number data types that use integer data.

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

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

Answer: C

Explanation:

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

Q18. DRAG DROP

You have a database that contains three encrypted store procedures named dbo.Proc1, dbo.Proc2 and dbo.Proc3. The stored procedures include INSERT, UPDATE, DELETE and BACKUP DATABASE statements.

You have the following requirements:

- You must run all the stored procedures within the same transaction.

- You must automatically start a transaction when stored procedures include DML statements.

- You must not automatically start a transaction when stored procedures include DDL statements.

You need to run all three stored procedures.

Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments to the answer area and arrange then in the correct order.

Answer:

Explanation:

Note:

Implicit transaction mode remains in effect until the connection executes a SET IMPLICIT_TRANSACTIONS OFF statement,which returns the connection to autocommit mode. In autocommit mode, allindividual statements are committed if they complete successfully.

When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:

ALTER TABLE (DDL) FETCH

REVOKE

BEGIN TRANSACTION GRANT

SELECT CREATE (DDL) INSERT

TRUNCATE TABLE DELETE (DML) OPEN

UPDATE (DML) DROP (DDL)

Note 2: XACT_STATE returns the following values.

1 The currentrequest has an active user transaction. The request can perform any actions, including writing data and committing the transaction. The transaction is committable.

-1 The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction.the transaction is uncommittable and should be rolled back.

0 There is no active user transaction for the current request. Acommit or rollback operation would generate an error.

References:

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

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

The value of the CustomerStatus column is equal to one for active customers. The value of

the Account1Status and Account2Status columns are equal to one for active accounts. The following table displays selected columns and rows from the Customer table.

You plan to create a view named Website.Customer and a view named Sales.FemaleCustomers.

Website.Customer must meet the following requirements:

1. Allow users access to the CustomerName and CustomerNumber columns for active customers.

2. Allow changes to the columns that the view references. Modified data must be visible through the view.

3. Prevent the view from being published as part of Microsoft SQL Server replication. Sales.Female.Customers must meet the following requirements:

1. Allow users access to the CustomerName, Address, City, State and PostalCode columns.

2. Prevent changes to the columns that the view references.

3. Only allow updates through the views that adhere to the view filter.

You run the spUpdateCustomerSummary stored procedure to make changes to customer

account summaries. Other stored procedures call the spDeleteCustAcctRelationship to delete records from the CustomerToAccountBridge table.

You must update the design of the Customer table to meet the following requirements.

1. You must be able to store up to 50 accounts for each customer.

2. Users must be able to retrieve customer information by supplying an account number.

3. Users must be able to retrieve an account number by supplying customer information. You need to implement the design changes while minimizing data redundancy.

What should you do?

A. Splitthe table into three separate tables. Include the AccountNumber and CustomerID columns in the first table. Include the CustomerName and Gender columns in the second table. Include the AccountStatus column in the third table.

B. Split the table into two separate tables. Include AccountNumber, CustomerID, CustomerName and Gender columns in the first table. Include the AccountNumber and AccountStatus columns in the second table.

C. Split the table into two separate tables, Include the CustomerID and AccountNumber columns in the first table. Include the AccountNumber, AccountStatus, CustomerName and Gender columns in the second table.

D. Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. IncludeAccountNumber, AccountStatus and CustomerID columns in the second table.

Answer: D

Explanation:

Two tables is enough.CustomerID must be in both tables.

Q20. DRAG DROP

You have two database tables. Table1 is a partioned table and Table 2 is a nonpartioned table.

Users report that queries take a long time to complete. You monitor queries by using Microsoft SQL Server Profiler. You observe lock escalation for Table1 and Table 2.

You need to allow escalation of Table1 locks to the partition level and prevent all lock escalation for Table2.

Which Transact-SQL statement should you run for each table? To answer, drag the appropriate Transact-SQL statements to the correct tables. Each command 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.

Answer:

Explanation:

Since SQL Server 2008 you can also control how SQL Server performs the Lock Escalation – through the ALTER TABLE statement and the property LOCK_ESCALATION. There are 3 different optionsavailable:

TABLE AUTO DISABLE

Box 1: Table1, Auto

The default option is TABLE, means that SQL Server *always* performs the Lock Escalation to the table level –even when the table is partitioned. If you have your table partitioned, and you want to have aPartition Level Lock Escalation (because you have tested your data access pattern, and you don’t cause deadlocks with it), then you can change the option to AUTO. AUTO means that the Lock Escalation is performed to the partition level, if the table is partitioned, and otherwise to the table level.

Box 2: Table 2, DISABLE

With the option DISABLE you can completely disable the Lock Escalation for that specific table.

For partitioned tables, use the LOCK_ESCALATION option of ALTER TABLE to escalate locks tothe HoBT level instead of the table or to disable lock escalation.

References:http://www.sqlpassion.at/archive/2014/02/25/lock-escalations/