MCTS - Exam 70-431 - SQL Server 2005 - Implementation and Maintenance

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

Top

www.mahipalreddy.com

Terms and Conditions of Use