All About 70-762 exam prep Oct 2021

we provide Verified Microsoft 70-762 latest exam which are the best for clearing 70-762 test, and to get certified by Microsoft Developing SQL Databases (beta). The 70-762 Questions & Answers covers all the knowledge points of the real 70-762 exam. Crack your Microsoft 70-762 Exam with latest dumps, guaranteed!

Q17. HOTSPOT

You have a database named Sales.

You need to create a table named Customer that includes the columns described in the following table:

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

Answer:

Explanation:

Box 1: MASKED WITH (FUNCTION ='default()')

TheDefualt masking method provides full masking according to the data types of the designated fields.

Example column definition syntax: Phone# varchar(12) MASKED WITH (FUNCTION =

'default()') NULL

Box 2: MASKED WITH (FUNCTION ='partial(3,"XXXXXX",0)')

The Custom String Masking method exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix

examples:

PhoneNumber varchar(10) MASKED WITH (FUNCTION = 'partial(5,"XXXXXXX",0)') Box 3: MASKED WITH (FUNCTION ='email()')

The Email masking method which exposes the first letter of an email address and the constant suffix ".com", in the form of an email address. .aXXX@XXXX.com.

Example definition syntax: Email varchar(100) MASKEDWITH (FUNCTION = 'email()') NULL

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

Q18. 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 and application to track customer sales.

You need to create an object that meet the 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 future 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. scalar-valued function

F. 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. Because managed code compiles to native code prior to execution, you can achieve significant performance increases in some scenarios.

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

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

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

Q21. 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/

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

Q23. 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 eventadded.

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.

Answer: D

Explanation:

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

Q24. DRAG DROP

You are monitoring a Microsoft Azure SQL Database. The database is experiencing high CPU consumption.

You need to determine which query uses the most cumulative CPU.

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 one or not at all. You may need to drag the split bar between panes or scroll to view content.

Answer:

Explanation:

Box 1: sys.dm_exec_query_stats

sys.dm_exec_query_stats returns aggregateperformance statistics for cached query plans in SQL Server.

Box 2: highest_cpu_queries.total_worker_time DESC Sort ontotal_worker_time column

Example: The following example returns information about the top five queries ranked by average CPU time.

Thisexample aggregates the queries according to their query hash so that logically equivalentqueries are grouped by their cumulative resource consumption.

USE AdventureWorks2012; GO

SELECT TOP 5 query_stats.query_hash AS "Query Hash", SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text" FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle)as ST) as query_stats GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

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