What Does 70-762 exam guide Mean?

Exam Code: 70-762 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Developing SQL Databases (beta)
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-762 Exam.

Q1. 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 an application to track customer sales.

You need to create a database object that meets the following requirements:

- Return a value of 0 if data inserted successfully into the Customers table.

- Return a value of 1 if data is not inserted successfully into the Customers table.

- Support logic that is written by using managed code. 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: B

Explanation:

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements.DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

A CLR trigger is a type of DDL trigger. A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger canalso be a DDL trigger. Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code thatare members of an assembly created in the .NET Framework and uploaded in SQL Server.

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

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

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

Answer: B

Explanation:

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

Q4. You have a database that is experiencing deadlock issues when users run queries. You need to ensure that all deadlocks are recorded in XML format.

What should you do?

A. Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.

B. Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).

C. Enable trace flag 1222 in thestartup options for Microsoft SQL Server.

D. Use the Microsoft SQL Server Profiler Lock:Deadlock event class.

Answer: C

Explanation:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is capturedin the SQL Server error log.Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources.

The output format for Trace Flag 1222 only returns information in an XML-like format. References:https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx

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

Answer: A

Explanation:

Create a clustered index on theAccountNumber column as it is unique. Create a nonclustered index that includes the ProductCode column.

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

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

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

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 CLUSTERED INDEX PK_Account ON Account(ProductCode); Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

We need an index on the productCode column as well. References:https://msdn.microsoft.com/en-us/library/ms190457.aspx

Q8. 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 cascading referential integrity constraints on the table. Does the solution meet the goal?

A. Yes

B. No

Answer: A

Explanation:

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

Q9. 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 an application to track customer sales.

You need to create a database object that meets the following requirements:

- Return a value of 0 if data inserted successfully into the Customers table.

- Return a value of 1 if data is not inserted successfully into the Customers table.

- Support TRY…CATCH error handling

- Be written by using Transact-SQL statements. 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: D

Explanation:

DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.

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

Q10. DRAG DROP

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 the series.

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

The Customer table includes a column that stores the date 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 begin to modify the table design to adhere to third normal form.

Which column should you remove for each table? To answer? drag the appropriate column names to the correct locations. Each column name 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:

In the Products table the SupplierName is dependant on the SupplierID, not on the ProductID.

In the Orders table the ProductName is dependant on the ProductID, not on the OrderID.

Note:

A table is in third normal form when the following conditions are met:

* It is in second normal form.

* All nonprimary fields are dependent on the primarykey.

Second normal form states that it should meet all the rules for First 1Normnal Form and there must be no partial dependences of any of the columns onthe primary key.

First normal form (1NF) sets the very basic rules for an organized database:

* Define the data items required, because they become the columns in a table. Place

related data items in a table.

* Ensure that there are no repeating groups ofdata.

* Ensure that there is a primary key. References:https://www.tutorialspoint.com/sql/third-normal-form.htm

Q11. HOTSPOT

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.

Answer:

Explanation:

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

Q12. 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 need to create a stored procedure that updates the Customer, CustomerInfo, OrderHeader, and OrderDetails tables in order.

You need to ensure that the stored procedure:

Solution: You create a stored procedure that includes the following Transact-SQL segment:

Does the solution meet the goal?

A. Yes

B. No

Answer: B

Explanation:

References: http://stackoverflow.com/questions/11444923/stored-procedure-to-update- multiple-tables

Q13. 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 the SQL Server instances in real time and optimize the server to maximize throughput, response time, and overall SQL performance.

What should you do?

A. A. Create asys.dm_os_waiting_tasks query.

B. Create a sys.dm_exec_sessions query.

C. Create aPerformance 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: B

Explanation:

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. This information includes client version, client program name, client login time, login user, current session setting, and more. Use sys.dm_exec_sessions to first view the current system load and to identify a session of interest, and then learn more information about that session by using other dynamic management views or dynamic management functions.

Examples of use include finding long-running cursors, and finding idle sessions that have open transactions.

Q14. 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 clusteredcolumnstore index on the table.

E. Create a nonclustered columnstore index on the table.

F. Create a hash index on the table.

Answer: C

Explanation:

A filtered index is an optimized nonclustered index, especially suited to cover queries that

select from awell-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

Q15. 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 that contains the following tables: BlogCategory, BlogEntry, ProductReview, Product, and SalesPerson. The tables were created using the following Transact SQL statements:

You must modify the ProductReview Table to meet the following requirements:

1. The table must reference the ProductID column in the Product table

2. Existing records in the ProductReview table must not be validated with the Product table.

3. Deleting records in the Product table must not be allowed if records are referenced by the ProductReview table.

4. Changes to records in the Product table must propagate to the ProductReview table.

You also have the following database tables: Order, ProductTypes, and SalesHistory, The transact-SQL statements for these tables are not available.

You must modify the Orders table to meet the following requirements:

1. Create new rows in the table without granting INSERT permissions to the table.

2. Notify the sales person who places an order whether or not the order was completed.

You must add the following constraints to the SalesHistory table:

- a constraint on the SaleID column that allows the field to be used as a record identifier

- a constant that uses the ProductID column to reference the Product column of the ProductTypes table

- a constraint on the CategoryID column that allows one row with a null value in the column

- a constraint that limits the SalePrice column to values greater than four

Finance department users must be able to retrieve data from the SalesHistory table for sales persons where the value of the SalesYTD column is above a certain threshold.

You plan to create a memory-optimized table named SalesOrder. The table must meet the following requirements:

- The table must hold 10 million unique sales orders.

- The table must use checkpoints to minimize I/O operations and must not use transaction logging.

- Data loss is acceptable.

Performance for queries against the SalesOrder table that use Where clauses with exact equality operations must be optimized.

You need to create the Sales Order table

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

Answer:

Explanation:

Box 1: NONCLUSTERED HASHWITH (BUCKET_COUNT = 10000000)

Hash index is preferable over a nonclustered index when queries test the indexed columns by use of a WHERE clause with an exact equality on all index key columns. We should use a bucket count of 10 million.

Box 2: SCHEMA_ONLY

Durability: The value of SCHEMA_AND_DATA indicates that the table is durable, meaning that changes are persisted on disk and survive restart or failover. SCHEMA_AND_DATA is

the default value.

The value of SCHEMA_ONLY indicates that the table is non-durable. The table schema is persisted but any data updates are not persisted upon a restart or failover of the database. DURABILITY=SCHEMA_ONLY is only allowed with MEMORY_OPTIMIZED=ON.

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

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

Related 70-762 posts: