File Info
| Exam | Developing SQL Databases |
| Number | 70-762 |
| File Name | Microsoft.70-762.ActualTests.2018-09-26.83q.tqb |
| Size | 4 MB |
| Posted | Sep 26, 2018 |
| Download | Microsoft.70-762.ActualTests.2018-09-26.83q.tqb |
How to open VCEX & EXAM Files?
Files with VCEX & EXAM extensions can be opened by ProfExam Simulator.
Coupon: MASTEREXAM
With discount: 20%
Demo Questions
Question 1
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?
- Create a Microsoft SQL Server Integration Services package that uses sys.dm_tran_locks.
- Enable trace flag 1224 by using the Database Cpmsistency Checker(BDCC).
- Enable trace flag 1222 in the startup options for Microsoft SQL Server.
- Use the Microsoft SQL Server Profiler Lock:Deadlock event class.
Correct 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 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
Question 2
You are developing an application that connects to a database.
The application runs the following jobs:
The READ_COMMITTED_SNAPSHOT database option is set to OFF, and auto-content is set to ON. Within the stored procedures, no explicit transactions are defined.
If JobB starts before JobA, it can finish in seconds. If JobA starts first, JobB takes a long time to complete.
You need to use Microsoft SQL Server Profiler to determine whether the blocking that you observe in JobB is caused by locks acquired by JobA.
Which trace event class in the Locks event category should you use?
- LockAcquired
- LockCancel
- LockDeadlock
- LockEscalation
Correct answer: A
Explanation:
The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated. The Lock:Acquiredevent class indicates that acquisition of a lock on a resource, such asa data page, has been achieved.
The Lock:Acquired and Lock:Released event classes can be used to monitor when objects are being locked, the type of locks taken, and for how long the locks were retained. Locks retained for long periods of time may cause contention issues and should be investigated.
Question 3
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 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:
- Allow users access to the CustomerName and CustomerNumber columns for active customers.
- Allow changes to the columns that the view references. Modified data must be visible through the view.
- Prevent the view from being published as part of Microsoft SQL Server replication.
Sales.Female.Customers must meet the following requirements:
- Allow users access to the CustomerName, Address, City, State and PostalCode columns.
- Prevent changes to the columns that the view references.
- 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:
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.
- You must be able to store up to 50 accounts for each customer.
- Users must be able to retrieve customer information by supplying an account number.
- 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?
- Split the 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.
- 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.
- 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.
- Split the table into two separate tables, Include the CustomerID, CustomerName and Gender columns in the first table. Include AccountNumber, AccountStatus and CustomerID columns in the second table.
Correct answer: D
Explanation:
Two tables is enough.CustomerID must be in both tables. Two tables is enough.CustomerID must be in both tables.
