Passed the 70-229 exam yesterday. All the questions were from www.exams.ws  study material. I got 90 % marks. That’s sufficient for me. I am satisfied with it.

I hereby submit some questions.

I hope it helps.

1: You are a database developer for a hospital. There are four supply rooms on each floor of the hospital, and the hospital has 26 floors. You are designing an inventory control database for disposable equipment. Certain disposable items must be kept stored at all times. As each item is used, a barcode is scanned to reduce the inventory count in the database. The supply manager should be paged as soon as a supply room has less than the minimum quantity of an item. What should you do?

A.     Create a stored procedure that will be called to update the inventory table. If the resultant quantity is less than the restocking quantity, use the xp_logevent system stored procedure to page the supply manager

B.      Create an INSTEAD OF UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use SQLAgentMail to send an e-mail message to the supply manager’s pager

C.      Create a FOR UPDATE trigger on the inventory table. If the quantity in the inserted table is less than the restocking quantity, use the xp_sendmail system stored procedure to page the supply manager

D.     Schedule the SQL server job to run at four-hour intervals. Configure the job to use the @notify_level_page = 2 argument. Configure the job so that it tests each item’s quantity against the restocking quantity. Configure the job so that it returns a false value if the item requires restocking. This will trigger the paging of the supply manager.

Answer: C.

2: You are a database developer for Proseware,Inc. The company has a database that contains information about companies located within specific postal codes. This information is contained in the company table within this database. Currently, the database contains company data for five different postal codes. The number of companies in a specific postal code currently ranges from 10 to 5,000. More companies and postal code will be added to the database over time You are creating a query to retrieve information from the database. You need to accommodate new data by making only minimal changes to the database. The performance of your query must not be affected by the number of companies returned. You want to create a query that performs consistently and minimizes future maintenance. What should you do?

A.     Create a stored procedure that requires a postal code as a parameter. Include the WITH Recompile option when the procedure is created

B.      Create one stored procedure for each postal code

C.      Create one view for each postal code

D.     Split the company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.

Answer: A.

3: You are a database developer for an automobile dealership. The company stored its automobiles inventory data in a SQL Server 2000 database. Many of the critical queries in the database join three tables named Make, Model, and Manufacturer. These tables are updated infrequently. You want to improve the response time of the critical queries. What should you do?

A.     Create an indexed view on the tables.

B.      Create a stored procedures that returns data from the tables

C.      Create a scalar user-defined function that returns data from the tables

D.     Create a table-valued user-defined function that returns data from the tables.

Answer: A .

4: You are the developer of a database named Inventory. You have a list of reports that you must create. These reports will be run at the same time. You write queries to create each report. Based on the queries, you design and create the indexes for the database tables. You want to ensure that you have created useful indexes. What should you do?

A.     Create a SQL Profiler trace, and use the Objects event classes.

B.      Run the Index Tuning Wizard against a workload file that contains the queries used in the reports.

C.      Run system monitor, and use the SQLServer:Access Methods counter.

D.     Execute the queries against the tables in SQL Query Analyzer, and use the SHOWPLAN_TEXT option.

Answer: B.