MCTS - Exam 70-431 - SQL Server 2005

MCTS - Exam 70-431 - SQL Server 2005 - Implementation and Maintenance - Practice Test -2

Candidates for this exam are professionals who typically pursue careers as database administrators, database developers, or business intelligence developers. The actual test (as of Jun 2007) consists of about 35-40 MCQs and 12-15 simulation questions. For more information, please visit   Microsoft Learning Website

1.  After creating some triggers on a table, you realized that they execute in the wrong order. What can you do to have the triggers executing in the right order?
 Drop the triggers, and re-create them in the corresponding order
 Use the sp_settriggerorder system stored procedure
 Execute an ALTER TABLE statement with ALTER TRIGGER to change the trigger order
 Create the triggers with _x where x is the trigger order number
2.  Your database server is configured using the default settings, and the user databases have the default options. One of your applications updates a table named tblCustomers. The update fires a trigger named UpdateCustomerDetails that will modify the tblCustomerDetails table. The modification of table tblCustomerDetails fires a trigger named UpdateCustomer that will modify the tblCustomers table. This behavior generates recursion. Which option allows this behavior?
 The RECURSIVE_TRIGGERS database option set to ON
 The RECURSIVE_TRIGGERS database option set to OFF
  The nested triggers’ server configuration option set to 0
 The nested triggers’ server configuration option set to 1
3.  You are the database administrator of your company. One of your company’s applications should maintain data integrity and return custom error messages when the entry data is incorrect. How can you achieve that with minimum effort?
 Add check constraints to necessary columns
 Use a DDL trigger
  Use a CLR trigger
 Use a DML trigger
4.  You have a large table with several thousand rows of product data. You have just imported a whole new catalog of seasonal data that has changed most of the data in the products table. You have a nonclustered index on the table that is now out-of-date. You need to bring it back up-to-date quickly. What can you do?
 Re-create the index using STATISTICS_NORECOMPUTE = ON
 Re-create the index using DROP_EXISTING = ON
  Drop the existing index manually, and re-create it
 Do nothing; SQL Server will bring the index back up-to-date automatically
5.  You have a server with several hard disk sets; all of your system databases, including TempDB, Master, and Model, are on one disk set, and a user database that you will be updating is on a disk set of its own. You need to create an index on a large table, and you want to make it as fast as possible. You are not worried about system resources such as disk space, processor time, or memory used. What can you do to create this index as fast as possible?
 Create the index with MAXDOP = 0
 Create the index with STATISTICS_NORECOMPUTE = ON
  Create the index with SORT_IN_TEMPDB = ON
 Create the index with PAD_INDEX = ON
6.  Which of these page types is used to store information about changes to the database since the last BACKUP DATABASE statement was executed?
 Index Allocation Map page
 Global Allocation Map page
  Differential Changed Map page
 Data page
 Page Free Space page
 Index page
 Bulk Changed Map page
 Text/image page
7.  One of your third-party applications has been certified to run on SQL Server 2000 but not 2005. Your company has just bought a new application that requires SQL Server 2005 to run. How can you run both of these applications with minimal overhead?
 Buy a second server, and install SQL Server 2005 on the new machine
 You can’t run both applications; you will have to wait until the older application is certified to run on SQL Server 2005
  Install SQL Server 2005 as a named instance, and configure your new application to use the new instance
 Install SQL Server 2005 as the default instance, and configure your new application to use the new instance
8.  You have a machine that has a 3.2GHz Pentium Xeon processor with 4GB of RAM and a 320GB hard drive running Windows 2003 Enterprise Edition. Which editions of SQL Server 2005 can you install? (Choose all that apply)
 Express Edition
 Workgroup Edition
  Standard Edition
 Enterprise Edition
 Developer Edition
9.  You are required to log every change to the customer data in a separate table named customer_history. How can you achieve this?
 Create a DML trigger on the customer table
 Create a DDL trigger on the customer table
  Use database snapshots
 Use database mirroring
10.  You have a table that contains employee information. Human Resources frequently queries this table using a query similar to the following:
SELECT FirstName, LastName, Address, Phone FROM Employees WHERE SSN = ‘ssn’ You need to create an index to speed up this query. What type should you create?
 Create a clustered index on the SSN column
 Create a nonclustered index on the SSN column
  Create a full-text index on the SSN column


Terms and Conditions of Use
Copyright © 2004 - 2006 Mahipal Padigela. All rights reserved.