Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 5

1.

You need to design the process for deleting customer accounts to fulfill the business requirements. What should you do?

Implement an AFTER delete trigger on the Customers table that deletes orders and invoices related to the customer when the customer is deleted. Implement cascading deletes for the foreign keys from the Orders and Invoices tables to the Customerstable. Design a stored procedure that uses a cursor to issue DELETE statements that delete each order andinvoice related to the customer account and then deletes the customer account. Design a stored procedure that uses a cursor to run stored procedures that delete each order and invoicerelated to the customer account and then deletes the customer account.

2.

You need to design the solution to create the airfare XML text file for the company extranet. What should you do?

Use a SELECT statement to fetch the data. Load the result set into a DataSet object.Write the XML code in the DataSet object to the text file by using the WriteXml method. Use a SELECT FOR XML statement to generate the XML code. Load the XML code from the database intoan XmlDocument object.Write the XML code in the XmlDocument object to the text file by using the Save method. Use a SELECT FOR XML statement to generate the XML code. Load the result set into a DataSetobject.Write the XML code in the DataSet object to the text file by using the WriteXml method. Use a SELECT FOR XML statement to generate the XML code. Read the XML code from the database byusing an XmlReader object.Write the XML code to the text file using an XmlTextWriter object.

3.

You need to design the retrieval of the passenger lists for the company intranet to fulfill the business requirements. What should you do?

Use a DataReader object to populate the Web page. Use a DataSet object to populate the Web page. Retrieve an XmlDocument object from a DataSet object, and use the XmlDocument object to populate theWeb page. Use the FOR XML clause of the SELECT statement to retrieve the data. Load the data into anXmlDocument object and use the XmlDocument object to populate the Web page.

4.

You need to allow the new version of the application to run from the remote offices. All SQL Server 2005 features must also be available through this connection. What should you do?

Use the System.Data.OleDb namespace to connect to SQL Server. Use the System.Data.SqlClient namespace to connect to SQL Server. Create an HTTP endpoint on SQL Server and configure the client computers to connect to the endpoint. Create a Web service on another computer, and configure this computer to connect to SQL Server.Configure the client computers to connect to the Web service.

5.

You need to design the storage of the Events table to fulfill the technical requirements. What should you do?

Place all nonclustered indexes on another filegroup. Place the table on another filegroup. Move the Events table to another database. Move the Events table to another server.

6.

You need to optimize the stored procedure spProductSearch. What should you do?

Execute the stored procedure by using the recompile option. Modify the stored procedure by adding the recompile option to it. Modify the stored procedure by adding the recompile option to the problematic query. Create a plan guide for the stored procedure that tells the problematic query to use a specific executionplan.

7.

You need to ensure that the stored procedure spInsuranceOrderAdd functions according to specifications. What should you do?

Wrap the inserts into the InsuranceOrders and InsuranceOrderItems tables in a TRY/CATCH block. Issue a rollback statement in the CATCH block. On the InsuranceOrderItems table, place a foreign key that points to the InsuranceOrders table. Insert the insurance order into the InsuranceOrders table, and then insert the items on the order into theInsuranceOrderItems table. Verify that the items were inserted by using the OUTPUT clause of the INSERT statement. If an incorrect number of items is found, roll back the transaction. Place a check constraint on the InsuranceOrders table. Design the check constraint to use a user-definedfunction to verify that the order has corresponding rows in the InsuranceOrderItems table.

8.

You need to design the method for managing user-defined error messages to fulfill the business requirements. What should you do?

Create a Service Broker queue to contain the error messages. Use SQL Servers built-in system for managing error messages. Create a new table to store the error messages. Create two new tables to store the error messages: use one for the languages that are available, and useone for messages.

Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 5

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner

Microsoft-PRO Designing and Optimizing Data Access by Using Microsoft SQL Server 2005 Set 5

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

Detail Form

COMPANY

Products

OTHERS

Partner