The Secret of 70-762 practice question

Actualtests offers free demo for 70-762 exam. "Developing SQL Databases (beta)", also known as 70-762 exam, is a Microsoft Certification. This set of posts, Passing the Microsoft 70-762 exam, will help you answer those questions. The 70-762 Questions & Answers covers all the knowledge points of the real exam. 100% real Microsoft 70-762 exams and revised by experts!

Q16. DRAG DROP

Case Study 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

Both of the stored procedures experience blocking issues. UspB must not abort if UspA commits changes to a row before UspB commits changes to the same row. UspA must not abort if UspB commits changes to a row before UspA commits changes to the same row.

You need to specify the transaction isolation levels to enable row versioning.

How should you complete the Transact-SQL statements? To answer, drag the 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.

Answer:

Explanation:

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

Q17. 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 update the application to use implicit transactions when connecting to the database.

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

References: http://sqltouch.blogspot.co.za/2013/05/writelog-waittype-implicit-vs- explicit.html

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

Q19. 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 stored procedure on the Employees table. Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

We should use table-valued functions, not procedures, to customize the recorded change data.

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

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

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

Q22. 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: Solution: You implement a FOR UPDATE trigger on the table. Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

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

Q23. HOTSPOT

Background

You have a database named HR1 that includes a table named Employee.

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

Users must only be able to modify data in the Employee table by using the vwEmployee view. You must prevent users from viewing the view definition in catalog views.

You need to identify the view attribute to use when creating vwEmployee. In the table below, identify the attributes that you must use.

NOTE: Make only one selection in each column.

Answer:

Explanation:

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

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

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

Q26. 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 return the sum of orders that have been finalized, given a specified order identifier. This value will be used in other Transact-SQL statements.

You need to create a database object.

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

Explanation:

User-defined scalar functions return a single data value of the type defined in the RETURNS clause.

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

Q27. Background

You have a database named HR1 that includes a table named Employee.

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

Q28. You have a view that includes an aggregate.

You must be able to change the values of columns in the view. The changes must be reflected in the tables that the view uses.

You need to ensure that you can update the view. What should you create?

A. table-valued function

B. a schema-bound view

C. a partitioned view

D. a DML trigger

Answer: B

Explanation:

When you use the SchemaBinding keyword while creating a view or function you bindthe structure of any underlying tables or views. It means that as long as that schemabound object exists as a schemabound object (ie you don’t remove schemabinding) you are limited in changes that can be made to the tables or views that it refers to.

References:https://sqlstudies.com/2014/08/06/schemabinding-what-why/

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

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

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

To see all sample questions go to: 70-762 braindumps

Related 70-762 posts: