70-762 Developing SQL Databases Practice Questions

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result. Establish if the solution satisfies the requirements.

You have a server that contains several physical disks that is not configured in a RAID array. Multiple Microsoft SQL Server instances are hosted on the server. A large number of SQL jobs are configured to execute within off-peak hours.
As a result of numerous deadlocks occurring at particular times, you want to keep an eye on the SQL environment and also capture the data relating to the processes producing the deadlocks.
Solution: You create a SQL Profiler trace.

Has the requirement been satisfied?

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result. Establish if the solution satisfies the requirements.
A database, named Transactions, three tables named Client, Purchases, and Items. The Client table has a column to record information regarding the last purchase made by the client. The Items table has the following fields:
ItemlD
ItemName
Description
QtyonHand
MerchantName
MerchantlD
Obsolete

The Purchases table has the following fields:
PurchaselD
ItemName
ItemlD
EmployeelD
Purchase date

You want to make sure that rows in the Purchases table include a genuine value for the ItemlD column at all times. Also, if rows in the Items table are part of any rows in the table, they should not be removed. Furthermore, all rows in Items and Purchases tables must be distinctive.

Solution: You configure a check constraint on the ItemlD in the Purchases table. Has the requirement been satisfied?

Background
The HumanResources database contains a table, named Staff.
A number of read-only, historical reports that make use of various queries, which execute simultaneously, to assess workforce costs, include totals that transform on a regular basis. When you are informed that the running of the workforce assessment reports is not constant, you are required to examine the database to detect the reason for this happening.
It is your intention to install the application on a database server supporting other applications. The storage space required by the database should be reduced.

Application
An application that updates the Staff table calls two stored procedures, named UspStaffA and UspStaffB, simultaneously and asynchronously. UspStaffA only updates the Staffstatus column, while UspStaffB only updates the StaffPayRate column
The application controls access to information by making use of views that permit user access to all columns in the tables that the view accesses, but also limit updates to the rows that the view returns.
You are required to create a view that will be used to permit users to modify data in the Staff table. This view must also block users from accessing the view definition in catalog views.

Which of the following is the view attribute that should be used to block users from accessing the view definition in catalog views?

Note: The question is included in a number of questions that depicts the identical set-up. However, every question has a distinctive result. Establish if the solution satisfies the requirements.
A database, named Transactions, three tables named Client, Purchases and Items. The Client table has a column to record information regarding the last purchase made by the client. The Items table has the following fields:

ItemlD
ItemName
Description
QtyonHand
MerchantName
MerchantlD
Obsolete

The Purchases table has the following fields:

PurchaselD
ItemName
ItemlD
EmployeelD
PurchaseDate

You are preparing to execute a stored procedure that removes an obsolete item from the Items table. The stored procedure should allow for the information for the item to remain in the event that an open purchase contains an obsolete item. The stored procedure should then produce a custom error message, which identifies the PurchaselD for the open purchase

Solution: You include the Try/Parse Transact-SQL segment to handle errors.
Has the requirement been satisfied?